Replace a * with Numbers

[pugsly8422]

I have a list of about 1,000 numbers with the following formats:

4010*0
1100*200
1100*10

That covers pretty much all of them. They all begin with 4 numbers, followed by a *, then have either 1, 2 or 3 numbers. I need to turn each of them into 7 digit numbers, without the *. Whatever it begins with are the first numbers, and whatever it ends with are the last numbers, and however many numbers it is short of 7 are all 0's in between the first and last numbers (basically the 0's replace the *). So the ones listed above would become:

4010000
1100200
1100010

Is there a formula I could use for all of these? I have quite a few of them and am hoping there is a way to get around typing them all.

Thanks!

[Marcol]

Maybe, with your data in A2 down

Please Login or Register  to view this content.

Drag/Fill Down.

[Pete_UK]

Assuming the first one is in A1, put this formula in B1:

=LEFT(SUBSTITUTE(A1,"*","")&REPT("0",3),7)

This will return a text value. If you want it to be a number then you can do this:

=LEFT(SUBSTITUTE(A1,"*","")&REPT("0",3),7)*1

Then copy this formula down and fix the values, then you can get rid of column A.

Hope this helps.

Pete

[pugsly8422]

Worked like a charm! I really appreciate the help from both of you!