parse based on first number

[Dave_epic]

Hi.

A quick query. I have a field which looks like this.

Valproic Acid_Cap E/C 150mgValproic Acid_Cap E/C 300mgValproic Acid_Cap E/C 500mgValproic Acid_Tab 250mgValproic Acid_Tab 500mgConvulex_Cap E/C 150mgConvulex_Cap E/C 300mg


I am simply looking to parse into 2 columns. Although I know how to left parse based on the underscore character _. How can I parse starting with the first number in a row as in the strength of the drug, whatever the number is? ie:

Convulex_Cap E/C 300mg

Cheers

 

[Dave_epic]

actually the column should look like this of course


Valproic Acid_Cap E/C 150mg
Valproic Acid_Cap E/C 300mg
Valproic Acid_Cap E/C 500mg
Valproic Acid_Tab 250mg
Valproic Acid_Tab 500mg
Convulex_Cap E/C 300mg

 

[gemma-the-husky]

clearly you have a normalisation issue, which is hard to fix directly IN Access, especially if there are no spaces etc as delimiters. I am not sure where you are going with all this, but from a general realtional consideration, you ought to consider the following

a) where is the data coming from - since it may be easier to fix it at the source -

b) in general, how will it help to turn this item into a column of entries - ie do ALL the entries have 6 values, or do some have more or less than six values, as you will need (should be looking for) a generic solution to this

c) if a new table strength comes along, or one disappears, changing the order you currently bsee, will this have any impact on anything you have done

d) therefore - do the first second third values etc, for each item correspond to something in particular

--------
taking this a bit further

it looks like Valproic Acid is a drug name, so we should be looking for probably
a) a drug table, showing Valproic Acid

b) a capsule/tablet strength table - The other entries then look like available tablet/capsule strengths, so at first sight these really belong in a separate table LINKED to the main drug table, as this will make processing these items much simpler. however, this does beg the question regarding Convulex - Is this a generic, or a substitiute or something else - why is there only one convulex strength - is there a separate main line for convulex

etc etc


I know this doesn't necessarily answer your question directly - but arbitrarily dividing (parsing) a row of text when there are no clear delimiters indicating where the splits SHOULD occur, is virtually impossible to do automatically

 

[namliam]

If your "delimiter" is the last space... you can use InstrRev to locate that space.
Then use Left and Right functions, and/or Mid to split your information

 

[Dave_epic]

Hi Gemma

Thanks for the reply.

I think we are going a bit too deep in terms of objectives. But in general I want to split the column into two with one column simply containing the drug strength in the format :

150mg
300mg

etc...
I can then match the drug strength against another table with a column which has only drug strengths in this format. So you can see I am primarily concerned with the strength rather than the drug name as I have separate lists of these .
My source for the data is an excel file so it doesn't really make it easier to split.

I saw a piece of code you posted by raskew back in June which parses based on the character "*". So I think I can use this but I just need to change the "*" so it splits the column based on the first numeric character whether it be "0-9"

Public Sub quickparse2(ByVal ptext As String, pDelim As String)
'*******************************************
'Purpose:   Extracts words/expressions from
'           a string based on a user-provided
'           separator
'Coded by:  raskew
'Input:     x = "Aaaaaaaa*aaa bbb nnnn* aaaa bbbb 123 cc*bbbbb"
'           Call quickparse(x, "*")
'Output     Aaaaaaaa
'           aaa bbb nnnn
'           aaaa bbbb 123 cc
'           bbbbb
'*******************************************
 
Dim strHold  As String
Dim texthold As String
Dim textsay  As String
Dim n        As Integer
 
    texthold = Trim(ptext)
    n = Len(ptext)
    Do While InStr(texthold, pDelim) > Len(pDelim)
       textsay = Left(texthold, InStr(texthold, pDelim) - 1)
       'add code to do something
       Debug.Print textsay
       strHold = strHold & textsay & vbCrLf
       texthold = Trim(Mid(texthold, InStr(texthold, pDelim) + Len(pDelim)))
       n = Len(texthold)
    Loop
End SubHTH - Bob

regards

 

[namliam]

If your "delimiter" is the last space... you can use InstrRev to locate that space.
Then use Left and Right functions, and/or Mid to split your information

Feeling a little left out...

Can you use the last space as a delimiter???

 

[DCrake]

I was reading this post and then I suddenly saw the word Excel. This led me to think what does the data look like in Excel? Surly it is in rows?

Send a copy of the Excel file so it can be examined.

 

[Dave_epic]

Sorry, namliam.

Have been on lunch. Yes the last space could act as the deliminator. This is probably better than using the first number right? Sorry for my ignorance, I'm a bit new at this. I wonder if you could help me using the "InstrRev" to find the space.

About excel, I could spilt it when I import from excel with a space deliminator I guess but I am trying to learn a different way to do it by figuring out the correct sql statment to do this in a query.

Thanks again

 

[namliam]

InstrRev is better explained by Access help, instead of me...
InstrRev("String","SearchCharacter(s)", -1), this will return a number.
This number is the Character number where the character is found counting from the left.

Left("String", InstrRev()) will then return up to the first number + Space
Mid("string",InstrRev()) will then return the space + the first number and the rest behind it.

 

[Dave_epic]

Thanks everyone

this worked in the end

Expr1: Mid(

.[field],InStrRev(

.[field]," ")+1)