Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
Hello all,
I was trying to use the Match function in a VBA module (I hate VBA). Match was failing unexpectedly. I evenetually tracked down one issue (using Match in a normal worksheet not in a module). If the tilda ('~') character is in the cell I am trying to match I will not get a match. I tested this by: 1) making a small column array from A1:A6 a b c d e f 2) In cell B1 entered 'c' 3) In cell C3 entered '=Match(B1, A1:A6, 0)' - the correct result of 3 appeared in cell C3 4) I edited cell A4 to 'd~' and copied and pasted this cell to cell B1 - no match returned #N/A I tried other forms such as '~d' etc and they failed. I didn't try many other unusual characters. A simple =IF function did say the cells were the same. Is there any way of dealing with this in my script and/or on a worksheet (otehr than the obvious iteration over all elements and test with For and If statements). Thanks, Ian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
Ian
Try d~~ in cell B1 (that is d ~ ~ (without the spaces)) Regards Trevor "IanW" wrote in message .243... Hello all, I was trying to use the Match function in a VBA module (I hate VBA). Match was failing unexpectedly. I evenetually tracked down one issue (using Match in a normal worksheet not in a module). If the tilda ('~') character is in the cell I am trying to match I will not get a match. I tested this by: 1) making a small column array from A1:A6 a b c d e f 2) In cell B1 entered 'c' 3) In cell C3 entered '=Match(B1, A1:A6, 0)' - the correct result of 3 appeared in cell C3 4) I edited cell A4 to 'd~' and copied and pasted this cell to cell B1 - no match returned #N/A I tried other forms such as '~d' etc and they failed. I didn't try many other unusual characters. A simple =IF function did say the cells were the same. Is there any way of dealing with this in my script and/or on a worksheet (otehr than the obvious iteration over all elements and test with For and If statements). Thanks, Ian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
The tilde is a special character in Excel.
A4 = d~ Lookup value in B1 = d~ =MATCH(B1&"~", A1:A6, 0) Or, make the lookup value: d~~ (that's d and 2 tildes) =MATCH(B1, A1:A6, 0) Biff "IanW" wrote in message .243... Hello all, I was trying to use the Match function in a VBA module (I hate VBA). Match was failing unexpectedly. I evenetually tracked down one issue (using Match in a normal worksheet not in a module). If the tilda ('~') character is in the cell I am trying to match I will not get a match. I tested this by: 1) making a small column array from A1:A6 a b c d e f 2) In cell B1 entered 'c' 3) In cell C3 entered '=Match(B1, A1:A6, 0)' - the correct result of 3 appeared in cell C3 4) I edited cell A4 to 'd~' and copied and pasted this cell to cell B1 - no match returned #N/A I tried other forms such as '~d' etc and they failed. I didn't try many other unusual characters. A simple =IF function did say the cells were the same. Is there any way of dealing with this in my script and/or on a worksheet (otehr than the obvious iteration over all elements and test with For and If statements). Thanks, Ian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
Thanks for the reply(s). Actually I had a double trailing '~' so I would
have needed '~~~~'. I did think of checking if teh ~ was a special char. Just did look now but can't find anything about it (easily) in the help. What is ~ used for? Thanks Ian "Biff" wrote in : The tilde is a special character in Excel. A4 = d~ Lookup value in B1 = d~ =MATCH(B1&"~", A1:A6, 0) Or, make the lookup value: d~~ (that's d and 2 tildes) =MATCH(B1, A1:A6, 0) Biff |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
The tilde is sort of what you might call a wildcard "delimiter". It tells
Excel that the common wildcards, * and ?, are not wildcards in certain uses. For example: A1 = AA A2 = XX A3 = X? A4 = GH The lookup value in B1 is X?. If you use: =MATCH(B1,A1:A4,0) The result will be 2 because Excel sees the ? as a wildcard and XX matches that criteria. So, the tilde is used to tell Excel that in this case the ? is not a wildcard and is in fact just a common question mark: So, you can make the lookup value in B1: X~? =MATCH(B1,A1:A5,0) = 3 (X?) Or, make the lookup value in B1: X =MATCH(B1&"~?",A1:A5,0) = 3 (X?) Biff "IanW" wrote in message .243... Thanks for the reply(s). Actually I had a double trailing '~' so I would have needed '~~~~'. I did think of checking if teh ~ was a special char. Just did look now but can't find anything about it (easily) in the help. What is ~ used for? Thanks Ian "Biff" wrote in : The tilde is a special character in Excel. A4 = d~ Lookup value in B1 = d~ =MATCH(B1&"~", A1:A6, 0) Or, make the lookup value: d~~ (that's d and 2 tildes) =MATCH(B1, A1:A6, 0) Biff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
Thanks - so ~ is essentially the escaping character equivalent of the '\'
in C/C++ etc. Thanks for taking the time to help I. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match with tilda characater
You're welcome.
Biff "IanW" wrote in message .243... Thanks - so ~ is essentially the escaping character equivalent of the '\' in C/C++ etc. Thanks for taking the time to help I. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |