Getting numbers from a string/cell

[SQUIDD]

Hi all

So I need to get just numbers from a cell or string.

I can obviously do a replace A with “”. And b,c,d and so on. On 26 lines.

Anyway I can write it shorter.

Like column(1).replace((“a,b,c”),””)

I’m just trying to get the numbers from a string. Using vba. Not formula.

Thanks.

Dave.

 

[johnnyL]

The following checks A3 and returns the numbers from it.

    s = Range("A3")
'
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            AllNumbersString = AllNumbersString + Mid(s, i, 1)
        End If
    Next

MsgBox AllNumbersString

 

[johnnyL]

This would do a range:

    Dim cell                As Range
    Dim AllNumbersString    As String
'
    For Each cell In Range("A1:A10")
        For i = 1 To Len(cell.Value)
            If Mid(cell.Value, i, 1) >= "0" And Mid(cell.Value, i, 1) <= "9" Then
                AllNumbersString = AllNumbersString + Mid(cell.Value, i, 1)
            End If
        Next
'
        cell.Value = AllNumbersString
        AllNumbersString = ""
    Next

 

[SQUIDD]

Hi Johnny.

Thanks for the help.
I have implemented this into my code.

Perfect ?

Dave.

 

[johnnyL]

Glad to help.