# "simple" search and replace

#### grasshappa

##### New Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

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

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!

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.

Replies
3
Views
429
Replies
2
Views
608
Replies
2
Views
729
Replies
6
Views
423
Replies
17
Views
874

1,218,540
Messages
6,143,098
Members
450,462
Latest member
PowerQueryKees

### 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?

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