| Home |
| Search |
| Today's Posts |
|
#4
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 |
| 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 | |||
Threaded Mode
