Extracting numbers from a text field

[chris100]

Below is an example of a field that I need to separate. It contains the name of a drug and then the dosage. Unfortunately it is not always separated by a space or comma but it does always start with a number. Also, the name of the drug and dosage varies in length. I need to pull out the dosage part and put into another field, ie, name of drug: Zofran - dosage: 40MG

Examples of what is in existing field:
Zofran 40MG, or
Zofran40MG

Thanks in advance for your help.
Chris100

 

[KenReay]

Hi

Is it possible that drug name contains a number?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[chris100]

Ken,

The drug name does not contain any numbers. Just text.

Thanks,
Chris100

 

[KenReay]

Hi

Ok so you need to right a function which will work its way along the text until it finds the first number, then note the charcaters until either the end of the string, a space or a non numeric character is encountered

Functions you would need would typically be instr(), left(), mid() do you need more info than that?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[chris100]

Thanks Ken,

Yes, that's what I need. A function that will follow the text until it finds a number.

I'll try these and let you know.

Thanks,
Chris100

 

[KenReay]

Hi

needs error trapping but something like

Public Function x(strIn As String) As String
Dim i As Integer
Dim j As Integer
For i = 1 To Len(strIn)
If IsNumeric(Mid(strIn, i, 1)) Then
For j = i To Len(strIn)
If Not IsNumeric(Mid(strIn, j, 1)) Then
Exit For
End If
Next j
Exit For
End If
Next i
x = Mid(strIn, i, (j - i))
End Function

? x("Viocodin 40mg")
40

is what you need

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[chris100]

Thanks Ken,

I'll try this and let you know.

Chris100

 

[scriverb]

Copy and paste this function into a Database module:

Public Function FindNumericChar(ParseField As String)
Dim vNumericChar As Boolean, i As Integer
For i = 1 To Len(ParseField)
    vNumericChar = IIf(Asc(Mid$(ParseField, i, 1)) >= 48 And Asc(Mid$(ParseField, i, 1)) <= 59, i, 0)
    If vNumericChar <> 0 Then
        FindNumericChar = i
        Exit Function
    End If
Next i
FindNumericChar = 0
End Function

Here is some sample SQL that parses the data in a query:

SELECT D.DD, Trim(IIf(FindNumericChar(D![DD])>0,Mid$(D![DD],1,FindNumericChar(D![DD])-1),D![DD])) AS Drug, Trim(IIf(FindNumericChar(D![DD])>0,Mid$(D![DD],FindNumericChar(D![DD])))) AS Dosage FROM [RED]YourTableName[/red] AS D;

Update the above SQL with your table name and field names. DD is your field for the combined "Drugs And Dosage".

After you check this out you can change this Select query to an Update query and actually split the field into two new fields. If you need help with that please post back.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]