Extract text

[Springman]

I have a list of [Drug_name] records as follows:

Lipitor tab 20 mg
Tylenol cap 500 mg

I would like to extract text to the left of the the words "cap" or "tab" or
if "cap" or "tab" is not in the string I'd like the record to remain
unchanged.

Any help would be appreciated.

 

[John W. Vinson]

I have a list of [Drug_name] records as follows:

Lipitor tab 20 mg
Tylenol cap 500 mg

I would like to extract text to the left of the the words "cap" or "tab" or
if "cap" or "tab" is not in the string I'd like the record to remain
unchanged.

Any help would be appreciated.

Tricky. Try this (all on one line):

Mid([Drug_name], 1,
IIF(InStr([Drug_Name], " tab ") > 0, InStr([Drug_Name], " tab ") - 1,
IIF(InStr([Drug_Name], " cap ") > 0, InStr([Drug_Name], " cap ") -1,
Len([Drug_Name]))))

This is a good example of the need to "atomize" the field further. It's a lot
easier to concatenate [Drugname], [DosageForm], [Dose] and [Unit] than it is
to decompose a field containing four different types of data!