Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
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 -- Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
Use something like
=VLOOKUP(SUBSTITUTE(G11,"~","~~"),I11:J14,2,FALSE) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "kmhnhsuk" wrote in message ... 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 -- Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
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 "kmhnhsuk" wrote: 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 -- Kevin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
Thanks Bob, Ron beat you to it.
Kevin -- Kevin "Bob Phillips" wrote: Use something like =VLOOKUP(SUBSTITUTE(G11,"~","~~"),I11:J14,2,FALSE) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "kmhnhsuk" wrote in message ... 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 -- Kevin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
Thanks Ron, that works a treat!
Kevin -- Kevin "Ron Coderre" wrote: 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 "kmhnhsuk" wrote: 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 -- Kevin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
No he didn't. Mine is timed at 16:58, his is 17:03 <G
"kmhnhsuk" wrote in message ... Thanks Bob, Ron beat you to it. Kevin -- Kevin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup strings with ~ and ^ characters
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) kmhnhsuk wrote: 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 -- Kevin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing characters in numeric text strings and SUMming | Excel Discussion (Misc queries) | |||
characters and strings | Excel Discussion (Misc queries) | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |