Forum Discussion
Extracting substrings: FIND throwing error despite being skipped by IF Statement
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
Yup, my bad! I did not try my example code on this example, because I assumed the error would occur as it does in the table I'm actually working on... Sorry for wasting your time! I'll go back to figuring out what makes the table I'm working on different from my example.
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