is it possible to do lookup with cells containing tildes "~"?
 

Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as well
? if not then I guess I'll have to replace all my tildes in my cells to other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Try using 2 of them:
" ~~ "
The first tells XL to look for the second.
Same as using the tilde to tell XL to look for the " * " or the " ? ".
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Still need more info.

I'm matching some titles like
1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

What does "matching" mean? "Matching" them to what? Do you want to know if
an entry is on 2 different lists?

Biff

"Steven Vegeta" wrote in message
...
Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and
I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles
are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the
lookup
actually failed when the cell contains one or more tildes "~" (well
never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

If you wanted to find

New DVD ~ Rocky Series

in the sheet, using Edit=Find, you would use

New DVD ~~ Rocky Series

You would put two tilde's in for the search target.

--
Regards,
Tom Ogilvy


"Steven Vegeta" wrote in message
...
Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and
I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles
are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the
lookup
actually failed when the cell contains one or more tildes "~" (well
never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Sorry Biff - my case is as follow:

I have 2 files - the first file containing the following lines (selected DVD
titles only), and the other one is a master file which has all titles and
it's stock availability (numbers).

I'm trying to use vlookup to get the stock availability for those selected
DVD titles, but then I found out that vlookup can only return the stock
availability for those titles without a tilde, while I'm 100% sure all titles
in my first file do exist in the master file.

I just did a test and it seems all titles with those wildcards "~" "*" "?"
are not vlookup-able at all......I may ask my colleagues to change the titles
then - if I can't get any workaround (apart form renaming the titles) from
experts like you :-)

Many Thanks.

"Biff" wrote:

Still need more info.

I'm matching some titles like
1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

What does "matching" mean? "Matching" them to what? Do you want to know if
an entry is on 2 different lists?

Biff

"Steven Vegeta" wrote in message
...
Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and
I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles
are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the
lookup
actually failed when the cell contains one or more tildes "~" (well
never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Yes Tom I was able to find the titles using "~~" - but when it comes to
thousands of lines I don't think I can do it manually :-(

Please see my reply to biff for more details....many thanks anyway

"Tom Ogilvy" wrote:

If you wanted to find

New DVD ~ Rocky Series

in the sheet, using Edit=Find, you would use

New DVD ~~ Rocky Series

You would put two tilde's in for the search target.

--
Regards,
Tom Ogilvy


"Steven Vegeta" wrote in message
...
Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and
I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles
are.

Biff

"Steven Vegeta" wrote in message
...
Hi there,

I've been using vlookup on my tables and just discovered that the
lookup
actually failed when the cell contains one or more tildes "~" (well
never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as
well
? if not then I guess I'll have to replace all my tildes in my cells to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

Use the SUBSTITUTE() function on your values when you lookup for them
=VLOOKUP( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( value, "~", "~~" ), "?", "~?"
), "*", "~*" ), table, FALSE )
--
Festina Lente


"Steven Vegeta" wrote:

Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as well
? if not then I guess I'll have to replace all my tildes in my cells to other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

is it possible to do lookup with cells containing tildes "~"?
 

See the reply by PapaDos. It'll work.

It seems like you are inclined to remove/change all those tildes/special
characters. I think that would be a good idea then a basic Vlookup would be
sufficient.

Biff

"Steven Vegeta" wrote in message
...
Sorry Biff - my case is as follow:

I have 2 files - the first file containing the following lines (selected
DVD
titles only), and the other one is a master file which has all titles and
it's stock availability (numbers).

I'm trying to use vlookup to get the stock availability for those selected
DVD titles, but then I found out that vlookup can only return the stock
availability for those titles without a tilde, while I'm 100% sure all
titles
in my first file do exist in the master file.

I just did a test and it seems all titles with those wildcards "~" "*" "?"
are not vlookup-able at all......I may ask my colleagues to change the
titles
then - if I can't get any workaround (apart form renaming the titles) from
experts like you :-)

Many Thanks.

"Biff" wrote:

Still need more info.

I'm matching some titles like
1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

What does "matching" mean? "Matching" them to what? Do you want to know
if
an entry is on 2 different lists?

Biff

"Steven Vegeta" wrote in message
...
Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles -
and
I
need to get the matching ones.

Many Thanks.
Steven

"Biff" wrote:

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles
are.

Biff

"Steven Vegeta" wrote in
message
...
Hi there,

I've been using vlookup on my tables and just discovered that the
lookup
actually failed when the cell contains one or more tildes "~" (well
never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes
as
well
? if not then I guess I'll have to replace all my tildes in my cells
to
other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta