Help

Extract Somewhat Inconsistent Text and Delete

Topic Labels: Formulas
976 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Howard_Goldstei
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!  I have text of the form:

10 x 8 inches (image)
25.4 x 20.3 cm
16 1/8 x 13 5/8 inches (frame)
41 x 34.6 cm

in a field called Image Size.  This is how the text came in from an Excel spreadsheet.  There did not seem to be a Delimiter I could successfully use to separate the text previous to my importing it to Airtable.  I want to take the string on the line that precedes "(frame)" and that follows "(frame)" (basically the inch and cm dimensions of the frame as opposed to the image size) and extract it to a new field (the formula field), while, hopefully also deleting it and the cm dimensions following "(frame)" on the next line from the original Image Size field.  Sometimes "(frame)" shows as  "(frame size)".  Thank you very much in advance for any assistance!

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

If it's always in four lines, you could try: 

 

REGEX_EXTRACT(
  Text,
  '^.*\\r?\n(.*)'
)
& "\n" & 
REGEX_EXTRACT(
  Text,
  '^.*\\r?\n.*\\r?\n.*\\r?\n(.*)'
)

 

And this would always extract the second and fourth line

Screenshot 2023-02-12 at 11.56.45 AM.png

Not sure I fully understand you though.  If I've got something wrong could you provide me with example output and I'll see what I can do?

Sorry, I should have been clearer- I actually want the 3rd and 4th lines. But it is not always 4 lines, and sometimes the 3rd and 4th lines don’t exist, which or no data exists in the field.