How to insert leading zeroes into mixed letter number field

**dcphotog** · 05-05-2007, 06:44 PM

I have a column of data that is given to me that is a mix of letters and numbers and I need the numbers to have leading zeros, they must all be three digits. The data has either 3, 4, or 5 letters followed by numbers 1 through 999. Example: ABCD7 I need to change it to ABCD007. Any help on automating this would be greatly appreciated. I am using Excel 2004 for the Mac.

Thanks very much,
David

**daddylonglegs** · 05-05-2007, 07:48 PM

Hello David, it's a little long but you could use this formula in another column. Assuming your data in A1 then use this in B1 copied down:

=LEFT(A1,LEN(A1)-LOOKUP(1000,RIGHT(A1,{1,2,3})+0,{1,2,3}))&TEXT( LOOKUP(1000,RIGHT(A1,{1,2,3})+0),"000")

If you then want to replace your data with the new column then convert to values by copying new column and then using Edit > Paste Special > Values

**dcphotog** · 05-05-2007, 09:41 PM

daddylonglegs, thank you for the formula. It did not seem to work when I pasted it, Excel gave me a warning that there was a circular reference in an open workbook and it returned 0 as the value in column B. I attached a sample workbook with the A column i am trying to work with. Again thanks for your help.

**daddylonglegs** · 05-06-2007, 06:13 AM

It worked fine for me when I tried it on your data. Are you putting the formula in B1 and copying down?

**dcphotog** · 05-06-2007, 01:55 PM

I pasted it into B1 and copied it down. I just get the circular reference warning and zeroes in the B column.

**daddylonglegs** · 05-06-2007, 02:17 PM

OK, but I can't see what the problem can be. Are you using an English language version of Excel? here's a copy of your file containing the suggested formula. It appears to give the correct results, I believe, see if you can see the same.....

**dcphotog** · 05-06-2007, 02:32 PM

It is english. When i opened your file the B column came up with the correct values, but if i double click on one of the B cells the formula pops up and when i hit return the cell jumps back to showing a 0. If I copy and paste the formula out of your file and into mine it just returns a zero. I attached my file with the formula inserted. It shows up as all zeroes, how does it look on yours? I wonder if the formula somehow is not compatible with the Mac version.

**olasa** · 05-06-2007, 02:38 PM

It should work. But you could also try this one.
//Ola

**dcphotog** · 05-06-2007, 02:46 PM

olasa, that worked. Thank you both for all of your help.

**grahny** · 11-25-2008, 08:51 PM

Old thread, but I've got a somewhat related question...

Trying to format a column in which the cells will have data of 9 digits. The data will need to allow any letter/number combination, BUT, must retain leading zeros. I know how do this with just numbers (i.e. 000000000), but if letters are included the formatting is shot. There are columns to the left/right that can't be affected either.

Using Excel 2000 on a PC.

Any help/guidance would be greatly appreciated.