Automatically reformat an alphanumeric text string

Question

The value D3000023405 is in cell A1.&nbsp; When I copy/paste A1 into cell B1, I want it to be automatically be reformatted like this:&nbsp;D30-0002-3405&nbsp;How?&nbsp;Thanks&nbsp;

oliverscheurich · Answer

latkahead&nbsp;=CONCATENATE(LEFT(A1,3),"-",MID(A1,4,4),"-",RIGHT(A1,4))&nbsp;Instead of copy/paste you can try this formula.

hansvogelaar · Answer

latkahead&nbsp;
Also asked at reformat 11 character text cell to include hyphens&nbsp;

latkahead · Answer

Thanks, that worked.&nbsp;&nbsp;

jerrydnyc · Answer

latkahead&nbsp;If you have 365, you could use:="D30-"&amp;TEXT(SEQUENCE(100),"000#")&amp;"-3405"Adapt as needed.&nbsp;

peterbartholomew1 · Answer

latkahead&nbsp;Copy/paste is usually a disaster, especially as it brings formats with it.&nbsp; Again with 365 and assuming that each value is a "D" followed by a ten-digit number, you could use= "D"&amp;TEXT(TEXTAFTER(value,"D"), "00-0000-0000")For arbitrary initial letters the formula would become= LET(
    chr, LEFT(value,1),
    chr &amp; TEXT(TEXTAFTER(value,chr), "00-0000-0000")
  )

Forum Discussion

Automatically reformat an alphanumeric text string

Share

Resources