Skip to main content

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!

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

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?

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

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.

Reply


Terms & Conditions