How to extract a word from a string?

[Brian_Boyd]

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.

[Brian_Boyd]

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 · Accepted solution]

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.

[Justin_Barrett]

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

[Brian_Boyd]

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