text format

[kennethr]

I have a list of text (part numbers) such as 704A34101006 which I want to re format with dashes included i.e 704A34-1010-06, is there a simple way to do this for a large number of cells at once, they are all in the format without the dashes and I want to keep the same digits but in the fornat with dashes

Any help would be much appreciatted

 

[Jon von der Heyden]

Assuming text lengths are always the same you could use:
=LEFT(A1,6)&"-"&MID(A1,7,4)&"-"&RIGHT(A1,2)
where A1 houses 704A34101006.

Regards,
Jon

 

[Peter_SSs]

Here is another one that only requires two function calls:
=REPLACE(REPLACE(A1,11,0,"-"),7,0,"-")

 

[ravishankar]

if your text is in cell C1 then the following formula will give you the desired result

c1 = 704A34101006

=CONCATENATE(LEFT(C1,6), "-",MID(C1,7,4),"-",RIGHT(C1,2))

formula output = 704A34-1010-06

ravi

 

[Peter_SSs]

if your text is in cell C1 then the following formula will give you the desired result

c1 = 704A34101006

=CONCATENATE(LEFT(C1,6), "-",MID(C1,7,4),"-",RIGHT(C1,2))

formula output = 704A34-1010-06

ravi

ravi

This suggestion certainly works but it is just the same as Jon's since CONCATENATE does just the same job as the '&' symbols in Jon's formula. This method still requires 4 functions whereas the REPLACE method only requires 2.