Query with wild card?

JAYLO

Board Regular
Joined
Apr 3, 2002
Messages
237
Hello,
I have 2 tables, all formatted as text.
Table one has a filed where the leading zero's are truncated. That is 0005117 appears as 5117.
Table 2 contains the leading zero's.
I am trying to write a query that will give me a match when theis occurs.

That is 0005117=5117
Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Rather than using a join between the two tables, you can use the field from one table as the criteria for the matching field in the other table. To illustrate, Table 1, field ZeroNumber and Table 2, field NoZeroNumber will be the fields that you want to join on. So, with the field from Table 2, NoZeroNumber in the Field row on the query layout, put this in the criteria row of this column:
Code:
Str(Val([Table 1].[ZeroNumber]))
This will turn the text number with leading zero into a number (without the zeros), then turn it back into a string (now without the zeros).
HTH,
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

Following on from Vic you could use the CDbl() function to convert the text values to numbers, which also removes the leading zeroes. Can you change the field types to Double or is that not an option?

Andrew
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top