On Fri, 10 Mar 2006 22:56:26 -0800, Roland
wrote:

In a spreadsheet I have headings for blood-test values (i.e. RBC, WBC, etc.).
The values below sometimes have text after to indicate the value is (L) -
low, or (VL) - v. low, such as: "23.5 (VL)". Is there a way to use the values
in cells that have text as values, that is to always ignore text and treat
numbers within as numbers. There are many, many other spreadsheets where this
ability w/b very beneficial. Thank you!

Depends on how much control you have over the data entry process.

One method, if there are to be no more than three suffixes, and if the
methodology remains stable between tests (so the normal range does also), would
be to custom format the columns appropriately (Format/Cells/Number Custom
Type:)

e.g. for WBC:

[<3]0.0 L;[<12]0.0;0.0 \H

Would display values <3 with an "L" suffix; and values 12 with an "H" suffix.

If the above restrictions cannot be met, then you need to have formulas that
extract the numeric portion of the value incorporated within your formula.

One way to do that is with something called regular expressions, which have
been implemented in Longre's free morefunc.xll add-in available at
http://xcell05.free.fr and easily distributed with a workbook.

To extract the numeric value from a string, you could use this formula in place
of the cell reference:

=REGEX.MID(A4,"(\d+(\.\d*)?)|\.\d+")

This extracts the number as a string so, depending on your use, you may need to
change it to a numeric value first; and may need to do error testing also.


--ron