Automatically reformat an alphanumeric text string

Copper Contributor

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






5 Replies




Instead of copy/paste you can try this formula.

Thanks, that worked.



@latkahead If you have 365, you could use:


Adapt as needed. 


Copy/paste is usually a disaster, especially as it brings formats with it.  Again with 365 and assuming that each value is a "D" followed by a ten-digit number, you could use

= "D"&TEXT(TEXTAFTER(value,"D"), "00-0000-0000")

For arbitrary initial letters the formula would become

= LET(
    chr, LEFT(value,1),
    chr & TEXT(TEXTAFTER(value,chr), "00-0000-0000")