Help

Re: Split single record into multiple records in the same cell

1287 1
cancel
Showing results for 
Search instead for 
Did you mean: 
federica
4 - Data Explorer
4 - Data Explorer

Hello! 

I am trying to split a single record into multiple records in the same cell. 

I have a column called "Languages" with single records such as "Arabic & French" or "French & Spanish". I'd like to split all this records in such a way that each cell contains two records "Arabic" "French". Is there any way to do this? Thanks in advance

4 Replies 4

Hey @federica

So, I've gone ahead and created a small demo to provide a side-by-side workflow.
For the sake of an example, let's say that I have previously created a table called Classes with a single-line text field called Class Languages.

Ben_Young1_0-1675267103241.png

After some time, I realize that these languages should be their own records that then relate to relevant classes.
To fix my schema, I'll first create a new table called Languages. Each record here will be a unique language.
I'll then create a linked field between these two tables.

Ben_Young1_1-1675267223269.pngBen_Young1_2-1675267230606.png

Now I need to transfer the stored data in the Class Languages (single-line text) field to the Languages table.
I'm going to assume that your Class Languages field contains values that universally fit the following format:

languageOne & languageTwo

If this is the formatting we can expect, then we can simply create a temporary formula field with the following formula:

IF(
    {Class Languages},
    REGEX_EXTRACT(
        TRIM({Class Languages}),
        "^\\w+\\b"
    )
    & "," &
    REGEX_EXTRACT(
        TRIM({Class Languages}),
        "\\b\\w+$"
    )
)

This will produce the following behavior:

Ben_Young1_3-1675267467745.png

From here, you can simply copy and paste the extract into the Languages linked record field.

Ben_Young1_4-1675267847992.gif

Once we've completed the data transfer, we can delete both the formula field and the old single-line text field.

This entire process assumes that your data is formatted in the same way. If your data takes on different formats, then we'll have to adjust to the other formats that might be present in your database.
If you have other data formats that need to be accounted for, let me know and I'll write a revision to the extract formula.

federica
4 - Data Explorer
4 - Data Explorer

Hi Ben, Thank you! This is really helpful. 

The dateset looks like this

federica_1-1675269320172.png

The language section includes both blanks, single entry as well as a wide range of possible language combinations. 

Also, since the dataset comprises more than 3,000 rows, is it possible to apply the extraction formula to the first row (or first 10 rows) and apply that to the whole dataset without copying that? I am a beginner on Airtable so thanks for your help!!!

Federica

 

To deal with the possibility of a blank field, a field with a single value, and a value of two languages separated by an ampersand, you can use the following formula:

IF(
    {Language(s)},
    IF(
        REGEX_MATCH(
            TRIM("" & {Language(s)}),
            "&"
        ),
        REGEX_EXTRACT(
            TRIM("" & {Language(s)}),
            "^\\w+\\b"
        )
        & "," &
        REGEX_EXTRACT(
            TRIM("" & {Language(s)}),
            "\\b\\w+$"
        ),
        TRIM("" & {Language(s)})
    )
)

Also, since the dataset comprises more than 3,000 rows, is it possible to apply the extraction formula to the first row (or first 10 rows) and apply that to the whole dataset without copying that?


Speaking plainly, this is spreadsheet thinking!
No, you cannot selectively apply a formula to a single record. The field lives on a table and runs the calculation on every record stored in the table.

That being said, don't be afraid of the number of records you're dealing with.
This is what makes databases preferable over spreadsheets. They're built to handle immense amounts of data and relationships. They are incredibly robust when executed properly.

To highlight this, I created 3,000 records with randomized language values in a multi-select field similar to what you shared in your screenshot.

Ben_Young1_0-1675284814415.png

Here's the Languages table before I transfer the data. Currently, we only have our four languages from our first test.

Ben_Young1_1-1675284918531.png

Now that we're set, we can just copy the entire table field value and paste it directly into the linked record field.
All the new records will be automatically created in the Languages table.

Ben_Young1_3-1675285249902.png

Ben_Young1_4-1675285323330.png

Then, same as before, we can just delete our language field and the formula field since we don't need them anymore.

If anything happens that you don't like, you can simply hit undo (CTRL + Z) or do it manually in the base using the Base history feature.

Ben_Young1_5-1675285475701.png

federica
4 - Data Explorer
4 - Data Explorer

Thank you! In case I get to this (language 1, language 2, language 3)

federica_1-1675341641282.png

Is there a way to merge these entries in such a way that they are still separate and colourful ones but within one single column? Thanks a lot for your help