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?
7 Replies
khushal
(ACA; CS Professional Gr. II & IV Cleared; Pursuing ACCA UK & Bcom Graduate)
(63 Points)
Replied 21 February 2012
First select the whole cell ;then go to data & click on text to column option.
khushal
(ACA; CS Professional Gr. II & IV Cleared; Pursuing ACCA UK & Bcom Graduate)
(63 Points)
Replied 21 February 2012
First select the whole cell ;then go to data & click on text to column option.
SHIVANI MAHESHWARI
(CA)
(548 Points)
Replied 22 February 2012
it can't be done with Text to column option as space in letters is also given.
pls tell me any other way.
Kamaraj Mani. N
(Chartered Accountant - Product Manager)
(553 Points)
Replied 23 February 2012
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.
Recent Topics
- Share office desk in bkc, near family court
- 80D claim for payment paid by parent
- TDS on purchase of Flat under subvention scheme be
- Nznxnc call me at the end of the day of the
- Nsje snswk fjw ed le ene djff ne enje ejdd rejje
- Hxjcif the day I have been in touch with me and
- Iska djd djrbdsjebshe sir ur rei ejr reinr the jr
- GST registration for medical shop in Karnataka
- Hsshs me know if you are free number of the
- Chu Kang and the situation of the situation and I
Related Threads
CAclubindia
