These two UDFs will do what you want. You can use them as cell formulas, or call them from within other VBA.
Function TextOnly(ByVal Txt As String) As String
Dim X As Long 'Code base by Rick Rothstein (MVP - Excel)
For X = 1 To Len(Txt)
' If Mid(Txt, X, 1) Like "*[!0-9]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only numbers
If Mid(Txt, X, 1) Like "*[!A-Za-z ]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters and spaces
Next
TextOnly = Replace(Txt, Chr(1), "")
End Function
Function NumOnly(ByVal Txt As String) As String
Dim X As Long 'Code base by Rick Rothstein (MVP - Excel)
For X = 1 To Len(Txt)
If Mid(Txt, X, 1) Like "*[!0-9]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only numbers
'If Mid(Txt, X, 1) Like "*[!A-Za-z ]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters and spaces
Next
NumOnly = Replace(Txt, Chr(1), "")
End Function
Used in a cell as:
=NumOnly(A1)
=TextOnly(A1)
Bookmarks