Multiply numbers with letters in different cells

I won't solve it like that, but since you ask for it, see the green cells in the attached file.

Notice I changed the format of the merged cells.

Try this

=(LEFT(A3,FIND("mtr.",A3)-1)*SUBSTITUTE(REPLACE(A3,1,SEARCH("x",A3)+1,""),"mtr.",""))*RIGHT(B$3,LEN(B$3)-2)

Row\Col	A	B	C
3	1.06 mtr. X 50 mtr.	R 12.50	R 662.50
4	1.37 mtr. x 50 mtr.		R 856.25
5	1.60 mtr. x 50 mtr.		R 1,000.00
6	2.20 mtr. x 50 mtr.		R 1,375.00
7	2.50 mtr. x 50 mtr.		R 1,562.50
8	3.20 mtr. x 50 mtr.		R 2,000.00
9	5.00 mtr. X 50 mtr.		R 3,125.00
10	5.00 mtr. X 60 mtr.		R 3,750.00

Originally Posted by pallove

Awesome!!!

Thank you guys. You've been of great help.

@ AlKey. Can you please explain your formula (hope I'm not asking for too much). I really need to understand this so that I can easily apply it with sense.

Regards,

Pallav

I use this formula to first numeric value =LEFT(A3,FIND("mtr.",A3)-1 and multiply it y second numeric value with SUBSTITUTE(REPLACE(A3,1,SEARCH("x",A3)+1,""),"mtr.",""))

then multiply by B3 with this formula RIGHT(B$3,LEN(B$3)-2)



Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).