Adding dashes

Hi Sophie

Part of the problem is that DH0001... is not a Number
If you change the format as you suggested, it will work for all numbers but not numbers and letters.

It might be helpful to write a formula that adds in the "_" and the "-" to the character string

If you put this into A1
DH00017251423DH000172514

Paste this into B1
=CONCATENATE(LEFT(A1,14),"_",MID(A1,15,5),"-",MID(A1,20,3),"-",RIGHT(A1,2))

It should make it look like
DH00017251423D_H0001-725-14

This splits the Ref Number into the 4 parts and adds the underscore and dashes at each stage.

You can then copy and paste the "converted" ref over the top of the original (Paste special, Values/123)

Let me know if we can help you further with this.


Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector