How to do math on cells with letters

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Max, is there a way to get this to work if there is any letter in a
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?
 
For this
121c
133bb
Click to expand...

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

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 '{}') .."
 
To handle it both ways, eg:
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 '{}') .."
Click to expand...

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)))
 
free ASAP Utilities has a function under "Text" to remove all text
characters in a cell or range.

Eric
 
Rick, that's great. How can your formula be modified so that it returns it
as text numbers instead, with all leading zeros retained?
 
Why? Well, from my personal standpoint, I would respond that UDF's tend to
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.
 
Hi

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

__________ Information from ESET Smart Security, version of virus signature database 4736 (20100101) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Rick, that's great. How can your formula be modified so that it returns it
as text numbers instead, with all leading zeros retained?
Click to expand...
Hello Max,

=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
 
Bernd, thanks for responding. Its more for the OP, SanCarlosCyclist to
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.
 
Back
Top