VLOOKUP with ~

[Mark F]

Hi All

My data has all dashes displaying as "~".

When doing a VLOOKUP, any parts with the "~" (example here A~2405~4) will not register, or comes up as an error.
If I try to do a find / replace it will not find the "~"

I know it's something simple I'm doing wrong, and appreciate the help

Mark

 

[sandy666]

try:
Find: ~~
Replace: whatever you want

 

[Mark F]

Thanks

Exactly what I needed . Much appreciated

Mark

 

[sandy666]

You are welcome

Have a nice day

 

[RoryA]

Just FYI, you can do it without altering the raw data by adding a SUBSTITUTE to the VLOOKUP - for example:

=VLOOKUP(SUBSTITUTE(A1, "~", "~~"),...rest of the formula...)