I use 2 custom functions to parse out number/text from a string.

Press Alt + F11 to open the VBA module, click INSERT/NEW/MODULE and paste
the following 2 functions into the empty module.

Each function starts with 'Function and ends with 'End Function'

Function ExtractNumbers(varVal As Variant) As Long


Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String
Dim intChar As Integer

intLen = Len(varVal)
Application.Volatile

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
intChar = Asc(strChar)
Select Case intChar
Case 1 To 47
Case 48 To 57
strVal = strVal & strChar
Case 58 To 255
Case Else
End Select
Next i

If Len(strVal) = 0 Then
ExtractNumbers = 0
Else
ExtractNumbers = CLng(strVal)
End If

End Function

Function ExtractAlpha(varVal As Variant) As String

Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String

intLen = Len(varVal)

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _
Asc(strChar) = 97 And Asc(strChar) <= 122 Then
strVal = strVal & strChar
End If
Next i

ExtractAlpha = strVal

End Function

Hope this helps...
--
Kevin Backmann


"Tazeem" wrote:

Is there any shortcut to separate (text and number - like ABCDE123456) of a
single cell?. Especially if the no. of digits are not constant. Thanks