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

Hi Justin,

thanks so much for this. Works like a charm!

1 Like

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

@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.

1 Like

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 .

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

1 Like