Trying to use specific text strings in A:A to populate something else in B:B

[urodoc]

Hi. I've tried using VLOOKUP, but I guess that didn't work because I need full values and not parts.

Column A has text that I need to transcribe into Column B as a specific Category. For instance, for every time "RX" appears in column A (not standalone necessarily) I need Column B to write "Medicine." For every time "Operative" or "OP" appears in Column A, I need "OP Report" to show up in column B. Here's a small example of what my A and B should look like. As it stands, A is full and B is completely empty.

AMBIENRX	Medication
AMOXICILLIN RX PHARM	Medication
SURGICAL PATH	Pathology Report
SURGICAL PATH 7/11	Pathology Report
PATIENT INTAKE	Past Record
PAST RECORD 2/13	Past Record
OP REPORT	OP Report
CT CYSTOGRAM 3/05/13	X-Ray

<tbody>
</tbody>

Any help is greatly appreciated, including letting me know that I'm asking for the moon!

Thanks a bunch

 

[urodoc]

Forgot to mention I'm in Excel 2010.

 

[Michael M]

Hi and welcome to the Board
I'm guessing this is only part of a bigger question....

So would a VBA soultion be suitable

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 1 Step -1
    If InStr(Range("A" & r).Value, "RX") Then
        Range("B" & r).Value = "Medicine"
    ElseIf InStr(Range("A" & r).Value, "OP") Then
        Range("B" & r).Value = "OP Report"
    End If
Next r
End Sub

 

[bschwartz]

	A	B
1	AMBIENRX	Medication
2	AMOXICILLIN RX PHARM	Medication
3	SURGICAL PATH	Pathology Report
4	SURGICAL PATH 7/11	Pathology Report
5	PATIENT INTAKE
6	PAST RECORD 2/13	Past Record
7	OP REPORT	OP Report
8	CT CYSTOGRAM 3/05/13

<tbody>
</tbody>

Formula: =IF(ISNUMBER(SEARCH("RX",A1)),"Medication",IF(OR(ISNUMBER(SEARCH({"Operative";"Op"},A1))),"OP Report",IF(ISNUMBER(SEARCH("Path",A1)),"Pathology Report",IF(ISNUMBER(SEARCH("Past",A1)),"Past Record",""))))

I dont know how to find "Past Record" from Patient Intake or "X-Ray" from CT CYSTOGRAM 3/05/13. If you have a criteria for that, I can add it to the formula.