Skip to main content

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.

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))

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


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


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

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


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


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.


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

Reply


Terms & Conditions