how to get reply 50 from (20 x 2.5 KGS)

**tariqnaz2005** · 06-02-2011, 10:47 AM

hi every one...

I m importing data from one other sheet which is like that "20 x 2.5 kgs"

I want a formula which give me reply "50" in cell B1...

thanks for any help in advance

Cell A1 = 20 x 2.5 kgs

I want in Cell B1 = 50

**shg** · 06-02-2011, 11:07 AM

How about doing text to columns with space as the delimiter, and then multiplying the resulting numeric cells?

**tariqnaz2005** · 06-05-2011, 02:22 AM

Thanks shg...but is there any formula for that??? cuz some time its like that (no space bewteen 2.5 & Kgs (2.5kgs)...so any suggestion pls?

Cell A1 = 20 x 2.5kgs
Cell A1 = 20 x 2.5 kgs

**nawas** · 06-05-2011, 08:29 AM

Try this;

=LEFT(A1,FIND(" ",A1,1)-1)*(MID(A1,6,3))

Thanks

**Marcol** · 06-05-2011, 09:00 AM

This is a bit of a mouthfull, but it should cover most variations likely to be incurred

Please Login or Register  to view this content.

e.g.
20 x 2.5kgs
20 x 2.5 kgs
20x2.5kgs
20x 2.5 Kgs
20 x 2.5 kgs
21 x 2.5 kgs
220 x 2.5 KGS
200.0x 2.5 kgs

Hope this helps

**martindwilson** · 06-05-2011, 09:42 AM

or
=LEFT(A1,SEARCH("x",A1)-1)*SUBSTITUTE(UPPER(MID(A1,SEARCH("x",A1)+1,20)),"KGS","")

**Marcol** · 06-05-2011, 10:07 AM

I'll give you that one Martin!

Must spend a little time more thinking, rather than just charging at things!

**tariqnaz2005** · 06-06-2011, 05:02 AM

I m sorry, i was on weekend..

Thanks every one who helped me and spared your costly time for me..

..I will try these all formulas and see which one is more suitable for me..hope all will be fine..

God Bless you all of you and your families..