Problem extracting text using MID & FIND/SEARCH when text doesn't contain character

[ineedham]

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.

[Vvelarde]

@ineedham

Hi, try with the follow dax:

Column =
VAR FINDIT =
    FIND ( "#", Table4[MESSAGE], 1; 0 )
RETURN
    IF ( FINDIT > 0, MID ( Table4[MESSAGE], FINDIT+1, 3 ) )

[ineedham]

That gave me the same results as Formula 1 -- the "#" is in front of the Message order numbers.

[Vvelarde · Accepted solution]

@ineedham

Hi, please check the image.

[ineedham]

Apologies,

I missed the +1.

That worked!

Thanks.