left or substring function

[SAS26]

Hi People,

INFO: Running Excel 2003 SP2

I have a simple problem i'm sure but a bit of a newbie when it comes to excel functions etc.

The problem I have and see picture attached is a column with contact data. As you will see I have Mr/Mrs information + Initial of first name + last name. Not exactly the best way to store data, especially in one col. What I require is to extract the Mr/Mrs/Miss out of this column and insert to the column to the the right for example. I guess a substring or left function is what I need, however not too sure how to go about this.

Something like IF substring = "Mr" then populate column to the right with "Mr" or IF substring = "Mrs" then so on and so forth. So far the column only holds either Mr, Mrs, Miss but would like to be able to add Dr, Hon etc.

Any ideas would be much appreciated.

All the best,

SAS26

[Audiguy82]

Im not too good at functions either, but I think the answer to your question is on the first page of the new excel users section. Its called seperating cell info or something like that, its on the first page and I think you could use that info. Hope it helps you

[SAS26]

OKAY thanks for the info. I will check it out.

SAS 26

[daddylonglegs]

You could try Data > Text to columns with space as a delimiter or use a formula to extract the title (assuming there always is one)

=LEFT(A1,FIND(" ",A1&" ")-1)

[SAS26]

Daddylonglegs, thanks for the info. Much appreciated. I think either text to columns or the statement you provided "=LEFT(A1,FIND(" ",A1&" ")-1)" should do the trick. 90% of the data in my column is "Mr" or "Mrs" so could run the formula based on the number of characters using left function.

Nice one.

All the best,

SAS26