Add Dashes to Numbers in VBA

[Johnny J]

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.

 

[mikerickson]

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

 

[Johnny J]

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.

 

[Johnny J]

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.

 

[mikerickson]

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

 

[Rick Rothstein]

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...

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