How to separate numbers and alphabate from a cell
 

Hi,

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

Thanks & regards

How to separate numbers and alphabate from a cell
 

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

How to separate numbers and alphabate from a cell
 

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


"Kevin B" wrote:

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

How to separate numbers and alphabate from a cell
 

On Mon, 2 Jun 2008 06:24:02 -0700, jai wrote:

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