Adding a numerical value to a cell item

[Jeremy]

In Excel 2004, MacOS10.3.6, Working on an order worksheet for motion
picture lamps. I also want to keep track of the electrical current used by
each type of lamp and have that info part of the calculation for the total
order. I would like to have that lamp characteristic (amperage) part of the
lamp item cell, rather than a separate column item. Can't seem to find a way
to include a number value within the lamp description cell and have that
part of a calculation. Thanks in advance-Jeremy

 

[Fredrik Wahlgren]

In Excel 2004, MacOS10.3.6, Working on an order worksheet for motion
picture lamps. I also want to keep track of the electrical current used by
each type of lamp and have that info part of the calculation for the total
order. I would like to have that lamp characteristic (amperage) part of the
lamp item cell, rather than a separate column item. Can't seem to find a way
to include a number value within the lamp description cell and have that
part of a calculation. Thanks in advance-Jeremy

I don't quite understand what you want to do. Can you provide an example?

/ Fredrik

 

[Jeremy]

Example: Column A-Lamp X (8.6Amps) Column B- 10 lamps Column C- 86Amps.
Column A- Lamp Y (100Amps) Column B- 4 Lamps Column C- 400 A.
I wanted to avoid a separate amperage rating column for each type of lamp to
make creating new sheets easier. I know I can have a separate amperage
column as part of the info calculated, but I thought there might be a way to
have the specific amp rating part of the item column itself. If this isn't
clear, I can send an email with a simple chart illustrating what I want to
accomplish. Thanks- Jeremy

 

[Fredrik Wahlgren]

Example: Column A-Lamp X (8.6Amps) Column B- 10 lamps Column C- 86Amps.
Column A- Lamp Y (100Amps) Column B- 4 Lamps Column C- 400 A.
I wanted to avoid a separate amperage rating column for each type of lamp to
make creating new sheets easier. I know I can have a separate amperage
column as part of the info calculated, but I thought there might be a way to
have the specific amp rating part of the item column itself. If this isn't
clear, I can send an email with a simple chart illustrating what I want to
accomplish. Thanks- Jeremy

Ahhh, I think I understand now. In column A you enter "A-Lamp X (8.6Amps)"
In columnn B, you enter the number of lamps. In column C you don't want to
enter anything other than a formula. The formula should suck out
1) # of lamps from column B
2) Amperage from column A

and then multiply these values. Is this correct?. So the question is "How do
I get the numeric value 8.6 from the string "A-Lamp X (8.6Amps)" . Right?

I assume X is a manufacturer's code, it could very well be something like
H70T36S, right? We don't want those numeric values

It seems as if this problem calls for a user defined function.
Unfortunately, you need to scan the text backwards.

Scan the text backwards. Stop when you reach a numerical value. You now have
the last position of the text which you must save in a variable. Keep on
looping until you find a token that is neither numerical nor a "." You now
have the position where the amperage begins. You can now use "mid" to get
8.6

In column C, enter =GetAmpFunction(A1) * B1

I recommend you put the word "Lamps" in column D. That will make things much
easier.

Slightly complicated if you haven't written any custom VBA functions before.
Anyway, I hope you get the idea.

Regards,
Fredrik

 

[Peo Sjoblom]

If the amperage is always enclosed by a parenthesis and is followed by
"Amps" and B always is the number and the string "Lamps" you can use this
in C

=MID(A1,FIND("(",A1)+1,SEARCH("amps",A1)-(FIND("(",A1)+1))*SUBSTITUTE(LOWER(B1),"lamps","")

and if you instead of 10 Lamps or 4 lamps have a header with Lamps and only
the quantity in B you could use this in C2

=MID(A2,FIND("(",A2)+1,SEARCH("amps",A2)-(FIND("(",A2)+1))*B2

copy down as long as needed, just in case you want to fill down the formula
before entering anything in B you could dodge the error by using

=IF(B1="","",MID(A1,FIND("(",A1)+1,SEARCH("amps",A1)-(FIND("(",A1)+1))*SUBSTITUTE(LOWER(B1),"lamps",""))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Fredrik Wahlgren]

Nice...
/ Fredrik

If the amperage is always enclosed by a parenthesis and is followed by
"Amps" and B always is the number and the string "Lamps" you can use this
in C

=MID(A1,FIND("(",A1)+1,SEARCH("amps",A1)-(FIND("(",A1)+1))*SUBSTITUTE(LOWER(
B1),"lamps","")

and if you instead of 10 Lamps or 4 lamps have a header with Lamps and only
the quantity in B you could use this in C2

=MID(A2,FIND("(",A2)+1,SEARCH("amps",A2)-(FIND("(",A2)+1))*B2

copy down as long as needed, just in case you want to fill down the formula
before entering anything in B you could dodge the error by using

=IF(B1="","",MID(A1,FIND("(",A1)+1,SEARCH("amps",A1)-(FIND("(",A1)+1))*SUBST
ITUTE(LOWER(B1),"lamps",""))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Bob Greenblatt]

Nice...
/ Fredrik

Or, an easier way, if the number in column A is always amps, just make a
custom format such as 0.0" Amps";

Then you simply enter the value in column A, and it can be used in a
calculation, but displayed as you like it.