Forum Discussion
Helper IProblem extracting text using MID & FIND/SEARCH when text doesn't contain character
| MESSAGE | DESIRED RESULT | FORMULA 1 RESULT | FORMULA 2 RESULT |
| Certification #1 | 1 | #1 | 1 |
| Certification #2 | 2 | #2 | 2 |
| Certification #3 | 3 | #3 | 3 |
| 2017 Live Webinar #1a | 1a | #1a | 1a |
| 2017 Live Webinar #1b | 1b | #1b | 1b |
| 2017 Fall Party | 201 | ||
| Committee Nominations | Com |
I’m working with email message data. Sometimes, there are multiple messages with the same title, distinguished by order number (#1, #2A, etc)
If the message contains a “#,” I’m looking to extract the 3 characters after the “#” sign in a the text string of the email message title. If the message does not contain the “#” character, I want to return a blank.
If I use Formula 1, it returns the blanks, but it also returns the “#” sign, which I don’t want:
Formula 1 = IFERROR(MID(Emails[Message], FIND("#",Emails[Message],1,BLANK()),3),"")
Formula 2 strips off the “#”, but it also returns the first 3 characters of the Messages that don’t contain “#,” instead of the blanks I want:
Formula 2 = IFERROR(MID(Emails[Message], FIND("#",Emails[Message],1,BLANK())+1,3),"")
I can't seem to find the proper order for the IFERROR statement and its associated blank.
