CODEWITHSUNDEEP
excelexcel-formula
Alexander
Alexander

Reputation: 4645

Extracting digits from strings in excel with condition

Say we have strings like this and want to output digits out from it. The digits is not always in the beginning so I think I need to define condition as well.

Tried this Excel: Extract Numbers from Date Strings but not working

enter image description here

how can we extract the digits from this kind of string in excel?

Upvotes: 0

Views: 58

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

If you prefer a formula solution, you can use:

=AGGREGATE(14,6,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDEX($A:$A,1):INDEX($A:$A,LEN(A1)))),1)

If you have O365 with the most recent updates, you can use:

=AGGREGATE(14,6,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SEQUENCE(LEN(A1))),1)

If you need to check for no-digits in the string, you can wrap the formula in IFERROR. eg:

=IFERROR(AGGREGATE(14,6,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SEQUENCE(LEN(A1))),1),"")

enter image description here

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Congrats, today is the day you are going to add some knowledge to your arsenal. This is how the result would look, if you add a vba formula to it:

enter image description here

In order to add the formula, press Alt+F11 and paste the following code either in Modul1 or in the Worksheet:

Public Function ExtractString(myRange As Range) As String

    Dim i As Long
    Dim result As String
    Dim currentString As String
    Dim okIndex As Long

    okIndex = 1
    result = ""

    For i = 1 To Len(myRange.Text)
        currentString = Mid(myRange.Text, i, 1)
        If IsNumeric(currentString) And okIndex >= 1 Then
            result = result & currentString
            okIndex = okIndex + 1
        Else
            If okIndex > 1 Then okIndex = -1
        End If
    Next

    ExtractString = result

End Function

Upvotes: 1

Related Questions