How to separate numbers and alphabate from a cell

[jai]

Hi,

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

Thanks & regards

 

[Kevin B]

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

 

[jai]

Dear Kevin,

A lots of thanks to you for providing me the wonderful answer.

I wanna know is it possible through formula or function.

Thanks & regards

Jai

 

[Ron Rosenfeld]

Hi,

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

Thanks & regards

If the digits are all together, as you show in your examples, then:

A1: your string
B1: (number)
=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

C1: (letters)
=SUBSTITUTE(A1,B1,"")

--ron