How to do math on cells with letters
ZenMasta
I'm trying to figure out portion info for food nutrition.
I have a sheet that has nutrition facts etc. However the columns will often
have letters like g, mg, mcg (grams miligrams, micrograms etc)
b12=33g d3=3
Well, =b12/d3 = 0 because the letter is throwing it off.
Is there a way I can ignore letters in a field so I can still perform math
on those fields?
Joe User
> b12=33g d3=3
> Well, =b12/d3 = 0 because the letter is
> throwing it off. Is there a way I can ignore letters in a
> field so I can still perform math on those fields?
Yes. But it would be better (less problematic) if you do not put the text
in the cell in the first place.
Two alternatives:
1. Simply put the text in an adjacent cell.
2. Custom format the cell with the number, e.g. 0 "g".
----- original message -----
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:ObyPvD1h...@TK2MSFTNGP02.phx.gbl...
Max
then do/force a calculation on the resulting text number
=SUBSTITUTE(B12,"g","")/D3
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:ObyPvD1h...@TK2MSFTNGP02.phx.gbl...
Rick Rothstein
=LOOKUP(9.9E+307,--LEFT(B12,ROW($1:$99)))/D3
--
Rick (MVP - Excel)
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:ObyPvD1h...@TK2MSFTNGP02.phx.gbl...
ZenMasta
Joe,
I'm just the recipient of this data I didn't design it, yeah I agree it'd
have been better if they could have separated the unit of measurement from
the value.
In regards to formatting the cell... the cells already have these values in
them so if I create a user defined format code like you suggest it wouldn't
make any difference.
Rick, I tried your solution also and got Err:508
Rick Rothstein
code? If a worksheet cell, where are you seeing this error number at?
--
Rick (MVP - Excel)
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:eTCOdn3h...@TK2MSFTNGP05.phx.gbl...
ZenMasta
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:#Td0E83h...@TK2MSFTNGP05.phx.gbl...
joeu2004
> I'm just the recipient of this data I didn't design it
Okay. Perhaps we could help you better by suggesting a macro to
convert the data. But we would need more information.
Returning to your original question....
You wrote previously:
> the columns will often have letters like g, mg, mcg
[....]
> b12=33g d3=3
>
> Well, =b12/d3 = 0 because the letter is throwing it off.
> Is there a way I can ignore letters in a field so I can
> still perform math on those fields?
Try the following.
=LEFT(B12,MIN(FIND({"g","m"},B12&"gm")-1) / D3
The ``&"gm"`` is needed only if there might be no text to the right of
the number.
Add more letters to the first FIND argument as needed. The following
demonstrates the most flexible:
=LEFT(B12,
MIN(FIND(
{"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},
B12&"abcdefghijklmnopqrstuvwxyz")-1) / D3
----- original message ------
----- previous message -----
Bernd P
I suggest not to use Excel for this but www.wolframalpha.com:
Enter, for example (maybe not reasonable, just to show that you can
use plenty of different measures):
20g butter + 1 slice of bread + 10000mg salmon
You will get nutrition facts you (I bet) will never get with any self-
made Excel application.
Regards,
Bernd
Max
> Max, I tried your suggestion and it gave me an Err:508
Don't know how the above happened,
but here's living proof of the formula working as advertised:
http://cjoint.com/?mCpN5ufqHO
--
Max
Singapore
Rick Rothstein
(unless those characters were deliberately placed in the cell as the result
of a formula or VB code result). Assuming you are using a PC version of
Excel, if you send me a copy of your worksheet (remove the NO.SPAM stuff
from my email address if you do), I'll be happy to take a look at it and see
if I can figure out what is going on.
Just in case you want to continue trying to fix the problem on your own,
this formula...
=LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))
will return the leading number at the beginning of a value in A1 even if
there is text after that number.
--
Rick (MVP - Excel)
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:uWUgPd4h...@TK2MSFTNGP06.phx.gbl...
Joe User
> I'm just the recipient of this data I didn't design it
Okay.
Perhaps we could help you better by suggesting a macro to convert the data.
But we would need more information.
Returning to your original question....
You wrote previously:
> the columns will often have letters like g, mg, mcg
[....]
> b12=33g d3=3
>
> Well, =b12/d3 = 0 because the letter is throwing it off.
> Is there a way I can ignore letters in a field so I can
> still perform math on those fields?
Try the following.
=LEFT(B12,MIN(FIND({"g","m"},B12&"gm")-1) / D3
The ``&"gm"`` is needed only if there might be no text to the right of the
number.
Add more letters to the first FIND argument as needed. The following
demonstrates the most flexible:
=LEFT(B12,
MIN(FIND(
{"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},
B12&"abcdefghijklmnopqrstuvwxyz")-1) / D3
----- original message ------
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:eTCOdn3h...@TK2MSFTNGP05.phx.gbl...
----- previous message -----
Joe User
Joe User
Joe User
Joe User
ZenMasta
just tried it with excel at work and it is fine.
Normally this type of stuff has always worked on other spreadsheet apps.
Just not this one.
Thank again.
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uRV4o38h...@TK2MSFTNGP02.phx.gbl...
ZenMasta
sheet that lists the nutrion facts for certain items and there are
several... too many to want to type manually into their engine.
"Bernd P" <bplu...@gmail.com> wrote in message
news:4b533bbc-73b1-4944...@a6g2000yqm.googlegroups.com...
ZenMasta
colons by mistake so yeah works for sure with semi colons.
"Max" <demec...@yahoo.com> wrote in message
news:OrDyww8h...@TK2MSFTNGP05.phx.gbl...
Max
same expression in my 1st response. It's probably the regional settings
thingy - I use commas as delimiters (same as US, UK) whilst you use
semicolons (Continental Europe?). Fortunately, it seems that Excel knows how
to show it properly depending on the settings used.
--
Max
Singapore
----
"ZenMasta" <m...@nospamkthx.bai> wrote in message
ZenMasta
"Max" <demec...@yahoo.com> wrote in message
news:Oz1oulFi...@TK2MSFTNGP02.phx.gbl...
SanCarlosCyclist
cell? Example:
121c
133bb
hh212
Is the a way to use a single substitute that would use only the number
is each of these cells?
Max
> 121c
> 133bb
Rick's offer: =LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))
will strip it as real numbers
To handle it both ways, eg:
> hh212
> 133bb
This works, taken from a posting by macropod:
"..Here's a more flexible formula to strip out all leading and trailing
non-numerics from a cell:
=MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))+1)
This is an array formula, so you input it with Ctrl-Shift-Enter, after which
it will ben enclosed in a pair of braces (ie '{}') .."
--
Max
Singapore
----
"SanCarlosCyclist" <sancarlo...@gmail.com> wrote in message
news:b2290351-7de0-4f3e...@j14g2000yqm.googlegroups.com...
> Max, is there a way to get this to work if there is any letter in a
> cell? Example:
> 121c
> 133bb
> hh212
>
> Is there a way to use a single substitute that would use only the number
Rick Rothstein
>> hh212
>> 133bb
>
> This works, taken from a posting by macropod:
>
> "..Here's a more flexible formula to strip out all leading and trailing
> non-numerics from a cell:
> =MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),"")),MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))+1)
> This is an array formula, so you input it with Ctrl-Shift-Enter, after
> which it will ben enclosed in a pair of braces (ie '{}') .."
I believe this much shorter formula will return the (first) number in the
text no matter where it is located within the text...
=LOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))),LEN(A1)),ROW($1:$99)))
ebloch
characters in a cell or range.
Eric
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:ObyPvD1h...@TK2MSFTNGP02.phx.gbl...
Max
as text numbers instead, with all leading zeros retained?
Bernd P
Why not just
=--RegExpReplace(A1,"^\D*(\d+).*$","$1")
?
This UDF you can find at
http://sulprobil.com/html/regexp.html
Regards,
Bernd
Rick Rothstein
be slower than formulas and that particular UDF requires you to learn
Regular Expression syntax if you do not already know how to construct them.
I will concede, though, that your method can more easily respond to the
question that Max asked (how do you make the return value a string so
leading zeroes can be preserved?)... simply removing the double minus sign
from the front of your formula will do that... my formula appears not to be
so easy to modify for this functionality.
--
Rick (MVP - Excel)
"Bernd P" <bplu...@gmail.com> wrote in message
news:de5c4581-755b-4c20...@26g2000yqo.googlegroups.com...
Roger Govier
If you have control of the sheet, then you could format the cells as
Format>Cells>Number>Custom>General "g" or General "mg" etc.
Entering 33 in cell B12 would display as 33 g, but all that would be held in
the cell would be 33 so it can be used in further calculation.
In place of general, you could use #.00 for example, if you wanted to
display 33.00 g
--
Regards
Roger Govier
"ZenMasta" <m...@nospamkthx.bai> wrote in message
news:ObyPvD1h...@TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to figure out portion info for food nutrition.
>
> I have a sheet that has nutrition facts etc. However the columns will
> often have letters like g, mg, mcg (grams miligrams, micrograms etc)
>
> b12=33g d3=3
>
> Well, =b12/d3 = 0 because the letter is throwing it off.
> Is there a way I can ignore letters in a field so I can still perform math
> on those fields?
>
> __________ Information from ESET Smart Security, version of virus
> signature database 4736 (20100101) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
__________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________
The message was checked by ESET Smart Security.
Bernd P
> Rick, that's great. How can your formula be modified so that it returns it
> as text numbers instead, with all leading zeros retained?
=RegExpReplace(A1,"^\D*(\d+).*$","$1")
This UDF you can find at
http://sulprobil.com/html/regexp.html
Just press ALT + F11, enter a new module, copy the macro text from
above link into the new module and go back to your worksheet and use
the command.
Regards,
Bernd
Max
consider and take up whichever option s/he finds suitable. Hopefully s/he
would be courteous to post a reply here, given the fortune of receiving so
many useful responses to the query.