Excel IF statement not working correctly 1

[tviman]

This is the statement:

=IF(FIND(",",G4,1),RIGHT(G4,LEN(G4)-SEARCH(",",G4,1))," ")

and this is the data:

10640 Queen St.

What's supposed to happen is that if a comma is found (TRUE), the result will give whatever is to the right of the comma. If no comma is found (FALSE), then the result shoud be a space. What's happening is if no comma is found, #VALUE is the result.

Help!!!

There's always a better way. The fun is trying to find it!

 

[TonyJollans]

Hi tviman,

If FIND doesn't find the substring it's looking for, it returns #VALUE! (see Help). To check for this you need to use ISERROR; you will then get TRUE when the FIND fails and FALSE when it succeeds so must swap the next two arguments ..

[blue][tt] =IF(ISERROR(FIND(",",G4,1))," ",RIGHT(G4,LEN(G4)-SEARCH(",",G4,1)))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[tviman]

Tony - THANK YOU!!! I was pulling my hair out on this - and at my age, I can't afford to do that. Anyway - thanks for heads-up on the ISERROR function! A star for you!

There's always a better way. The fun is trying to find it!