How to do math on cells with letters

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Z

ZenMasta

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?
 
One thought, remove the letter using SUBSTITUTE
then do/force a calculation on the resulting text number
=SUBSTITUTE(B12,"g","")/D3
 
ZenMasta said:
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?
Click to expand...

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 -----
 
Max, I tried your suggestion and it gave me an Err:508


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
 
Err:508? Where are you putting the formula... in a worksheet cell or VB
code? If a worksheet cell, where are you seeing this error number at?
 
ZenMasta said:
I'm just the recipient of this data I didn't design it
Click to expand...

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?
Click to expand...

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 ------

Max, I tried your suggestion and it gave me an Err:508

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
Click to expand...


----- previous message -----
 
Hello,

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
 
I'm not sure what to tell you... I have never seen Err:508 appear in a cell
(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.
 
ZenMasta said:
I'm just the recipient of this data I didn't design it
Click to expand...

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?
Click to expand...

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 said:
Max, I tried your suggestion and it gave me an Err:508


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
Click to expand...


----- previous message -----
 
Rick, your original solution worked. I was using calc and google docs. But I
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.
 
Thanks for the suggestion. I've used WA before but I already have an excel
sheet that lists the nutrion facts for certain items and there are
several... too many to want to type manually into their engine.
 
Hey Max I just double checked... your first reply had commas instead of semi
colons by mistake so yeah works for sure with semi colons.
 
There was no mistake. What I plugged into the working sample is exactly the
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.
 
Back
Top