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

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

Try this...

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

--
Biff
Microsoft Excel MVP


"Tonso" wrote in message
...
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

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


"T. Valko" wrote in message
...
Try this...

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

--
Biff
Microsoft Excel MVP


"Tonso" wrote in message
...
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

On Apr 19, 6:16*pm, "T. Valko" wrote:
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

"T. Valko" wrote in message

...



Try this...

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

--
Biff
Microsoft Excel MVP

"Tonso" wrote in message
....
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- Hide quoted text -

- Show quoted text -

Biff,

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


Tonso

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tonso" wrote in message
...
On Apr 19, 6:16 pm, "T. Valko" wrote:
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

"T. Valko" wrote in message

...



Try this...

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

--
Biff
Microsoft Excel MVP

"Tonso" wrote in message
...
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- Hide quoted text -

- Show quoted text -

Biff,

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


Tonso

Hi Tonso

You could also create a named Formula
Insertnamedefine
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

--
Regards
Roger Govier

Tonso wrote:
On Apr 19, 6:16 pm, "T. Valko" wrote:
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

"T. Valko" wrote in message

...



Try this...
=IF(ISERR(FIND("*",A1)),--RIGHT(A1,3),SUBSTITUTE(MID(A1,FIND("*",A1)-4,5),"**","")*MID(A1,FIND("*",A1)+1,5))
--
Biff
Microsoft Excel MVP
"Tonso" wrote in message
...
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- Hide quoted text -
- Show quoted text -

Biff,

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


Tonso