How to create salutation in Excel spreadsheet ???? :(

Hi guys, I have long list of names (about 5000), I would like to create a salutation column. I want the salutation result in the fourth column of my spreadsheet. Can someone please advise. Please see my list below. it would be great If the fourth column results like Mr Miller, Mr Lampitt, Pamela, Mr Vucich, Barney, Mr Newman, Naomi etc. I can use concatenate formula to get "Dear" in the front

Title First Name Surname
Mr David Miller
Mr C F Lampitt
Mrs Pamela Towers
Mr P J Vucich
Mr Barney Sowerby
Mr Thomas P Newman
Mrs Naomi ****
Mrs A A Ross
Mr M Rennie
Mr A Swney
Mr Patrick Webby

Much appreciated.
Cheers

Thanks for this. But sorry I got forgot to include important information that many names don't have a title for which I would prefer just a first name as result. I reckon there is some IF function formula to get these results. the result 2 column is optional, which I can work on them and get the 'result 2' using concatenate formula. But If you know about IF formula to get the 'result 1' column that would be great. cheers

title firstname surname result 1 result 2
Mr D Eggers Mr Eggers Dear Mr Eggers
Linda Belcher Linda Dear Linda
Ms Dorothy Bogers Ms Bogers Dear Ms Bogers
Mr Graeme Stewart Mr Stewart Dear Mr Stewart
Derek Johnston Derek Dear Derek
Mrs Kerri Major Mrs Major Dear Mrs Major

Mr & Mrs David & Margaret Cross Mr & Mrs Cross Dear Mr & Mrs Cross

=TRIM(IF(A2="",CONCATENATE("Dear"," ",B2),CONCATENATE("Dear"," ",A2," ",C2)))

hi that formula works for first row and if drag it to the bottom, your formula result is below.
title firstname surname result 1
Mr D Eggers Dear Mr Eggers
Linda Belcher Dear Belcher
Ms Dorothy Bogers Dear Ms Bogers
Jenni Stiles Dear Stiles

but I want Dear 'Firstname' for those who don't have a title cell content. eg. for Linda Belcher---> I want Dear Linda (not Dear Belcher) & for jenni Stiles--->Dear Jenni (not Dear Stiles) Please let me know if you know!
but your formula works for the names who have a title.

You are welcome

If problem is resolved, add star and mark thread as solved. Thank you.

Thanks for rep

OMG..... great... it worked... Thank you heaps.... ) you genius made my day! :-)

Hi thanks very much for this. I was about to post this question, as I realised later, that my data had many names with single letter first names and with no title.

This formula is much more useful, but can you please alter the formula to get Dear supporter for those who don't have title, but have single or double letter first names eg. J Smith or J R Smith or JR Smith (I just want the result - Dear Supporter)

Formula:

=TRIM(IF(AND(TRIM(A2)="",LEN(TRIM(B2))<=3),"Dear Supporter",IF(AND(TRIM(A2)<>"",LEN(TRIM(B2))<=3),CONCATENATE("Dear"," ",TRIM(A2)," ",TRIM(C2)),IF(TRIM(C2)="",CONCATENATE("Dear"," ",TRIM(B2)),CONCATENATE("Dear"," ",TRIM(B2))))))

Something like this?

Formula:

=TRIM(IF(AND(TRIM(A2)="",LEN(TRIM(B2))<=3),"Dear Supporter",IF(TRIM(A2)="",CONCATENATE("Dear"," ",TRIM(B2)),IF(TRIM(C2)="",CONCATENATE("Dear"," ",TRIM(B2)),CONCATENATE("Dear"," ",TRIM(A2)," ",TRIM(C2))))))

Yes, thanks This is want I want. I appreciate it
And just checking if the following option is possible? Can you alter the formula for eg. Mr Jack Smith (I want the result Dear Jack) The above formula you mentioned brings Dear Mr Smith as result.
I mean if a cell has full first name then its more friendly if we use the salutation Dear Firstname.
But note: my data has names like J Smith, JR Smith, J R Smith etc
so eg: J R Smith - Dear Supporter, J Smith - Dear Supporter, Mr Jack Smith - Dear Jack, Mr J Smith - Dear Mr Smith
Sorry for giving more work to your genius brain!

Wow.. Incredible.... You are a rock star! Thanks heapsssss!!!!

You are welcome.

Should I get another star?

I think I did close this thread and gave you another star before you even asked!

And actually it didn't allow me to add another rep, looks like I need to give/spread rep to other people before I give another one to you!

I see only one from you but never mind

It was joke

Thanks again & have a good rest of the day!

Thanks, 2U2