Change text entry to sort accurately

[Alice M.]

My column: Would like:

08-10 08-010
08-100 08-100
07-90 07-090
07-888 07-888

How do I add another 0 to change 08-10 to 08-010?

 

[Jim Thomlinson]

Try this formula...

=A1 & REPT("0", 6-LEN(A1))
where your data is in cell a1

 

[Teethless mama]

=LEFT(A1,3)&TEXT(MID(A1,4,3),"000")

 

[Alice M.]

Jim, at present the text I want to change is in Col B. If I insert a helper
column to the right of it, and put your formula in it, will new text appear
in place of the old, or should I insert a second helper column to display the
new data?

 

[Gord Dibben]

Enter Jim's formula in C1 and change the A1's to B1's

The new text will appear in C1

Copy down as far as you need.

Copy Column C and Edit>Paste Special(in place)>Values>OK>Esc.

Delete Column B


Gord Dibben MS Excel MVP

 

[Alice M.]

Thank you for your post. It worked great.

=LEFT(A1,3)&TEXT(MID(A1,4,3),"000")

 

[Alice M.]

Gord, thanks to both you and Jim for your posts. The formula generally did
what I expected, except that instead of changing 08-13 to 08-013, it changed
it to 08-130.