Help

How to extract a word from a string?

Topic Labels: Formulas
Solved
Jump to Solution
2563 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Brian_Boyd
5 - Automation Enthusiast
5 - Automation Enthusiast

I could use some assistance with extracting a word from a string.

Here is an example of the string.
200 East Broadway #Z001 (Jacob to Seymore)

This is the formula that almost has what I need but missing something.
MID({Transaction Name},
SEARCH("#",{Transaction Name}),
SEARCH("#",{Transaction Name},
SEARCH(" “,{Transaction Name})) -
SEARCH(” ",{Transaction Name}))

I’m trying to extract the apartment number (#Z001) from the string.

Thanks in advance to anyone that tries to help with a solution.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

This could be greatly simplified by using regular expressions and the REGEX_EXTRACT() function:

IF({Transaction Name}, REGEX_EXTRACT({Transaction Name}, "#[^\\s]+"))

Screen Shot 2021-09-03 at 11.07.07 PM

That extracts everything beginning with the # symbol up to—but not including—the next whitespace in the string. I wrapped the IF() function around it to prevent an #ERROR result if {Transaction Name} is empty.

See Solution in Thread

4 Replies 4
Brian_Boyd
5 - Automation Enthusiast
5 - Automation Enthusiast

I posted the question too soon as I now have a working formula.

TRIM(MID(SUBSTITUTE({Transaction Name}," “,REPT(” “,99)),MAX(1,FIND(”#",SUBSTITUTE({Transaction Name}," “,REPT(” ",99)))-50),99))

Justin_Barrett
18 - Pluto
18 - Pluto

This could be greatly simplified by using regular expressions and the REGEX_EXTRACT() function:

IF({Transaction Name}, REGEX_EXTRACT({Transaction Name}, "#[^\\s]+"))

Screen Shot 2021-09-03 at 11.07.07 PM

That extracts everything beginning with the # symbol up to—but not including—the next whitespace in the string. I wrapped the IF() function around it to prevent an #ERROR result if {Transaction Name} is empty.

Note to self: disable out-of-office autoresponder for Airtable community forum updates. :rofl:

Thank you Justin, this works perfectly. My apologies about the autoresponder.