Lookup strings with ~ and ^ characters

[Guest]

Hi,
I am trying to perform a lookup function on tow columns contaning data, the
data is text strings with the values '~' and '^' included in some cases eg:

BCH^~BCH
BOS^~BOS^~5FW-BW Burnham W

When I use the VLOOKUP formula to compare the data, when the strings are
identical in both columns I get the '#N/A' value returned when I should get
blank. I get the '#N/A' value returned even when the values are different or
not found when I should just get the string returned.

I think that the characters '~' and '^' are causing the VLOOKUP function to
behave differently. Is there something that I am not doing correctly here?

Many thanks

Kevin

 

[Bob Phillips]

Use something like

=VLOOKUP(SUBSTITUTE(G11,"~","~~"),I11:J14,2,FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Guest]

The tilde (~) is used to flag a wildcard character as a regular character to
be matched. For instance to match anything beginning with the letter "A",
you would use this: A*. But to match "A" followed by an asterisk, you'd use
A~*

So...Try something like this:

For a lookup value in A1

B1: =VLOOKUP(SUBSTITUTE(A1,"~","~~"),(your lookup range),2,0)

That formula replaces tildes in A1 (~) with 2 tildes (~~)
So if A1 contained 123~456, the formula would convert it to 123~~456.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Bob, Ron beat you to it.
Kevin
--
Kevin

Use something like

=VLOOKUP(SUBSTITUTE(G11,"~","~~"),I11:J14,2,FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Guest]

Thanks Ron, that works a treat!
Kevin
--
Kevin

The tilde (~) is used to flag a wildcard character as a regular character to
be matched. For instance to match anything beginning with the letter "A",
you would use this: A*. But to match "A" followed by an asterisk, you'd use
A~*

So...Try something like this:

For a lookup value in A1

B1: =VLOOKUP(SUBSTITUTE(A1,"~","~~"),(your lookup range),2,0)

That formula replaces tildes in A1 (~) with 2 tildes (~~)
So if A1 contained 123~456, the formula would convert it to 123~~456.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

No he didn't. Mine is timed at 16:58, his is 17:03 <G>

 

[Dave Peterson]

Excel supports wild cards (* and ?, any set of characters and any single
character).

The tilde is used to tell excel that you don't mean the wildcard--you actually
mean that character ~* and ~?.
Since tilde has a special purpose, you have to treat it special too: ~~.

And if your data has asterisks and question marks, you may want something like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)