VBA Code to Sum Numbers and Text in the same cell in excel

Step 1: Open Excel Sheet with values and numbers in a same cells.
Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window.
Step 3: Go to Insert Menu and select Module.
Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet)
Step 5: Go to the Data sheet, at last cell where you want calculate use the formula: =numb(select cells) and press enter.


Function Numb(RNG As Range) As String
    Dim TotalItems As Integer
    Dim rCell As Range
    Dim TSum As Long
    Dim TStr As String
    TSum = 0
    TVar = ""
    For Each rCell In RNG.Cells
        If rCell.Value <> "" Then
            If InStr(rCell.Value, " ") Then
                arrSplit = Split(rCell.Value, " ")
                TotalItems = UBound(arrSplit)
                    If TotalItems > 0 Then
                        For i = 0 To TotalItems
                            If IsNumeric(arrSplit(i)) Then
                                TSum = TSum + arrSplit(i)
                            Else
                                TVar = TVar & " " & arrSplit(i)
                            End If
                        Next
                     
                    End If
            Else
                arrSplit = rCell.Value
                    If IsNumeric(arrSplit) Then
                        TSum = TSum + arrSplit
                    Else
                        TVar = TVar & " " & arrSplit
                    End If
            End If
        End If
    Next rCell
    Numb = TSum & TVar
End Function