Replacing - with . macro

[alesha711]

I have numbers in column A (The number of rows will vary) that are in this format:
0000-000-000

I want them to be in this format:
0000000.000

I've tried several macros with no luck.

Thanks in Advance!

[6StringJazzer]

Try this, and apply custom format

0.000

to the results. This puts the results in column B so you don't lose the original data. If you want to overwrite the original data, change "B" to "A".

Private Sub test()

   Dim R As Long
   Dim S As String
   R = 1
   Do Until Cells(R, "A") = ""
      S = Cells(R, "A")
      Cells(R, "B") = Left(S, 4) & Mid(S, 6, 3) & "." & Right(S, 3)
      R = R + 1
   Loop

End Sub

[BrokenDome]

Ms. Alesha,

Good morning and hello again!! Might try something like this:

Sub changeFormat()

Dim x As Integer
Dim lastRow as Long

lastRow = Range("A95000").End(xlUp).Row

For x = 1 To lastRow

     If Range("A" & x).Value Like "####-###-###" Then

          Range("A" & x).Value = Format(Range("A" & x).Value, "#######.###")

     End If

Next x

End Sub

Hope this helps!!

Dome

[jindon]

Replace the last hyphen with a period.

Sub test()
    Dim a, i As Long
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        For i = 1 To UBound(a, 1)
            If a(i, 1) Like "*-*" Then
                a(i, 1) = "'" & Replace(Application.Replace(a(i, 1), InStrRev(a(i, 1), "-"), 1, "."), "-", "")
            End If
        Next
        .Value = a
    End With
End Sub

[alesha711]

Thanks everyone! Works great!