"simple" search and replace

[grasshappa]

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!

 

[NBVC]

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]

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]

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]

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.