Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate 5digits by inserting -
I have a column containing codes and I wish to separate the first digit from
the rest by inserting a hyphen. E.g 40000 to be 4-0000, 40001 to 4-0001. Any suggestions rather than going thru a list of more than 1000s? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate 5digits by inserting -
Hi!
Use a temporary helper column. Suppose your data is now in the range A1:A1000. Enter this formula in B1 and copy down to B1000: =LEFT(A1)&"-"&MID(A1,2,255) Select the range of formulas in B1:B1000 Goto EditCopy Then EditPaste SpecialValuesOK Then you can delete the original data if you'd like. Biff "Faio" wrote in message ... I have a column containing codes and I wish to separate the first digit from the rest by inserting a hyphen. E.g 40000 to be 4-0000, 40001 to 4-0001. Any suggestions rather than going thru a list of more than 1000s? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate 5digits by inserting -
If they are all 5 digit numbers.........
=LEFT(A1,1)&"-"&RIGHT(A1,4) Gord Dibben Excel MVP On Thu, 1 Dec 2005 13:59:09 +1200, "Faio" wrote: I have a column containing codes and I wish to separate the first digit from the rest by inserting a hyphen. E.g 40000 to be 4-0000, 40001 to 4-0001. Any suggestions rather than going thru a list of more than 1000s? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate 5digits by inserting -
On Thu, 1 Dec 2005 13:59:09 +1200, "Faio" wrote:
I have a column containing codes and I wish to separate the first digit from the rest by inserting a hyphen. E.g 40000 to be 4-0000, 40001 to 4-0001. Any suggestions rather than going thru a list of more than 1000s? Are they all 5 digit numbers? If so: =TEXT(a1, "0-0000") and copy/drag down as needed. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate 5digits by inserting -
Thanks Biff, Dibben and Rosenfeld.
What you gave has solved this problem in seconds rather than hours. "Ron Rosenfeld" wrote in message ... On Thu, 1 Dec 2005 13:59:09 +1200, "Faio" wrote: I have a column containing codes and I wish to separate the first digit from the rest by inserting a hyphen. E.g 40000 to be 4-0000, 40001 to 4-0001. Any suggestions rather than going thru a list of more than 1000s? Are they all 5 digit numbers? If so: =TEXT(a1, "0-0000") and copy/drag down as needed. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
Separate Groups by Inserting Lines | Excel Discussion (Misc queries) | |||
Open separate window | Excel Worksheet Functions | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) |