FIND returning #VALUE! error

[Nola111]

The string in cell A1 is:

Multi-Specialty - 12254

If I put this formula in B1:

FIND("-", A1)

It returns 6.

But if I do:

FIND(" - ",A1)

I get a #VALUE ! error.

Why can't it find that string of space-hyphen-space when FIND can normally find any string of characters? How would I do a FIND for space-hyphen-space?

 

[Fluff]

It maybe that the spaces are not "normal" spaces. Try
=FIND(" - ",SUBSTITUTE(A1,CHAR(160)," "))

 

[mole999]

are those hardspaces from a download, maybe a different acsii code

 

[Nola111]

It maybe that the spaces are not "normal" spaces. Try
=FIND(" - ",SUBSTITUTE(A1,CHAR(160)," "))

This one works. Interesting. Thanks!

 

[Joe4]

It works fine for me. What that tells me is that the spaces around the "-" are probably not regular spaces, but probably special spaces (like non-breaking spaces).
I often see that with data that is downloaded from the Web or comes from other sources.

Assuming that space before the hyphen is in space 16 (as per your example), you can see exactly what is in the space with the following formula:

=CODE(MID(A1,16,1))

This returns the ASCII code for the value in the 16th space. Then you can use an ASCII table to see what that is: http://www.asciitable.com/
A regular space should return "32". If it returns anything else, it is not a typical space.

 

[Nola111]

are those hardspaces from a download, maybe a different acsii code

It did come from a website so, I suppose that could be the issue.

 

[Fluff]

This one works. Interesting. Thanks!

You're welcome & thanks for the feedback. It's a regular occurrence when copying from the web.