Forum Discussion

Copper Contributor

Mar 18, 2022

Number Extraction Excel

I have a cell with theses values: 14.19, 14.36, 20.22, 22.23A I would like to have a formula that would extract each of the numbers before the periods and eliminate whatever values are doubl...

excel

Formulas and Functions

HansVogelaar

MVP

Mar 18, 2022

alex2476

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.

alex2476

Copper Contributor

Mar 19, 2022

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).

HansVogelaar

MVP

Mar 19, 2022

alex2476

Does your colleague have a Mac? The macro that I posted will only work in the desktop version of Excel for Windows.

alex2476

Copper Contributor

Mar 19, 2022

YES she does have a Mac! Anyway to get around this?

HansVogelaar

MVP

Mar 19, 2022

alex2476

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 & ", " & d(i)
    Next i
    If r <> "" Then
        ExtractNum = Mid(r, 3)
    End If
End Function