Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard in Excel
hi, I am trying to search for 10070162*** as a whole string text, the
asterisks do not represent wildcard characters. When I use 10070162*** as the lookup value in vlookup, it brought back results for 10070162001, 10070162002 etc. Is there a way to disable Excel from treating the asterisks as wildcard? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard in Excel
Use
10070162~*~*~* -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Clark Shiao" <Clark wrote in message ... hi, I am trying to search for 10070162*** as a whole string text, the asterisks do not represent wildcard characters. When I use 10070162*** as the lookup value in vlookup, it brought back results for 10070162001, 10070162002 etc. Is there a way to disable Excel from treating the asterisks as wildcard? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard in Excel
Use the tilde ~ character to override the wildcard. So, you'd search for
10070162~*~*~* HTH, Elkar "Clark Shiao" wrote: hi, I am trying to search for 10070162*** as a whole string text, the asterisks do not represent wildcard characters. When I use 10070162*** as the lookup value in vlookup, it brought back results for 10070162001, 10070162002 etc. Is there a way to disable Excel from treating the asterisks as wildcard? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard in Excel
Hi,
Thanks for the tip, but how can I incorporate the ~ into the A1 field? I have many fields that contains the wildcard characters, I don't know how to get teh ~ into the cell other than change the cell value and not use the A1 as the lookup reference. 10073661*** =VLOOKUP(A1,$A$3:$B$6,2,FALSE) 10073661010 5 10073661015 10 10073661083 15 10073661*** -17289 "Clark Shiao" wrote: hi, I am trying to search for 10070162*** as a whole string text, the asterisks do not represent wildcard characters. When I use 10070162*** as the lookup value in vlookup, it brought back results for 10070162001, 10070162002 etc. Is there a way to disable Excel from treating the asterisks as wildcard? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wildcard in Excel
=INDEX($B$3:$B$6,MIN(IF(--LEFT(IF(LEFT(A3:A6,LEN(A1))<"",A3:A6),LEN(A1))=A1
,ROW(A3:A6)-MIN(ROW(A3:A6))+1,""))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Clark Shiao" wrote in message ... Hi, Thanks for the tip, but how can I incorporate the ~ into the A1 field? I have many fields that contains the wildcard characters, I don't know how to get teh ~ into the cell other than change the cell value and not use the A1 as the lookup reference. 10073661*** =VLOOKUP(A1,$A$3:$B$6,2,FALSE) 10073661010 5 10073661015 10 10073661083 15 10073661*** -17289 "Clark Shiao" wrote: hi, I am trying to search for 10070162*** as a whole string text, the asterisks do not represent wildcard characters. When I use 10070162*** as the lookup value in vlookup, it brought back results for 10070162001, 10070162002 etc. Is there a way to disable Excel from treating the asterisks as wildcard? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Excel Open Dialog Improper Sort | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
Opening two separate instances of Excel | Excel Discussion (Misc queries) |