Add Dashes to Numbers in VBA

Johnny J

New Member
Joined
Jul 25, 2011
Messages
6
I'm looking for VBA code that will add dashes between the 9th and 10th digits and between the 7th and 8th digits of a 10 digit number.

For example: 1234567003 would convert to 1234567-00-3

I need to code to include all numbers in Column B. One twist is that some of the numbers are already formatted with the dashes so, I would need to ignore those.

Thank you,

Johnny J.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
If you need the actual string, you could use

=REPLACE(REPLACE(SUBSTITUTE(A1,"-",""), 9, 0, "-"), 7, 0, "-")

OR you could format the cell with the custom format 0000000-00-0, so that the number
1234567003 would be shown as 1234567-00-3
 
Upvote 0
I already have the custom format at 000000-00-0 but the cell value still equals the number without the dashes. I need VBA code to searh all values in column C and add the dashes to the numbers so I can do a VLOOKUP on them where the lookup value contains the dashes. Hopefully that made sense. Or, maybe I am going about it the wrong way and you may have another suggestion.
 
Last edited:
Upvote 0
Please ignore my prior post. The formula:

=REPLACE(REPLACE(SUBSTITUTE(A1,"-",""), 9, 0, "-"), 7, 0, "-")

does the job I want it to do but I would like to run code in Visual Basic
on all cells C3 to the last cell with a value in that same column. I would like the code to replace the value that is in each cell with the new formatted value that contains the dashes.

I don't have much experience in VBA so please bear with me.
 
Upvote 0
Code:
With Sheet1
    With Range(.Cells(3,3), .Cells(.Rows.Count,3).End(xlup))
        With .Offset(0, Sheet1.UsedRange.Columns.Count +3)
            .FormulaR1C1 = "=REPLACE(REPLACE(SUBSTITUTE(RC3,""-"",""""), 9, 0, ""-""), 7, 0, ""-"")"
         End With
         .Value = .Offset(0, Sheet1.UsedRange.Columns.Count +3).Value
         .Offset(0, Sheet1.UsedRange.Columns.Count +3).EntireRow.Delete
    End With
End With
 
Upvote 0
I would like to run code in Visual Basic
on all cells C3 to the last cell with a value in that same column. I would like the code to replace the value that is in each cell with the new formatted value that contains the dashes.

I don't have much experience in VBA so please bear with me.
Give this macro a try...
Code:
Sub AddDashesToNumbers()
  Dim Cell As Range
  On Error GoTo NoFilledCells
  For Each Cell In Range("C3:C" & Cells(Rows.Count, "C").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    Cell.Value = Format(Replace(Cell.Value, "-", ""), "0000000-00-0")
  Next
NoFilledCells:
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top