Forum Discussion

Copper Contributor

Dec 23, 2022

Automatically reformat an alphanumeric text string

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 How? Thanks

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Dec 25, 2022

latkahead

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")
  )