Finding symbols within text in a cell ???

sobeitjedi · Aug 28, 2014

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 · Aug 28, 2014

.
.

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 · Aug 28, 2014

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 · Aug 28, 2014

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 · Aug 28, 2014

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 · Aug 28, 2014

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

shg · Aug 28, 2014

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

sobeitjedi · Aug 28, 2014

I'll remove the C:\ from my cells

FormR · Aug 28, 2014

sobeitjedi said:
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 · Aug 28, 2014

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

[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][/tr]
[tr][td]

1

[/td][td]file1.txt[/td][td]

TRUE

[/td][td]B1: =SUMPRODUCT(COUNTIF(A1, "*" & Symbols & "*")) = 0[/td][td][/td][td]

Symbols

[/td][/tr]

[tr][td]

2

[/td][td]file2$.txt[/td][td]

TRUE

[/td][td][/td][td][/td][td]

"

[/td][/tr]

[tr][td]

3

[/td][td]file7+.txt[/td][td]

TRUE

[/td][td][/td][td][/td][td]

~*

[/td][/tr]

[tr][td]

4

[/td][td]file*.txt[/td][td]

FALSE

[/td][td][/td][td][/td][td]

/

[/td][/tr]

[tr][td]

5