convert data to include leading zeros

  • Thread starter Thread starter BCP
  • Start date Start date
B

BCP

I want to convert a column of data to include leading zeros so that I can
sort the column properly. There are over 500 entries so I don't want to do
it by hand.

Here's an example of the data, original data on the left, converted on the
right:
1.1.0.1 01.01.00.01
1.1.1.0 01.01.01.00
10.2.75.0 10.02.75.00
10.6.2.0 10.06.02.00
11.0.0.0 11.00.00.00
11.1.1.3 11.01.01.03
4.8.4.2 04.08.04.02
5.10.14.2 05.10.14.02

Does anyone know a way I could do this in Excel?
Or perhaps a program on the internet?
Thanks
 
With your values starting in A2, enter these formulas:

B2
=IF(FIND(".",A2)=2,"0" & A2,A2)

C2
=IF(FIND(".",B2,4)=5,LEFT(B2,3) & "0" & MID(B2,4,LEN(B2)),B2)

D2
=IF(FIND(".",C2,7)=8,LEFT(C2,6) & "0" & MID(C2,7,LEN(C2)),C2)

E2
=IF(LEN(D2)=10,LEFT(D2,9) & "0" & RIGHT(D2,1),D2)

Then copy down to match your data. Convert E from formulas to values, and delete columns A:D.

HTH,
Bernie
MS Excel MVP
 
I'd insert some empty columns to the right and...

Select the column
Data|text to columns (xl2003 menus)
delimited by periods

And then recombine the separated values:

=text(a1,"000.")&text(b1,"000.")&text(c1,"000.")&text(d1,"000")

(I used 3 0's. They looked like they could be IP addresses.)
 
Thank you for your reply Dave, but I've just tried Bernie's solution before
yours and that works perfectly, so I haven't tried yours, but thanks very
much for the suggestion.
 
Back
Top