Search code examples
excelformatcell

Excel extract specific number from cell

i have excel cell inputs like this:

Line 644: 6

or

Line 1004:

the only thing i need is the number next to the ":" so in the above cases i need

644 and 1004. all cells are formatted as "general" is there a quick way to remove the other not needed characters and only extract the needed numbers ?

i tried with =LEFT(Q2,4) for the first example above, but this gives me an error "The formula you typed contains an error"

Solution

  • How about:

    =MID(MID(A1,1,FIND(":",A1)-1),FIND(" ",MID(A1,1,FIND(":",A1)-1))+1,9999)

    enter image description here

    MUCH more reliable would be:

    Public Function GetNumber(r As Range) As Variant
    Dim arr, L As Long, i As Long, CH As String, temp As String

    GetNumber = ""
    arr = Split(r.Value, ":")
    L = Len(arr(0))
    CH = ""
    temp = ""

    For i = L To 1 Step -1
        CH = Mid(arr(0), i, 1)
        If CH Like "[0-9]" Then
            temp = CH & temp
        Else
            GetNumber = temp
            Exit Function
        End If
    Next i

    GetNumber = temp
End Function

    Because this UDF() does not rely on a single space before the number.

    BY THE WAY:

    if a formula like:

     =LEFT(Q2,4)

    does not work, next try:

     =LEFT(Q2;4)

    instead.