Help

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

Re: Split text to multiple columns

625 0
cancel
Showing results for 
Search instead for 
Did you mean: 
CSM_PF
5 - Automation Enthusiast
5 - Automation Enthusiast

I want to separate the following text in different columns :

Column A : @Ben//test-bug//Feb 22, 2024 3:54pm UTC//Budget//Test again//Test again//Moderate

Column B : @Ben
Column C : test-bug
Column D : Feb 22, 2024 3:54pm UTC
Column E : Budget
Column F : Test again
Column G : Test again
Column H : Moderate

1 Reply 1
kuovonne
18 - Pluto
18 - Pluto


Like this?

kuovonne_0-1708708889600.png

It looks like you have seven values that are separated by double slashes. 

Unfortunately, Airtable formulas do not have a SPLIT() function, so we have to look elsewhere.

There are actually lots of formulas that can work, but using a single regular expression pattern that works for all seven formulas makes things easier to maintain.

kuovonne_1-1708709114512.png

To get subsequent values, just change the number in the fourth line.

kuovonne_4-1708709317267.png

kuovonne_3-1708709244891.png


The difficulty that I have with situations like this is there is only one example, and it isn't clear whether or not all the other values will follow the same pattern or not. In this case, it looks like seven values separated by double slashes. However, if other values will have different patterns, such as more/fewer values, that can affect the formula.
kuovonne_5-1708710140578.png

It is possible to calculate the number of values based on the number of slashes.

kuovonne_6-1708710389088.png

Then the number of values can be used to build the regular expression. Here is an example.

kuovonne_7-1708710441238.png

On the other hand, you may have some other variations that will cause other problems.

Another possibility is to use AI to extract the values for you.

I also wonder if you are coming from a spreadsheet background and if something different might actually be better for whatever your ultimate end goals are.