IF statement with FIND not behaving as expected

[Zembu]

Hello Friends

In this;

=IF(FIND("(",B2),(LEFT((B2),LEN(B2)-LEN(B2)+FIND("(",B2)-2)),B2)

I am trying to have it return (if there is no "(" in the cell B2), the contents of cell B2, and if there is a "(" it should return the part of the contents of cell B2 before the "(" and also minus a further character (a space).

I works correctly if there is a "(", but if no "(" it just states #VALUE!

 

[ExcelToDAX]

That is because "If find_text does not appear in within_text, FIND and FINDB return the #VALUE! error value."

Try this formula:

=IF(ISNUMBER(FIND("(",B2)),(LEFT((B2),LEN(B2)-LEN(B2)+FIND("(",B2)-2)),B2)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 or 2024 how about

=TRIM(TEXTBEFORE(B2,"(",,,1))

 

[Zembu]

That is because "If find_text does not appear in within_text, FIND and FINDB return the #VALUE! error value."

Try this formula:

=IF(ISNUMBER(FIND("(",B2)),(LEFT((B2),LEN(B2)-LEN(B2)+FIND("(",B2)-2)),B2)

Many thanks ExcelToDAX - that works perfectly!

 

[Zembu]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 or 2024 how about

=TRIM(TEXTBEFORE(B2,"(",,,1))

Many thanks Fluff - I have updated now as you suggested. Am using the 2016 version of Office on a Windows PC, so I guess yours won't work for me.
The solution by ExcelToDAX worked

 

[Fluff]

I have updated now as you suggested.

Thanks for that.

I guess yours won't work for me

No it wont.