Looking for the existence of a certain character in a cell value

[StewartUK]

What formula(s) would I use to find out if a cell value has an & in it?

What I have is one column with names, which are either a single person or a couple (ie Mr Smith or Mr & Mrs Smith).

What I want to do is be able to set a cell value on the same row but in an additional column, showing whether or not the & exists in the name column.

Thanks,

Stewart

 

[jdhilljr]

=FIND("&",A1)

Jim

 

[gizmo1973]

=FIND("&",A1,1)
If your data is oin coumn A put that in column B

This would give you a number if the & occurred or a #Value if no & is present.
So you want any numbers and delete all #Value to complete your task
Quick and simple but I'm sure someone could post a better way

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[StewartUK]

Thanks Jim & Phil.

I put in the formula =IF(FIND("&",A1),2,1) )sorry I should of course have mentioned that in my first post) and found when the & is found it returns 2, but returns the #Value! when it's not found.

Hunting around, I found the ISERROR function and changed my formula to =IF(ISERROR(FIND("&",D2)),1,2), which returns 1 when there is no & in the cell and 2 when there is.

Thanks again,

Stewart