Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

adding dashes

ResolvedVersion 2010

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

Mon 17 Oct 2016: Automatically marked as resolved.

Excel tip:

Paste with Live Preview in Excel 2010

Did you know you can preview what you are about to paste? Here's how to do it.

Copy what you are about to paste
Position the cursor where you want to paste
Right click to display the shortcut menu
Place the cursor over the Paste Options and this will give you a preview of the what you are about to paste.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.