Number Extraction Excel

Question

&nbsp;I have a cell with theses values:14.19, 14.36, 20.22, 22.23A&nbsp;I would like to have a formula that would&nbsp; extract each of the numbers before the periods and eliminate whatever values are doubled.&nbsp;So the content of the cell above would appear in another cell as:14, 20, 22&nbsp;I've looked at this article:https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/I'm not sure it applies because they are numbers and because I don't know how many values id have in the original cell. So in this example there are 4 values in the original cell and 3 in the result cell. But the first cell could have any number of values.&nbsp;Any help is appreciated

hansvogelaar · Answer

alex2476&nbsp;
Here is a custom VBA function you can use:
Function ExtractNum(s As String) As String
    Dim d As Object
    Dim a() As String
    Dim n As String
    Dim i As Long
    Set d = CreateObject(Class:="Scripting.Dictionary")
    a = Split(s, ", ")
    For i = 0 To UBound(a)
        n = Split(a(i), ".")(0)
        d(n) = Null
    Next i
    ExtractNum = Join(d.Keys, ", ")
End Function
With a value such as 14.19, 14.36, 20.22, 22.23A in cell A2, the formula
=ExtractNum(A2)
will return 14, 20, 22.
The formula can be filled down if required.

starrysky1988 · Answer

Textsplit function is currently available to users running Beta Channel, Version 2203 (Build 15104.20004) or later on Windows and Version 16.60 (Build 22030400) or later on Mac.

=LET(a,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),b,CONCAT(IF(CODE(a)>64,"",a)),c,FLOOR(VALUE(TEXTSPLIT(b,", ")),1),TEXTJOIN(", ",,UNIQUE(INDEX(c,ROW(INDIRECT("1:"&SUM(FREQUENCY(c,c))))))))

hansvogelaar · Answer

alex2476&nbsp;
This version should work on both Mac and Windows:
Function ExtractNum(s As String) As String
    Dim d As Collection
    Dim a() As String
    Dim n As String
    Dim i As Long
    Dim r As String
    Set d = New Collection
    a = Split(s, ", ")
    For i = 0 To UBound(a)
        n = Split(a(i), ".")(0)
        On Error Resume Next
        d.Add Item:=n, Key:=n
        On Error GoTo 0
    Next i
    For i = 1 To d.Count
        r = r &amp; ", " &amp; d(i)
    Next i
    If r &lt;&gt; "" Then
        ExtractNum = Mid(r, 3)
    End If
End Function

alex2476 · Answer

Thank you so much, I will try this!

alex2476 · Answer

Ive tried the code above it works great!
A collegue of mine has the french version of excel. it does not work on her computer. its the same document on a shared Gdrive . I assume that there is no french version of the vb language . but i dont know why she gets "#value" (#valeur in french).

Forum Discussion

Number Extraction Excel

Share

Resources