need a formula to find "*" in s string and multiply by preceding andfollowing values

[Tonso]

I have cells enrties which can look like this:

Cell Entries: >>
Desired Results
Cars
6 6
Trucks
11 11
Bikes
4*3 12
Mowers 31*100
3100

They might have an * in them, or not. If there is not an asteric, i
want to, in an adjacent cell, to have the value 6, as shown under
"Desired results" for Cars, or, 3100 (31*100), as shown under Desired
results for Mowers. If there is no asteric, the number can be from 1
to 3 digits long. If there is an asteric, the number to the left of it
can be from 1 to 3 digits long, and the number to the right can be
from 1-5 digits long. There will always be 10 spaces from the word to
the number, so that for example, the Len of "Cars 6" is 15 (4
+ 10 + 1), and the length of "Bikes 4*3" is 18 (5 + 10 + 3). I
have been unscucessful in creating a formula that will give me the
value, or, if "*", is present, perform the multiplication.

Thanks,

Tonso

 

[T. Valko]

Try this...

=IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),SUBSTITUTE(MID(A1,FIND("*",A1)-4,5),"*","")*MID(A1,FIND("*",A1)+1,5))

 

[T. Valko]

Improvement...

We can eliminate the SUBSTITUTE function.

=IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),MID(A1,FIND("*",A1)-4,4)*MID(A1,FIND("*",A1)+1,5))

 

[Tonso]

Improvement...

We can eliminate the SUBSTITUTE function.

=IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),MID(A1,FIND("*",A1)-4,4)*MID(A1,FIND(­"*",A1)+1,5))

--
Biff
Microsoft Excel MVP







- Show quoted text -

Biff,

Thank you very much for your expert help. That did the trick!!


Tonso

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Improvement...

We can eliminate the SUBSTITUTE function.

=IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),MID(A1,FIND("*",A1)-4,4)*MID(A1,FIND(­"*",A1)+1,5))

--
Biff
Microsoft Excel MVP







- Show quoted text -

Biff,

Thank you very much for your expert help. That did the trick!!


Tonso

 

[Roger Govier]

Hi Tonso

You could also create a named Formula
Insert>name>define
Name Result
Refers to =EVALUATE(RIGHT(A1,9))

Now in any cell to the right of your string of data, enter =Result
and copy down