I need to parse a number from a string 1

[DISI]

Is there a string function to locate a number in an alpha-numeric string. It is typical that I get "dirty" pharamcy data: TBL_RX, field Drug_Name
Prozac 20mg, 30 $27.90
IMIPRAMINE HCL 100 mg 60 $5.75
XALATAN SOL 0.025mg/ml 30 $42.95

*Notice that there is no consistent pattern of spaces or commas in the data

I would like to create the following fields:

Drug Dose Unit Quantity Cost
Prozac 20 mg 30 $27.90
IMIPRAMINE HCL 100 mg 60 $5.75
Xalatan Sol 0.025 mg/ml 30 $42.95

Ideally I want a function to get the position of a numeral or a specific character ie dollar sign.

Thanks for the help

Paul Faculjak
paul@DataIntegritySolutions.com

 

[llkhoutx]

Try instr(), left(), right() - see Help menu for these functions.

 

[DISI]

LLKHOUTX, can't say your suggestion was much help. Unless '97 is different from 2000, all InStr gets me in help is a single reference page "Differences in String Functions Operations"

Anyone else out there with some help?

Thanks Paul Faculjak
paul@DataIntegritySolutions.com

 

[MichaelRed]

Split in (In Ms. Access 2K) will return the individual "words" in the string. e.g.

MyVar = Split("Prozac 20mg, 30 $27.90", " &quot

yields:

MyVar(0) = "Prozac"
MyVar(1) = "20mg,"
MyVar(3) = "30"
MyVar(3) = "$27.90"


Next, use the InStr Function to seperate the "problem" (20mg) into the numeric (Dose) and text (units) parts

For Idx = 1 to Len(MyVar(1))
MyChr = Mid(MyVar(1), Idx, 1)
If (MyChr >= &quot;0&quot; and MyChr <=&quot;9&quot Then
MyVarNum = MyVarNum & MyChr
Else
MyVarChr = MyVarChr & MyChr
Else
Next Idx

Admittedly, this is just a couple of CRUDE snippets, and there is an hour or two of work to set this up propperly and account for the variations but the concepts are to just parse the strings (in a variety of ways) to get te elements you are searching for.

Since I am currently in the 'employment' market, I cannot take time to do the whole soloution - even if I had a more-or-less complete set of trese to grapple w/. but this should be sufficient for you to at least start fraying the nerves.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[DISI]

Michael,

You've thoroughly enlighted me with your response and given me enough to go forth and learn by trying. I didn't know about the &quot;Split&quot; function.

Paul Paul Faculjak
paul@DataIntegritySolutions.com