I have to separate text & amount (both text & amount is in same cell ) in different cells in excel file.
for Ex. Fixed Asset 2,30,700 5,30,210
How can i do this?
I have to separate text & amount (both text & amount is in same cell ) in different cells in excel file.
for Ex. Fixed Asset 2,30,700 5,30,210
How can i do this?
| Originally posted by : khushal | ||
 |
First select the whole cell ;then go to data & click on text to column option. |
 |
it can't be done with Text to column option as space in letters is also given.
pls tell me any other way.
You can use combined formula to break these items. i assume that you have the data in this format
| Befor Split | After Split | |
|---|---|---|
| Cell value (A1) | Text (B1) | Amount (C1) |
| Fixed Assets 50,00,000 | Fixed Assests | 50,00,000 |
| Current Assets 25,00,000 | Current Assets | 25,00,000 |
Assume that you have value in A column.
Step 1:
Go to B1 and enter this formula
=LEFT(A1, LEN(A1)-LEN(RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))))
Step 2:
Go to C1 and enter this formula
=RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Note: There should not be any space in the amount.
Thanks .........
Rahul Dang & Associates-Chartered Accountants
Pune
CA Inter
View Details
Vardhaman Group of India
Pimpri Chinchwad
CA Inter
View Details
T R SOOD & CO
New Delhi
CA Inter
View Details
CAclubindia
