Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
Hi
I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
Hi
The tilde ~ is used as a wild card. You could use something like =VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0) where you are substituting the tilde with something that is unlikely to be found within the text entries of column J. -- Regards Roger Govier "JB2010" wrote in message ... Hi I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
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) JB2010 wrote: Hi I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
Hi Roger
Many thanks for that, did not know that it was used as a wild card, so I have learned something new today! I can see the logic of your suggested solution, but I can only get it to bring back a #VALUE error, could this be because we are trying to amend the text in a range as well as a single cell? cheers jb "Roger Govier" wrote: Hi The tilde ~ is used as a wild card. You could use something like =VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0) where you are substituting the tilde with something that is unlikely to be found within the text entries of column J. -- Regards Roger Govier "JB2010" wrote in message ... Hi I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
Dave
that works a treat. thanks for explaining cheers jb "Dave Peterson" wrote: 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) JB2010 wrote: Hi I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup & "~" symbol
Hi
Dave gave you the correct description about the tilde, it is used to tell excel to disregard the wildcards * and ?, not to be used as a wildcard itself. As for the formula, it worked fine for me when I tested it. I had Sales~Dept in A1, and in J1 Accounts~Dept, J2 Accounts Dept, J3 Sales~Dept, J4 Sales Dept It returned the value of 30 from cell K3 -- Regards Roger Govier "JB2010" wrote in message ... Hi Roger Many thanks for that, did not know that it was used as a wild card, so I have learned something new today! I can see the logic of your suggested solution, but I can only get it to bring back a #VALUE error, could this be because we are trying to amend the text in a range as well as a single cell? cheers jb "Roger Govier" wrote: Hi The tilde ~ is used as a wild card. You could use something like =VLOOKUP(SUBSTITUTE(A1,"~","^^"),SUBSTITUTE(J1:K10 0,"~","^^"),2,0) where you are substituting the tilde with something that is unlikely to be found within the text entries of column J. -- Regards Roger Govier "JB2010" wrote in message ... Hi I think i must have missed something in my Excel learning... What's the deal with the "~" symbol? if you try to look something up & the text has one of those in it, it messes up the vlookup. is this a pretty standard thing that has just passed me by all these years? is there anyway round it? cheers jb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop Excel from replacing "(c)" with copyright symbol? | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Truncated font list in Excel's "Insert Symbol" menu selection | Excel Discussion (Misc queries) |