Populate a cell based on a keyword it found in another
 

Hi

I'm well out of my league on this one but its something that I really
need to do and was hoping someone could help

I have a column called Screener and each cell in that column contains
text data such as the following example

S1 ~ The specimen is satisfactory for evaluation. ~ 01;G1 ~ Negative
for intraepithelial lesion or malignancy. ~ 01;R1 ~ The next smear
should be taken at the usual screening interval. ~ 01

Another cell might contain different text

I cant change this as it is generated by software that I use and I
export that data to a spreadsheet.

What I need to do is run a macro to look at that text above along the
lines of - if the text in that cell contains the word S1 or the word
ASCUS or any other combination of words, then it will assign a text
value to it such as 'LG'. If it found some other word in that text
above then it might assign a different text type e.g if it found the
word HS1 then it would assign a value of 'HG'. It would put that
assigned value into a new column. It would then move to the next cell
in that column and perform the same function and so on.

So essentially I would like to compress all that text above into one
word depending on what key words it finds in that text. I have upwards
of several hundred records which I have to manually determine whether
its and LG or HG or something else and it would make life so much
easiier if I could read one word rather than a whole string of text. I
can then sort the data based on that new word it generated. Hope I've
made that clear. I have attached an example of what I'm trying to
acheive.

Any help would be most appreciated - I just dont have the necessary
skills to do this and was hoping someone out there would know the code
I would need to enter into the macro to acheive this end. Someone did
suggest an array formula but I would much rather stick with a macro as
I have less a grasp with formulae than I do with VBA.

kind regards

Steve


+-------------------------------------------------------------------+
|Filename: example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4748 |
+-------------------------------------------------------------------+

--
ghobbit
------------------------------------------------------------------------
ghobbit's Profile: http://www.excelforum.com/member.php...o&userid=12385
View this thread: http://www.excelforum.com/showthread...hreadid=541209