Extract Only Numbers & TEXT From Text String

[Rick Rothstein]

Thank you mr admin , This formula is not working after the seventh condition

That is because you are using XL2003 which has a limit of 7 nested function calls (this limit was lifted in XL2007 and above). How about two UDFs (User Defined Functions)...

Function DigitsOnly(ByVal s As String) As Variant
  Dim X As Long
  For X = 1 To Len(s)
    If Mid(s, X, 1) Like "[!0-9]" Then Mid(s, X, 1) = Chr(1)
  Next
  DigitsOnly = Replace(s, Chr(1), "")
  If Len(DigitsOnly) < 16 Then DigitsOnly = Val(DigitsOnly)
End Function

Function NoDigits(ByVal s As String) As String
  Dim X As Long
  For X = 1 To Len(s)
    If Mid(s, X, 1) Like "#" Then Mid(s, X, 1) = Chr(1)
  Next
  NoDigits = Trim(Replace(s, Chr(1), ""))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, put the specified formula in the indicated cells...

B3: =DigitsOnly(A3)

C3: =NoDigits(A3)

and then copy them down.