Snasui Excel Forum

Code: Select all

Sub GetNumbers()

    Dim uColumn As String

   uColumn = "A"

    Dim i As Long, j As Long, r As Range
    For i = 2 To Range(uColumn & Rows.Count).End(xlUp).Row
        Set r = Range(uColumn & i)
        Dim tmpStr As String
        tmpStr = vbNullString
        For j = 1 To Len(r)
            If IsNumeric(Right(Left(r, j), 1)) Then tmpStr = tmpStr & Right(Left(r, j), 1)
        Next j
        r.NumberFormat = "#,##0"
        r = tmpStr
    Next i

End Sub

Code: Select all

Dim rAll As Range, r As Range
Dim i As Integer, t As String
With Sheets("Sheet1")
    Set rAll = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
    rAll.Offset(0, 2).NumberFormat = "#,##0.00"
End With
For Each r In rAll
    t = ""
    For i = 1 To Len(r)
        If Mid(r, i, 1) Like "#" Or Mid(r, i, 1) = "." Then
            t = t & Mid(r, i, 1)
        End If
    Next i
    r.Offset(0, 2).Value = t
Next r