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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
=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!
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Great! Thanks for the explanation - I'm just getting used to the logic used in array formulas and this helps alot!!
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top