Automatically reformat an alphanumeric text string

New 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

 

How?

 

Thanks

 

5 Replies

@latkahead 

=CONCATENATE(LEFT(A1,3),"-",MID(A1,4,4),"-",RIGHT(A1,4))

 

Instead of copy/paste you can try this formula.

Thanks, that worked.

 

 

@latkahead If you have 365, you could use:

="D30-"&TEXT(SEQUENCE(100),"000#")&"-3405"

Adapt as needed. 

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