Vlookup with | and ~ characters

[msbetty]

I am trying to do a Vlookup with a string that has the | (pipe) and ~ (tilde) characters in it.

Vlookup doesn't appear to recognize or be able to do a match on these character.

My string looks something like this - 112|a3457|lmpop|~|~

And yes, I do need all these elements.

I've formatted both the target and source to be consistent.

I've copied a cell from the target and put it in the source and the Vlookup didn't recognize it.

I've tried a find and replace on these characters and Find didn't find them.

I've an ugly feeling that I'm doomed.

Any ideas, anyone?

 

[barry houdini]

What's the actual lookup value?

pipes should be OK but ~ is used when you want to look for a literal wildcard, e.g. to find an actaul asterisk you search for "~*"

Similarly to search for tilde you need two.

If lookup value is A2 then try replacing with

=SUBSTITUTE(A2,"~","~~")

for example in the VLOOKUP like

=VLOOKUP(SUBSTITUTE(A2,"~","~~"),F2:G10,2,0)

 

[msbetty]

Hi Barry,

The string in my original post is the match field from source tab to target tab.

I've not used the SUBSTITUTE Function before.

I'll give it a whirl.

b

 

[msbetty]

Hi Barry,

Success!!! (sort of)

I now have a #Value error on the items that should have brought back some data, but I think that is a different problem.

The Substitute command worked great!

Thank you very much!

b