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?

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?

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?

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

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