Excel formula

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?

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

Your Data Structure
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 .........

with the use of CONCATENATE formula this can be done very easily

 

Regards

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
ARTICLESHIP 25 October 2026
Article Intern (CA Articleship)

Arpan Bohra & Co

Hyderabad

CA Inter

View Details
Company
ARTICLESHIP 14 November 2026
Article Assistant

Jitendra Agarwal & Associates

Bengaluru

CA Inter

View Details
Company
Featured 07 November 2026
Chartered Accountant

DPV & Associates

Chennai

CA

View Details
Company
ARTICLESHIP 07 November 2026
Article Assisstant

DPV & Associates

Chennai

CA Inter

View Details
Company
16 November 2026
Indirect Tax Associate

Sekhri Kanodia & Associates

Mumbai

B.Com

View Details
Company
Featured 17 November 2026
Chartered Accountant

M/s. A V C & Co.

New Delhi

CA

View Details
Company
20 November 2026
Senior Accountant

Compass Tex India Pvt Ltd

Coimbatore

CA Inter

View Details
Company
ARTICLESHIP 16 November 2026
Audit Executives

Loonker Godawat And Company

Bengaluru

B.Com

View Details