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!

Roland,

One solution to this would be
- keep the numbers on their own in one column
- use a vlookup to give the description (VL etc.)
- concatenate these two cells to give the combined version
Then, if you just want the number, refer only to the cell with the
number in it.
e.g. any statistical analyis would just involve referrring to the
numbers (or the description if that's your preference).

If you don't want to see the two individual columns, hide them.

It may mean adding a column or two to your spreadsheet, but otherwise
it should provide a fairly simple solution to your problem.

It really depends on how the cell values you describe (e.g. 23.5 (VL))
were generated in the first place. Regardless, it should be a fairly
simple operation.

If you need clarification, let me know. As an aside, I'm quite
familiar with the area you're working in.

Let me know if I can help further,

Eddie


--
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112
View this thread: http://www.excelforum.com/showthread...hreadid=521322

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