Inset a dash after every 4 characters

Mike Daniels

Board Regular
Joined
Feb 28, 2006
Messages
73
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You could also use REPLACE function, i.e. with data in A1 try this formula in B1

=REPLACE(REPLACE(A1,5,0,"-"),10,0,"-")
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top