Change text entry to sort accurately
 

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?

Change text entry to sort accurately
 

Try this formula...

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

Jim Thomlinson


"Alice M." wrote:

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?

Change text entry to sort accurately
 

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

"Alice M." wrote:

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?

Change text entry to sort accurately
 

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?


"Jim Thomlinson" wrote:

Try this formula...

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

Jim Thomlinson


"Alice M." wrote:

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?

Change text entry to sort accurately
 

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 EditPaste Special(in place)ValuesOKEsc.

Delete Column B


Gord Dibben MS Excel MVP

On Fri, 8 Aug 2008 14:55:01 -0700, Alice M.
wrote:

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?


"Jim Thomlinson" wrote:

Try this formula...

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

Jim Thomlinson


"Alice M." wrote:

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?

Change text entry to sort accurately
 

Thank you for your post. It worked great.

"Teethless mama" wrote:

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

"Alice M." wrote:

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?

Change text entry to sort accurately
 

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.

"Gord Dibben" wrote:

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 EditPaste Special(in place)ValuesOKEsc.

Delete Column B


Gord Dibben MS Excel MVP

On Fri, 8 Aug 2008 14:55:01 -0700, Alice M.
wrote:

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?


"Jim Thomlinson" wrote:

Try this formula...

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

Jim Thomlinson


"Alice M." wrote:

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?