Match with tilda characater

[IanW]

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

 

[Trevor Shuttleworth]

Ian

Try d~~ in cell B1 (that is d ~ ~ (without the spaces))

Regards

Trevor

 

[Biff]

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]

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]

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]

Thanks - so ~ is essentially the escaping character equivalent of the '\'
in C/C++ etc.

Thanks for taking the time to help
I.

 

[Biff]

You're welcome.

Biff