Can I separate cells with varying name data?

[djrome13]

I have an excel sheet that I need to separate out. I've already got the city/state issue solved, but I have a "name" cell that contains varying data ie) a1= Dr Joe Smith a2= dr and mrs john adams a3= mr greg j. Johnson

I need to separate it out as simply as possible....any suggestions? I have 14k cells, so manually will take forever.

Thank you all for your help in advance. Your advice has greatly helped in the past!

 

[FDibbins]

exactly how would you want to separate them? Maybe take a look at Text2Columns?

 

[djrome13]

I tried the T2C but since there are varying names/lengths it does not convert very well.

I'd like:
Title
First Name/Initial
Last Name

 

[Aktion]

First use Len to determine the length in characters of the name field
then use =search to find the locations of the spaces
then use left and right to pull the names
last name = right of the Len minus the position of the space
first name uses left

for salutations just search for them explicitly =search("mr", within text) and amputate them before applying the Len left right methodology

 

[djrome13]

The thing I've come across is there are a bunch of "Dr. And Mrs David J Jones" or "Mr and Ms Roger Franklin"...it seems like it's going to end up just being a lot of manual fixes any way I do it.

 

[FDibbins]

Could you give a range of names/titles, and how you wantthem to look?