Results 1 to 6 of 6

Add Dashes to Numbers in VBA

This is a discussion on Add Dashes to Numbers in VBA within the Excel Questions forums, part of the Question Forums category; I'm looking for VBA code that will add dashes between the 9th and 10th digits and between the 7th and ...

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    5

    Default Add Dashes to Numbers in VBA

    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.

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,942

    Default Re: Add Dashes to Numbers in VBA

    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

  3. #3
    New Member
    Join Date
    Jul 2011
    Posts
    5

    Default Re: Add Dashes to Numbers in VBA

    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 by Johnny J; Aug 4th, 2011 at 02:28 PM.

  4. #4
    New Member
    Join Date
    Jul 2011
    Posts
    5

    Default Re: Add Dashes to Numbers in VBA

    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.

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,942

    Default Re: Add Dashes to Numbers in VBA

    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

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,393

    Default Re: Add Dashes to Numbers in VBA

    Quote Originally Posted by Johnny J View Post
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com