Sophie has attended:
Excel Advanced course
Adding dashes
Hi
I am trying to convert the number on the left to the number on the right with underscore and dashes.
I have lots of cells that I need to do this to.
DH000172514 23 D_H0001-725-14
I went to format cells and custom and did #_####-###-## but my sample did not change - what am I doing wrong?
Thanks
Sophie
RE: 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
