Formula to break a string of text onto 2 separate strings of text

[J_B_S]

I have a string of text that I want to break into two separate strings of text.
Here’s an example:

• This is an example string of text / I want to break it into two separate strings of text

I want all of the words that precede the “/” to be one string of text. And then I want all of the words that come after the “/” to be a separate string of text. I want to completely eliminate the “/”.

So, to recap, we start with this:

• This is an example string of text / I want to break it into two separate parts

And then we end-up with this:

• This is an example string of text
• I want to break it into two separate parts

[Daley_Maasz]

@Justin_Barrett That is correct, it can be anywhere from 2 to 4 lines of text.

[Justin_Barrett]

@Daley_Maasz Thanks for the clarification. This changes the setup slightly, but the core of what I wrote before still remains. First I suggest making a formula field named {Line Count} using this formula:

IF({All lines}, LEN({All lines}) - LEN(SUBSTITUTE({All lines}, "\n", "")) + 1)

The other fields will use this to determine whether or not to extract a given line. Here are the rest of the formulas.

First line

IF({Line Count}, REGEX_EXTRACT({All lines}, "(.*)(?:\n.*)"))

Second line

IF({Line Count} > 1, REGEX_EXTRACT({All lines}, "(?:.*\n)(.*)"))

Third line

IF({Line Count} > 2, REGEX_EXTRACT({All lines}, "(?:.*\n){2}(.*)"))

Fourth line

IF({Line Count} > 3, REGEX_EXTRACT({All lines}, "(?:.*\n){3}(.*)"))

[Daley_Maasz]

Justin_Barrett:

IF({Line Count} > 3, REGEX_EXTRACT({All lines}, "(?:.*\n){3}(.*)"))

@Justin_Barrett It worked perfectly, thank you.

[Mathilde_Vandon]

Hello,

I have a similar problem, but I can’t seem to find the answer in this thread.

I would like to separate into different records, strings of text separated by commas.
!

The tricky thing is, I don’t know how many “restaurants” there will be ! Does anyone have a solution ?

Also, I would like to add those “restaurants” records (split into separate strings of text) in another table (with an automation), and link it to the previous table with the strings of text.

Thank you for your help!!

[Dimitris_Goudis]

Hi Mathilde,

do you know the maximum number of restaurants that can be in the field?
What do you actually want is to separate each restaurant in a separated value new field (field = new column) ?

Could you give an extra explanation for the automation?

Thanks

[Mathilde_Vandon]

Hi Dimitris,

Actually I don’t know the maximum because it will depend on our client profiles, but I can imagine that we won’t have over 20 restaurants in a single string

Exactly, I would like to create a record for each restaurant in another table, each of them linked to the group of restaurants they belong to !
So :
1- Separate strings into different restaurants records
2- Link each restaurant records to the first table

This is the result I am looking for :

Thank you very much!

[kuovonne]

Welcome to the Airtable community!

Airtable does not have a split function. You can look into this thread for one method. However, since your end goal is more linked records, and not the actual contents of the different fields, you may want to look into having a script to do all of the processing from splitting the text string to making the linked records.

[Dimitris_Goudis]

Hi again :slightly_smiling_face:

to split the filed with restaurants the solution is like above, to split based in comma existence and position in the mother field for every next restaurant.

As about the automation you are looking for I didn’t get again what you want to do because I can not understand the process you follow. If you want feel free to text me a private message for further explanation. :slightly_smiling_face:

[Mathilde_Vandon]

Hi kuovonne,

Thanks for your reply ! Would you have an idea of what the script could be ? Thank you!!

[kuovonne]

Mathilde_Vandon:

Would you have an idea of what the script could be ?

If you choose to go the scripting route, I recommend a custom script. Situations like this tend to have enough unique details that a pre-built script (if you can find one) rarely does everything that you want.

If you know how to code the documentation is excellent.

If you do not know how to code or do not want to learn to code, you can hire someone to write a custom script for you. There are several custom script writers on these forums, including myself.