identify whether cell contains one of several characters

SDTakade

New Member
Joined
Oct 3, 2006
Messages
4
Trying to design a formula that will tell me if a text string contains any of the characters in a specific range. The range (named INVALID_ CHARACTERS) contains the values "!", "@", "#", "^", "(", ")", "_", ",", "." (without the quotes). Each character is in a separate cell, range INVALID_CHARACTERS) is D1:D9. For example, if cell A1 contains the value "a#d1" the value in cell B1 would by "yes". I thought the FIND () function could be used but can't get it to work using a range as first argument.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
=IF(ISNUMBER(SEARCH("^",A1)),"Good!", "Bad!")


From any other cell: Check to see if any of the text in cell A1 contains "^" somewhere in it. Note the formula returns "Good!" if the cell does contain "^" in any part of it!
 

SDTakade

New Member
Joined
Oct 3, 2006
Messages
4
Yes I know I can check for each character in the list separately by using the formula you suggest. I'm looking for a formula (probably array) that will check for whether any of the characters in the named range (list of 9 characters) are in the string in cell A1.
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
try;

Code:
=IF(IF(ISERROR(SEARCH(D1,A1)=TRUE),0,SEARCH(D1,A1))+IF(ISERROR(SEARCH(D2,A1)=TRUE),0,SEARCH(D2,A1))+IF(ISERROR(SEARCH(D3,A1)=TRUE),0,SEARCH(D3,A1))+IF(ISERROR(SEARCH(D4,A1)=TRUE),0,SEARCH(D4,A1))+IF(ISERROR(SEARCH(D5,A1)=TRUE),0,SEARCH(D5,A1))+IF(ISERROR(SEARCH(D6,A1)=TRUE),0,SEARCH(D6,A1))+IF(ISERROR(SEARCH(D7,A1)=TRUE),0,SEARCH(D7,A1))+IF(ISERROR(SEARCH(D8,A1)=TRUE),0,SEARCH(D8,A1))+IF(ISERROR(SEARCH(D9,A1)=TRUE),0,SEARCH(D9,A1))>0,"yes","no")
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi SDTakade

One possible solution, using Find, is:

Code:
=IF(SUM(--ISNUMBER(FIND($D$1:$D$9,A1))),"Yes","No")
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

I don't know if it interests you, but an alternative that would give you more information would be:

Code:
=SUM(--ISNUMBER(FIND($D$1:$D$9,A1)))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

This would give you the number of different invalid characters A1 contains. In the case of ""a#d1" would have the result 1, in the case "a#d1,#)" would have the result 3 because it contains three different invalid characters: "#", "," and ")".

Hope this helps
PGC
 

SDTakade

New Member
Joined
Oct 3, 2006
Messages
4
Thanks for all the answers!

They are useful. Just a quick question - in the array formulas what is the purpose the two minus operatorsjust before the ISNUMBER? The array formula doesn't work without them.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi SDTakade

ISNUMBER returns TRUE or FALSE

SUM expects numbers. The two minus signs convert the logical values to numbers.

If you convert a logical to number, False => 0 and True => 1.

--False=-(-False)=-(0)=0
--True=-(-True)=-(-1)=1

So SUM adds 1 unit for each True value.

Kind regards
PGC
 

SDTakade

New Member
Joined
Oct 3, 2006
Messages
4
Great! Thanks for the explanation - I'm just getting used to the logic used in array formulas and this helps alot!!
 

Forum statistics

Threads
1,141,718
Messages
5,708,072
Members
421,544
Latest member
Keanna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top