How Can I Insert a Character within Existing Cells?

[copyboy007]

I have a column of 10-digit numbers (some fewer than 10; some more than 10). The format of the cells is general.

How can a dash ('-') be inserted after the sixth digit for the whole column? Would it be via a macro?

 

[jim may]

Does this help?

Excel Workbook
	A	B	C
1	My Numbers	Number Length	Converted
2	3216549999	10	321654-9999
3	654987888	9	654987-888
4	159487777	9	159487-777
5	35715966	8	357159-66
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=LEN(A2)
B3	=LEN(A3)
B4	=LEN(A4)
B5	=LEN(A5)
C2	=LEFT(A2,6) &"-" & MID(A2,7,99)
C3	=LEFT(A3,6) &"-" & MID(A3,7,99)
C4	=LEFT(A4,6) &"-" & MID(A4,7,99)
C5	=LEFT(A5,6) &"-" & MID(A5,7,99)

 

[Armando Montes]

Another one:
=LEFT(A2,6)&"-"&RIGHT(A2,LEN(A2)-6)

 

[bosco_yip]

One more way:

=REPLACE(A2,6,1,MID(A2,6,1)&"-")

Regards

 

[pgc01]

Also, with the numbers in column A starting in A2, in B2:

=REPLACE(A2,7,0,"-")

Copy down

 

[copyboy007]

One more way:

=REPLACE(A2,6,1,MID(A2,6,1)&"-")

Regards

I tried this one, which worked perfectly.

One more parameter just given is to restrict this to just the cells containing ten digits. How can I exclude the others?

 

[Scott Huish]

This is the usual syntax for inserting characters with REPLACE:

=REPLACE(A2,7,0,"-")

Excel Workbook
	A	B
1	3216549999	321654-9999
2	32165499	32165499
Sheet1

 

[copyboy007]

Perfect! Thanks so much!