Separate 5digits by inserting -

F

Faio

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?
 
B

Biff

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 Edit>Copy
Then Edit>Paste Special>Values>OK

Then you can delete the original data if you'd like.

Biff
 
G

Gord Dibben

If they are all 5 digit numbers.........

=LEFT(A1,1)&"-"&RIGHT(A1,4)


Gord Dibben Excel MVP
 
R

Ron Rosenfeld

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?
Click to expand...

Are they all 5 digit numbers?

If so:

=TEXT(a1, "0-0000")

and copy/drag down as needed.


--ron
 
F

Faio

Thanks Biff, Dibben and Rosenfeld.

What you gave has solved this problem in seconds rather than hours.
 
Top