| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto entry of data based on entry of text in another column or fie | Excel Discussion (Misc queries) | |||
| How do I change the case of text in more than one entry at a time | Excel Discussion (Misc queries) | |||
| How to automatically change a single letter text entry to caps?? | New Users to Excel | |||
| automatically change text case on entry | Excel Discussion (Misc queries) | |||
| How do you change sort defaults for combined text number & hyphen. | Excel Discussion (Misc queries) | |||
Linear Mode
