|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 10th, 2010, 11:17 AM
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
April 10th, 2010, 11:39 AM
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
April 10th, 2010, 03:12 PM
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
April 10th, 2010, 03:45 PM
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
April 11th, 2010, 03:52 AM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
