The following 2 custom function parse out numbers or alpha characters from a
string. Press Alt + F11 to open the VBE, click INSERT in the VBE menu and
select MODULE.

You can copy and paste from here if the line breaks end in an underscore.

Function ExtractNumbers(varVal As Variant) As Long


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 IsNumeric(strChar) Then strVal = strVal & strChar
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

--
Kevin Backmann


"jai" wrote:

Hi,

Is it possible to separate alphabate and number from a code no for
example-adf1234 or 123456abaa.

Thanks & regards