Finding symbols within text in a cell ???

[sobeitjedi]

Hi.

Column A, cells 1 to 4 has text like:

A1 = "c:\file1.txt"
A2 = "c:\file2$.txt"
A3 = "c:\file7+.txt"
A4 = "c:\file12.txt"

Ideally, what I would like is for the cell to be highlighted (conditional formatting?) if the cell contains any + or $ or % or { or &, etc - so in the example above, A2 and A3 would be highlighted because they contain one or more of those symbols, A1 and A4 would remain untouched.

Any suggestions?

 

[ParamRay]

.
.

Select your 4 cells and then create a conditional formatting rule with this formula:

=OR(ISNUMBER(SEARCH("+",A1)),ISNUMBER(SEARCH("$",A1)),ISNUMBER(SEARCH("%",A1)),ISNUMBER(SEARCH("{",A1)),ISNUMBER(SEARCH("&",A1)))

 

[Special-K99]

I think the OP is looking for other characters as well as he states "etc"
What youve done is what I started to do, then noticed the "etc" at which point I gave up solving this since the OP has not clearly specified what characters he wishes to search for.

 

[sobeitjedi]

The characters I wish to highlight are:

Tilde
Number sign
Percent
Ampersand
Asterisk
Braces
Backslash
Colon
Angle brackets
Question mark
Slash
Pipe
Quotation mark

 

[FormR]

Hi, create a list of your special characters and name the range "SYMBOLS" - note, you will need to escape any wild card characters with the tilde (~) character (see rows 1, 6 and 13 in the example table)

And then use:
=SUMPRODUCT(COUNTIF(A1,"*"&SYMBOLS&"*"))>0

Excel Workbook
	H
1	~~
2	+
3	-
4	%
5	&
6	~*
7	{
8	}
9	\
10	:
11	[
12	]
13	~?
14	\
15	|
16	"
17	/
Sheet1

 

[sobeitjedi]

None of these suggestions work so far - anyone else care to try?

 

[shg]

Any file path is going to have a colon and a backslash, no?

 

[sobeitjedi]

I'll remove the C:\ from my cells

 

[FormR]

None of these suggestions work so far - anyone else care to try?

You'll need to expand on "does not work" if you want to progress with my suggestion; i.e. post the list of symbols and a list of values that either didn't highlight when they should have have, or did highlight when they should not have.

 

[shg]

I think FormR's suggestion was great. If you want to limit it to valid characters for filenames,

	A​	B​	C​	D​	E​
1​	file1.txt	TRUE​	B1: =SUMPRODUCT(COUNTIF(A1, "*" & Symbols & "*")) = 0		Symbols​
2​	file2$.txt	TRUE​			"​
3​	file7+.txt	TRUE​			~*​
4​	file*.txt	FALSE​			/​
5​					\​
6​					:​
7​					<​
8​					>​
9​					~?​
10​					|​

The only thing I changed was the symbols and the comparison operator.