What is the "IF" formula for searching for a certain letter or symbol in a cell? (MS Excel)?

Question

I've got a list of several thousand names. Names of people and names of companies. Each time a company is used, a specific symbol is added to show that it's a company. This symbol is sometimes before, and sometimes after the company name. I.e. the cell may or may not begin with this specific symbol.

Had it begun with this specific symbol, I know I could've used something like
=IF(LEFT(A1,1)="SYMBOL","1","0")
But since the symbol may be at any location in the cell I need it to search through the entire cell to find it. If there is such a symbol in the cell, return 1. If not, return 0.

I would rather not use any macros, but if you can't think of any formula I suppose that would work out as well.

I will make sure to select a best answer if a satisfying answer is given.

Answer 1

You can try the FIND expression.



Here's an example:



=IFERROR(FIND("SYMBOL",A1),"-1")



Will return the position of the symbol within the A1 cell, or "-1" if it's not there.



--- Later Edit ---



I can't tell why you would receive a #NAME? error. Try this function:



=IF(ISNUMBER(SEARCH("SYMBOL",A1)),1,0)



Also, please check to have all the quotes and commas.



=== Later Later Edit ===



You have to use OR(logical1, logical2, logical3,...). In this case, the logical expression is ISNUMBER(SEARCH("SYMBOL",A1)). Be careful for the parenthesis.



So, for 3 symbols you'll have something like this.



=IF(OR(ISNUMBER(

SEARCH("SYMBOL1",A1)),

ISNUMBER(SEARCH("SYMBOL2",A1)),

ISNUMBER(SEARCH("SYMBOL3",A1)))

,1,0)





P.S. I don't know the exact restrictions concerning the maximum number of parameters for the OR function, but you shouldn't have a problem.

Answer 2

=ISNUMBER(SEARCH("symbol",A1)) will give you a TRUE or FALSE result to work with.

Put your symbol in a spare cell and reference it in the formula for multiple matches ie put the symbol in X1 and change formula to read

=ISNUMBER(SEARCH($X$1,A1))

Answer 3

pass to the cellular you opt to repeat. Press F2 (edit). spotlight the letters or numbers you opt to repeat by using preserving down shift key mutually as pressing left or suitable arrow key. once you highlighted already, press Ctrl C and pass the the cellular you like paste and press Ctrl V. attempt it.