Prev: Number format changes to date by itself
Next: Protection
From: Tonso on 19 Apr 2010 16:52
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
From: T. Valko on 19 Apr 2010 18:10
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" <wthomasss(a)hotmail.com> wrote in message
news:448459c6-6125-49bd-9135-15193fda58fe(a)n33g2000pri.googlegroups.com...
>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


From: T. Valko on 19 Apr 2010 18:16
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" <biffinpitt(a)comcast.net> wrote in message
news:ewesk0A4KHA.4332(a)TK2MSFTNGP02.phx.gbl...
> 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" <wthomasss(a)hotmail.com> wrote in message
> news:448459c6-6125-49bd-9135-15193fda58fe(a)n33g2000pri.googlegroups.com...
>>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
>
>


 | 
Pages: 1
Prev: Number format changes to date by itself
Next: Protection