Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Split a linked record by comma

1715 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Tina_Lopez
4 - Data Explorer
4 - Data Explorer

Hello, 

I've looked everywhere and can't find a simple solution that works for me. I have a table where I link records and I want to take the linked records field and split it by comma and input those items into it's own field. 

Can someone please give me a formula to input into the columns?

Tina_Lopez_0-1671832973910.png

3 Replies 3
John_B2
6 - Interface Innovator
6 - Interface Innovator

Record 1 =

LEFT({Record ID from record ids}, FIND(",",{Record ID from record ids},1)-1)

Record 2 = 

MID({Record ID from record ids}, FIND(",",{Record ID from record ids} ,1)+1,99)

 

Note the 99 character count in the 2nd formula could be replaced by another calc to get the exact length of the string but using 99 works just as well!

 

Hi John_B2 I don't think this worked properly. Here's what I've inputted. 
https://komododecks.com/recordings/NNvWuEBLCQPhULqzextt

The formula throws out an error and the second formula just throws out the last number instead of the second record ID

Hi TIna

sorry my formula was for comma delimited strings but on looking again I see your field is a lookup that returns a single long string with no commas - it's a case of Airtable's "what you see is not what you get". In some ways this will make it easier to retrieve to data. It's displayed with commas but when used in a formula the commas are not there. The first comlumn will just be

LEFT({Record ID from record ids}&"",17)
The &"" converts the field to a string and from that we grab the first 17 characters.
To get the second value use
MID({Record ID from record ids}&"",18,17)
This just grabs the data starting from the 18th position for the next 17 chars.