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 Threads
- Can I claim benefit under 54F if I sell my equity
- Income tax refund 25/26
- Who is responcible to pay GST Liability (If Servi
- Excess Payment on RCM
- AOC4 auto population of previous year data
- Exports against INR PO payable by Domestic Company
- Which websites are best for government exam prepar
- TDS on maturity payment of LIC of india
- ITR2 Gold ETF short and long term
- Query under faceless assessment
Related Threads
CAclubindia
