Inset a dash after every 4 characters

[Mike Daniels]

I have a slew of 12 character serial numbers. The first 11 are numeric and the last character is an alpha (ex. 12345678901A). Would like to convert them to read 1234-5678-901A. Have tried formatting as custom, but it appears the alpha prevents that from working. Tried text to columns and successfully moved the alpha to another cell and then used custom to reformat the numerics and then tried concantenate to add the alpha back into the new custom. Doesn't work. This may be simple but I can't figure it out yet. Please assist.

 

[Trevor G]

Is this what you want.

Excel Workbook
	D	E
2	12345678901A	1234-5678-901A
Sheet1

 

[barry houdini]

You could also use REPLACE function, i.e. with data in A1 try this formula in B1

=REPLACE(REPLACE(A1,5,0,"-"),10,0,"-")

 

[Mike Daniels]

Absolutely perfect. Thank you. Now it's my job to understand that formula.

 

[Rick Rothstein]

I would use Barry's formula as that should be the most efficient, but I wanted to show you that in addition to Barry's and Trevor's formulas, there is yet another formula available...

=TEXT(LEFT(A1,11),"0000-0000-000")&RIGHT(A1)