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

[Steffen_Giebele]

Hi Justin,

thanks so much for this. Works like a charm!

[Prabhjot_Singh_]

Hi Justin ,

I have similar kind of problem , hope you can help me also.

I am trying to extract particular Strings followed by word. for e.g

This is stored as long text.

Date Wed, 31 Mar 2021 11:45:50 PDT Build Version 1.1.22 User ID ABC Issue Reported* this is for testing Actions Tried -------------Networking-----

I would need to Extract values followed by Date , USER ID and Issue Reported Fields. Length of User id value or the Issue reported may differ so I would like to take whole value followed by these keywords. MID will give me the value, starting value will be same always but as I dont know the end of String so dont know how to proceed with that.

Any help on the same would be Highly Appreciated

Note : Values might not be on the same line as shown above but there can be spaces between the field and Value

[Justin_Barrett]

@Prabhjot_Singh_Gill I made three regular expressions. Here’s the one to grab the date:

IF(String, TRIM(REGEX_EXTRACT(String, "(?:Date\\s)(.*)(?:\\sBuild.*)")))

Now the user ID:

IF(String, TRIM(REGEX_EXTRACT(String, "(?:.*User ID)(.*)(?:\\sIssue Reported.*)")))

And the issue reported:

IF(String, TRIM(REGEX_EXTRACT(String, "(?:.*Issue Reported)(.*)(?:\\sActions Tried.*)")))

These will work whether everything is on a single line (top record), or split onto separate lines (bottom record).

The way these work is by using groups. Groups are wrapped in parentheses, and each group is matched in order. When a group begins with ?: it means to find the group, but ignore its contents when returning the result. In the end, each field finds (and ignores) just enough material to locate when a given item begins, and captures captures everything from there to the start of the next specific group, which is also ignored. The end result is trimmed to eliminate whitespace.

[Prabhjot_Singh_]

Thank alot @Justin_Barrett , I took me somewhere but now I got an idea on how it works so I will play around with it more. Really appreciate your help on the same .

[Mindy_Peters]

The second half of this formula, the RIGHT() portion, solved my need for an Airtable split string formula. Thank you so very much!

[Daley_Maasz]

Kia ora from New Zealand @Justin_Barrett,

Reading through the thread you sound like the kind of human that may be able to help me out, I am looking to split each of the lines from a long text field into a separate short text field?

eg

Long Text Field:
This is the first line
This is the second Line
This is the third

Split out into:

Short Text Field:
This is the first line

Short Text Field:
This is the second line

Short Text Field:
This is the third line

Any thoughts?

[Justin_Barrett]

Welcome to the community, @Daley_Maasz! :grinning_face_with_big_eyes: This is doable, but the three target fields will be formula fields, not single line text fields. Formulas can only dictate their own output; they can’t control the contents of other fields.

Here’s the setup in action, followed by the formulas:

First line…

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

Second line…

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

Third line…

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

[Daley_Maasz]

Justin_Barrett:

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

Thanks @Justin_Barrett - just an update that I had to apply to below changes for it to work in my case:

Second line extract:
REGEX_EXTRACT({parsed_delivery-address}, “(?:.\n)(.)”)

Third line extract:
REGEX_EXTRACT({parsed_delivery-address}, “(?:.\n)(?:.\n)(.*)”)

And I am now playing around with it being able to extract a fourth line but hitting issues so will update once I am able to get it working.

[Daley_Maasz]

Not sure what was going on but the fourth line extract answer did indeed follow the same logic:

REGEX_EXTRACT({parsed_delivery-address}, “(?:.\n)(?:.\n)(?:.\n)(.)”)

@Justin_Barrett any ideas on how to build these into ‘IF’ statements that would would return a blank field entry in place of the ‘#ERROR!’ when the regex returns false?

[Justin_Barrett]

@Daley_Maasz The formulas I wrote above were copied directly from the fields where I tested them, so it’s odd that the second and third ones didn’t work for you. Were you copying them to your formula fields, or retyping them manually? If it’s the latter, you might have typed something incorrectly. If you copied them, then there must be something with your base setup that wouldn’t work with them. Your comment about extracting a fourth line makes me think that the number of lines to extract isn’t always consistent. Is that the case?