
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 03, 2021 01:51 PM
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.
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 03, 2021 11:08 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 03, 2021 02:01 PM
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))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 03, 2021 11:08 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 03, 2021 11:25 PM
Note to self: disable out-of-office autoresponder for Airtable community forum updates. :rofl:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 04, 2021 06:23 AM
Thank you Justin, this works perfectly. My apologies about the autoresponder.
