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?

 

[shg]

Or, to embed the symbols in the formula,

=SUMPRODUCT(COUNTIF(A1, "*" & {"""";"~*";"/";"\";":";"<";">";"~?";"|"} & "*")) = 0

 

[sobeitjedi]

I prefer this method, but every cell comes back as TRUE, whether it contains a symbol or not

 

[FormR]

Please post an example that returns TRUE when it should be FALSE..

 

[shg]

How about the example on row 4?

 

[sobeitjedi]

Please post an example that returns TRUE when it should be FALSE..

One of my rows in column A19 contains the text:
RA1 Expenditure.xlsx

B2 where I've put the formula:
=SUMPRODUCT(COUNTIF(A19, "*" & {"""";"~*";"/";"\";":";"<";">";"~?";"|"} & "*")) = 0


returns TRUE

 

[shg]

That means it's a valid filename.

 

[FormR]

If you want it the other way, switch the comparison operator back to ">"

=SUMPRODUCT(COUNTIF(A19, "*" & {"""";"~*";"/";"\";":";"<";">";"~?";"|"} & "*")) > 0

But that doesn't explain how you are getting a TRUE result for "all" of your values.

 

[sobeitjedi]

One of my rows in column A19 contains the text:
RA1 Expenditure.xlsx

B2 where I've put the formula:
=SUMPRODUCT(COUNTIF(A19, "*" & {"""";"~*";"/";"\";":";"<";">";"~?";"|"} & "*")) = 0


returns TRUE

This returns True for all cells down the column B, whether A contains an invalid symbol or not

 

[FormR]

This returns True for all cells down the column B, whether A contains an invalid symbol or not

No one here can see the what you have in the cells in column A - can you provide examples of where it is going wrong?

Excel Workbook
	A	B	C
1	Value to Test	Does NOT contain the symbols	Does Contain the symbols
2	RA1 Expenditure.xlsx	TRUE	FALSE
3	RA1 Expendit?ure.xlsx	FALSE	TRUE
4	RA1 Exp*enditure.xlsx	FALSE	TRUE
5	RA1/Expenditure.xlsx	FALSE	TRUE
6	RA1Exp:enditure.xlsx	FALSE	TRUE
7	abc	TRUE	FALSE
Sheet1

 

[sobeitjedi]

It works when I use this formula:

=SUMPRODUCT(COUNTIF(A2, "*" & {"""";"~";"#";"%";"&";"~*";"{";"}";"$";"..";"!";"~+";"£";"^";"~*";"/";":";"<";">";"~?";"|"} & "*")) > 0

Thanks for all your help anyway.