Coding multiple text in Excel column, return value to adjacent column

Hello,

I am new to this forum, and am a beginner Excel user.

I have a list of 186k position titles in a column that have been manually entered by various people in a survey. The text is all over the place in terms of punctuation, spaces, capitals, etc..

I want to analyse this text, looking for certain key words or partial text (like “director”/"direct" or “president”/"pres", or “treasurer”/"tres" for example) and assign a code in another column. This will allow me to collapse the list into a manageable range of say 10-20 position titles, with the remainer coded as a particular value for "other".

I have been looking on-line but I don’t see a solution that would suit my purposes. I was thinking it would be a macro with multiple “if” statements resulting in multiple codes in an adjacent column (the position titles I am interested in), else “other” (with its own code value).

I am in a time crunch otherwise I could take my time to try and find a solution on-line.

Do you know how I would do this?

Thanks in advance for any help you could offer.

Tania

Just to confirm the scenario ..

You have in column A, rows 1 --> x a list of position titles that vary:

President = Pres / pres / president / presid / pres. / preside / etc etc..
Director = Director / dir / director / direc. / dire. / direct / direct. etc etc ..

How many different types of titles do you have?

Hello, thank you for the reply. I have uploaded a sample of the data (I think). The number beside the data is a count. You can see I have quite a lot of blank spaces too. The data is in English and in French. Don't ask me why they did not create a drop-down menu of pre-set titles before the survey was sent out!!

Tania

I mean how many proper ones do you have? not the forms of the titles..

So say you have Director (Dir, director, direct etc), Pastor (Pastor, pastor, past.) General (Gen, Gener, general) .. you would only have 3 "proper" titles