Forum Discussion
Solved
Extracting substrings: FIND throwing error despite being skipped by IF Statement
Hi all! I'm trying to extract a column containing information (in this example: the numbers) from a column which looks something like this: INFORMATION01 INFORMATION02 ABCDEFG Info: ...
Hi PowerBINewbie7 ,
You can update the formula of calculated column as below:
Column = VAR _information = SEARCH ( "INFORMATION", 'Table'[Text], 1, 0 ) VAR _info = SEARCH ( "Info: ", 'Table'[Text], 1, 0 ) VAR _info1 = SEARCH ( "Ifo: ", 'Table'[Text], 1, 0 ) RETURN SWITCH ( TRUE (), _information > 0, RIGHT ( 'Table'[Text], 2 ), _info > 0, MID ( 'Table'[Text], VALUE ( _info ) + 6, 2 ), _info1 > 1, MID ( 'Table'[Text], VALUE ( _info1 ) + 5, 2 ), "Info not found" )Best Regards
Not a problem at all
If there are still issues, just come back to this thread.
I will try to help you best as I can
Thanks a lot!
As an update: For some reason, using SEARCH instead of FIND fixed it. I played around with my original table and "FIND"ing some strings worked fine, but not the ones I needed. The only explanation I can think of is that maybe FIND reacts to special characters (specifically: dots) in some way I was unaware of and SEARCH doesn't.
Glad, you found a way!
Only difference I know: FIND is case sensitive, SEARCH is not.
However, have a nice weekend!Hi PowerBINewbie7 ,
You can update the formula of calculated column as below:
Column = VAR _information = SEARCH ( "INFORMATION", 'Table'[Text], 1, 0 ) VAR _info = SEARCH ( "Info: ", 'Table'[Text], 1, 0 ) VAR _info1 = SEARCH ( "Ifo: ", 'Table'[Text], 1, 0 ) RETURN SWITCH ( TRUE (), _information > 0, RIGHT ( 'Table'[Text], 2 ), _info > 0, MID ( 'Table'[Text], VALUE ( _info ) + 6, 2 ), _info1 > 1, MID ( 'Table'[Text], VALUE ( _info1 ) + 5, 2 ), "Info not found" )Best Regards