Pull 2 Numbers From Cell Then Multiply Them

**icschenholm** · 07-18-2012, 03:28 PM

I am new to excel and am trying to make a scripted invoice for some work I am doing. The cell I need to pull 2 numbers from is organized as:
"Appointments ($8 x 18)"
Is there a way to pull the 2 numbers from that cell and multiply them, showing the result in an adjacent cell? Thank you!

**Cutter** · 07-18-2012, 03:43 PM

You have only given one example so, based on that one:

=LEFT(MID(A2,16,20),2)*SUBSTITUTE(RIGHT(A2,3),")","")

**icschenholm** · 07-18-2012, 03:46 PM

Brilliant work Cutter, thank you! Works perfectly, I just changed the A2 to A11 for my purposes, and it works great, I really appreciate it. Is there any way you could explain how it works though? The old programmer in me is curious and can't figure it out just by reading it. Thanks again!

**Cutter** · 07-18-2012, 03:51 PM

Actually, this is simpler

=MID(A11,16,2)*SUBSTITUTE(RIGHT(A11,3),")","")

The MID() function is stripping off the "Appointments ($" portion so it assumes every cell has that.
So it takes 2 characters starting with #16 - in the example that's 8space

The SUBSTITUTE() is getting rid of the closing bracket on the result of the RIGHT()
The RIGHT() is taking the last 3 characters of the original value, in this case 18)

Although the 2 portions are text results, they are converted to numeric by the multiplication.