"simple" search and replace

grasshappa

New Member
Joined
Sep 5, 2006
Messages
6
It seems to me that what I'm trying to do is quite easy, but I haven't been able to figure it out. I have two lists in two columns - one is a list of six digit numbers - the other is a list of ascending seven digit numbers. For each six digit number, there is one and only one seven digit number which begins with those six digits, but the seventh digit is random. I want to replace all of the six digit numbers with their corresponding seven digit numbers. I can't figure out which combination of lookup or if or match or replace commands to use...

009988 0000014
000823 0000027
234534 0000031
more many more

Please help! Thank you very much in advance!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Not exactly sure what you mean, but perhaps a Vlookup()?

e.g.
In a new column:

=VLOOKUP(A1&"*",$B$1:$B$100,1,0) where B1:B100 house the 7-digit numbers and A1 is the 1st 6-digit number to lookup.

Copy the formula down
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Grasshappa

Welcome to the Board!

I'm not convinced I have understood you correctly, but if you mean there is only one value in the B column that would begin with the digits :

009988_

ie so the final digit could be anything (0-9), and you want to return what that full 7 digit number is (say 0099885) then you could use the following formula in column C:

=INDEX($B$1:$B$1000,MATCH(A1&"?",$B$1:$B$1000,0))

and copy down. Adjust ranges to suit.

Did this help?

Richard
 

grasshappa

New Member
Joined
Sep 5, 2006
Messages
6
YAY

Wow! You are both amazing and fast! Both formulae worked flawlessly.

For future reference, what do the arguments A1&"*" and A1&"?" mean to Excel?

Thank you so much!
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
The * and ? are wildcards.

The * after the A1 says to search for a string that begins with A1 and ends whatever number of characters, the ? searhes for A1 & any single character.
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

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