Please help with extracting a number out of text

[Guest]

I am trying to do a search for all numbers that are higher than 160. This is
the easy part. The hard part is that the field I am trying to do the search
in, is a memo field. I need to find the word “Sodium†and see what is the
value of Sodium to eliminate the records where the Sodium value is 160 or
less. I used the (instr and Mid) functions and was able to get the line that
contained the Name (Sodium) and the value extracted. Now I have a column in a
query that I made contains about 50 to 75 characters (text and numbers). I
need to extract the Sodium values out of this column. Any idea
Thanks
Al

 

[Guest]

Al,

Have to provide some more information about the format of the line. Is it
something like a nutrition chart on food? E.g., part of the line reads '...
Sodium 160 mg ...'?

Once you extract the number that goes with 'Sodium', you can use the Val()
function to convert the number string to a numeric that can be used for
numeric comparisons.

Good Luck!

 

[Guest]

Val() does not work well if the number is not at the start of the field. here
are some examples:
"sodium level decreased to 141 mEq/L. The white bl"
"sodium level decreased to 144 mEq/L."
"sodium level of 147 mEq/L and white blood cell inc"
"sodium concentration was 142 mEq/L and potassium c"
you can see that there is no consistency.
thanks
Al

 

[Guest]

Al,

Are the units always mEq/L? You might be able to step back from the units
string with something like: mid (<sodium containing substring>, InStr(1,
<sodium substring>, " mEq/L") - 4), InStr(1, <sodium substring>, " mEq/L") ).
Make the "<sodium containing substring>" a query so that this is more
expressible.

Of course, this doesn't take care of a value larger than 999. But if the
subject has that kind of sodium concentration, this is probably the least of
their problems.

Good Luck!

 

[John Vinson]

Val() does not work well if the number is not at the start of the field. here
are some examples:
"sodium level decreased to 141 mEq/L. The white bl"
"sodium level decreased to 144 mEq/L."
"sodium level of 147 mEq/L and white blood cell inc"
"sodium concentration was 142 mEq/L and potassium c"
you can see that there is no consistency.
thanks
Al

Garbage in, garbage out... :-{(

What about the line

"Potassium, chloride, and sodium levels were 85, 160, and 142 mEq/L
respectively"

There's no way Access could parse this English language construct
("respectively") to correctly pair 142 with Sodium.

The best you're likely to be able to accomplish is to use some VBA
code to find the first numeric substring in the string containing the
word "sodium". This may of course be the wrong numeric value. Is the
result of enough importance that you want it right? Or does it not
matter if it's wrong one time in ten?


John W. Vinson[MVP]