| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separate text and number
dear sir
in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234". please help me. thanks advance. raju |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
separate text and number
Hi Raju
One way.. In B1 =LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789"))-1) In C1 =SUBSTITUTE(A1,B1,) -- Jacob (MVP - Excel) "raju" wrote: dear sir in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234". please help me. thanks advance. raju |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
separate text and number
Give these formulas a try...
B1: =LEFT(A1,LEN(A1)-LEN(C1)) C1: =LOOKUP(9E+307,--RIGHT(A1,ROW($1:$99))) Note: When you put the first formula into B1, it will initially display the entire contents of A1 **until** you put the other formula into C1. -- Rick (MVP - Excel) "raju" wrote in message ... dear sir in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234". please help me. thanks advance. raju |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
separate text and number
In C1: =LOOKUP(10^10,--RIGHT(A1,ROW(1:99)))
In B1: =SUBSTITUTE(A1,C1,) "raju" wrote: dear sir in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234". please help me. thanks advance. raju |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
separate text and number
Try: http://www.ozgrid.com/VBA/ExtractNum.htm to pull the number into C1,
then: =SUBSTITUTE(A1,C1,"") in B1 -- Regards Dave Hawley www.ozgrid.com "raju" wrote in message ... dear sir in Cell A1 is "rajulu1234". i want in cell B1 "rajulu" & cell C1 "1234". please help me. thanks advance. raju |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How I can separate text and number in one column from each others | Excel Discussion (Misc queries) | |||
| separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
| How to split number and text string to separate cells? | Excel Discussion (Misc queries) | |||
| Trying to put a dash- in my formula to separate a number&name | Excel Worksheet Functions | |||
| separate a text cell to many number cells | Excel Worksheet Functions | |||
Hybrid Mode
