How to create a Link sheet from Column with multiple entries per cell?

[Dustin_Hodge]

Hi there,

I just imported a spreadsheet that currently has a column that would be a “multiple select” in airtable, each entry separated by a semicolon.

For example, here’s how a few cells in the column might look:

Alabama; Michigan; Massachusetts
New Hampshire; Alabama
Massachusetts; New Hampshire; Maine

I have already created an empty table in the base named “States” and I want to convert that column type to a “Link to another record” in such a way that creates records in the way that one might expect: one entry per State.

However, using the GUI interface I have only managed to get Airtable to do the dumb thing of converting each cell as one thing. Is there any way to do better?

[Julian_Kirkness]

Hi @Dustin_Hodge

Try changing the semicolons to commas - this should then work for you.

[Dustin_Hodge]

The problem with that is airtable doesn’t have a find-and-replace yet and I can’t import the data with commas in it because airtable only accepts .csv and not .tsv. So… I’m trying to find another solution besides changing literally thousands of semicolons to commas by hand. :confused:

[Julian_Kirkness]

Hi @Dustin_Hodge

Try this:

This will give you a field containing commas instead of semi colons - and even though it’s a formula you can still convert its field type to Link to another record - and it will create or populate your lookup table.

Hope this helps!

Julian

[Dustin_Hodge]

That… is a brilliant hack. Thanks so much @Julian_Kirkness!