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 Topic
- Gift recd from brother
- How to show Loan write off in DPT 3?
- Form 10E and Salary arrear
- LTCG U/S 54F. Sale of property plus sale of share
- Issue with ITR2 online utility for HUF
- TDS deducted by EPFO under wrong section
- Schedule SI in ITR-2
- What is the easy way to do CA- through foundation
- Schedule FA query for this AY 2026-27
- Business income source for husband and wife
Related Topics
CAclubindia
