Seperate text and number to different cells
 

Hi,

I have some data in cell (A1) which is alpha numeric, from this data I want
number and text in seperate cells (B1) and (C1) respectively per the below
example.

A B C
Raw Data Numbers Text
asho344555k123 344555123 ashok
123ab47 12347 ab
1affu123 1123 affu


Thanks
Afroz

Seperate text and number to different cells
 

The following User Defined Function will return the numerals in a mixed cell:

Public Function ReturnNumerals(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
ReturnNumerals = sStr1
End Function

This UDF will return the non-numerals in a mexed cell:

Public Function ReturnAlphas(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If Not sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
ReturnAlphas = sStr1
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu201003


"Seperate text and number" wrote:

Hi,

I have some data in cell (A1) which is alpha numeric, from this data I want
number and text in seperate cells (B1) and (C1) respectively per the below
example.

A B C
Raw Data Numbers Text
asho344555k123 344555123 ashok
123ab47 12347 ab
1affu123 1123 affu


Thanks
Afroz

Seperate text and number to different cells
 

For Numbers:

B2:
=LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2)))))&LOOKUP(10^10 ,--RIGHT(A2,ROW($1:$99)))

For Text:

C2:
=LEFT(SUBSTITUTE(A2,LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2))))),"")&LOOKUP(10^ 10,--RIGHT(A2,ROW($1:$99))),LEN(A2)-LEN(B2))

copy B2 and C2 down as far as needed



"Seperate text and number" wrote:

Hi,

I have some data in cell (A1) which is alpha numeric, from this data I want
number and text in seperate cells (B1) and (C1) respectively per the below
example.

A B C
Raw Data Numbers Text
asho344555k123 344555123 ashok
123ab47 12347 ab
1affu123 1123 affu


Thanks
Afroz

refers to below links

http://www.ozgrid.com/VBA/ExtractNum.htm

all the best