Equation based on one cell with word & number

**dagindi** · 02-09-2011, 01:32 PM

Is it possible for Excel to run an equation based on a cell that contains a word and a number. For example:
A1 = $100,000
A2 = Bob 15
A3 = James 8
A4 = Michelle 18

Can excel identify the number and multiply by $100,000??

**NBVC** · 02-09-2011, 01:34 PM

If separated with space as shown, and consistently that way, then

=MID(A2,FIND(" ",A2)+1,100)*A$1

copied down

**dagindi** · 02-09-2011, 05:02 PM

After I posted and looked at what I wrote the concept came to me but not the solution.

Can you explain the "+1,100)" part of the equation?

**NBVC** · 02-09-2011, 05:12 PM

In this formula: =MID(A2,FIND(" ",A2)+1,100)*A$1

First we look for the space in celll A2, by using FIND(" ",A2)... I add 1 to move to first digit (although not really needed in the end, because the space would be "canceled out" in the multiplication with A$1. The result is the position number within A2 where the first digit occurs.

Then the MID() function looks along string A2, starting at the position found by the FIND()+1 function.. and extract upto 100 characters from there... if the number of characters is less than 100 it just grabs all (100 is a safety number really)...

The result is multiplied by A1.