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

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

alex2476
Copper Contributor
Mar 20, 2022
This seems to work very well across both Platforms!

Big Thanks to you Hans!