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?
Menu
Excel formula
Replies (7)
First select the whole cell ;then go to data & click on text to column option.
First select the whole cell ;then go to data & click on text to column option.
| 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 .........
Recent Topics :
- Itr 4 problem
- Capital Gains Mismatch Due to Sale Agreement in FY
- REGARDING NRO INVESTMENT IN SHARES OF DOMESTIC COM
- DTAA benefits
- SUPPLEMENTARY AGREEMENT FOR ADDING A PARTER IN TN
- Part availmement of ITC in case of rejection of su
- Due date for itr 5 no audit with loss for fy 25 26
- Capital Gains Mismatch AIS
- Inward E-way bill from Tally Prime 7.1
- Query regarding exemption under Section 10(10AA) &
Related Topics :
CAclubindia
