CODEWITHSUNDEEP
excelexcel-formulavba
WouterB
WouterB

Reputation: 235

Extract number sets from string, and return as comma seperated values in new string

Can anyone give me any pointers as to how to achieve this? I already managed to extract all numbers, but can't get them separated in the correct position. Hoping to achieve this through an excel function, but VBA will be OK too.

This is what I have now:

=SUMPRODUCT(MID(0&A1;LARGE(INDEX(ISNUMBER(--MID(A1;ROW($1:$25);1))*ROW($1:$25);0);ROW($1:$25))+1;1)*10^ROW($1:$25)/10)

Example of the required result:

Cell A1:                     Result:

123abc                       123
abc456                       456
123abc456                    123,456
789sometext753               789,753
6ty cents, 5%                6,5

Upvotes: 2

Views: 559

Answers (2)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

You could try this UDF:

Public Function GetNumbers(sMark As String) As String

    'regexp declaration
    Dim objRegExp As Object
    Set objRegExp = CreateObject("vbscript.regexp")

    With objRegExp
        .Global = True
        .Pattern = "(\d+)\D*"

        Dim i As Long
        If .Execute(sMark).Count > 1 Then
            For i = 0 To .Execute(sMark)(0).submatches.Count
                GetNumbers = GetNumbers & .Execute(sMark)(i).submatches(0) & ","
            Next
        Else
            GetNumbers = .Execute(sMark)(0).submatches(0) & ","
        End If
    End With
    GetNumbers = Left(GetNumbers, Len(GetNumbers) - 1)

End Function

it's tried and tested for examples you provided and hope it will work for all situations you need.

Upvotes: 1

Stepan1010
Stepan1010

Reputation: 3136

You could use this UDF:

Function GetNums(target As Range)
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Then
            MyStr = MyStr & Mid(target, i, 1)
        Else
            MyStr = MyStr & " "
        End If
    Next i
GoExit:
    GetNums = Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Trim(MyStr), " ", ",")
End Function

Upvotes: 0

Related Questions