Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
http://www.ozgrid.com/VBA/ExtractNum.htm all the best
__________________
Thanks Bala |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of cells between 2 seperate cells | Excel Discussion (Misc queries) | |||
How can I seperate numbers from text (in one cell) into 2 cells? | Excel Discussion (Misc queries) | |||
How can I seperate text and number from alphanumeric cell? | Excel Worksheet Functions | |||
How to seperate a text group in once cell into many cells | Excel Discussion (Misc queries) | |||
Need to convert text string to seperate cells | Excel Worksheet Functions |