Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 11, 2019 05:19 PM
I have a string of text that I want to break into two separate strings of text.
Here’s an example:
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:
And then we end-up with this:
Mar 08, 2021 02:13 AM
Hi Justin,
thanks so much for this. Works like a charm!
Apr 01, 2021 07:35 AM
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
Apr 03, 2021 08:49 PM
@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.
Apr 05, 2021 04:45 AM
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 .
Apr 07, 2021 08:45 AM
The second half of this formula, the RIGHT() portion, solved my need for an Airtable split string formula. Thank you so very much!
May 19, 2021 01:17 AM
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?
May 19, 2021 01:55 PM
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}(.*)")
May 20, 2021 10:18 PM
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.
May 21, 2021 01:24 AM
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?
May 21, 2021 01:47 PM
@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?