Find and Replace, Formatting, Formatting Formula

Hi! Am using MS Office 2007,

I have a list of Names followed by the City and State, I need to separate the the Names, City and States

Eg:

Mr.Thomas B Fernan dez New York.NY
Mr.Bar ry K Olivia Houston.TX
Ms.Dor othy D Hawell Rocky Mount.NC
Mr.Tho mas L Morg Deerpark.NY
M rs.Jennifer D Lebl anca Carlisle.PA


Expected Result:<tab> represents next column

Mr.Thomas B Fernandez <tab>New York.NY
Mr.Barry K Olivia <tab>Houston.TX
Ms.Dorothy D Hawell <tab>Rocky Mount.NC
Mr.Thomas L Morg <tab>Deerpark.NY
Mrs.Jennifer D Leblanca <tab>Carlisle.PA

Suggestions:

1. Take off all <SPACE> continued by small letters (Fernan dez - Fernandez; Bar ry - Barry; Dor othy - Dorothy; M rs. - Mrs.; Lebl anca - Leblanca, so on......)

2. Names always have the same format. i.e.: SALUTATION.<FIRSTNAME>SPACE<Initial of MIDDLE NAME>SPACE<LAST NAME>

3. Place a <TAB> before the 5th Capital Letter, i.e. before city 'New York' in first case.

which can give the above mentioned result.

Its really urgent for me! Please help me out!

Thank you in advance! your early reply is highly appreciated.

This one is very similar to your second problem in your other post and we solve it in a similar way.

First remove all the Spaces in the string
Then use your macro to insert a space before each capital letter.
That should give you:

                       A
1) Mr. Thomas B Fernandez New York. NY
2) Mr. Barry K Olivia Houston. TX
3) Ms. Dorothy D Hawell Rocky Mount. NC
4) Mr. Thomas L Morg Deerpark. NY
5) Mrs. Jennifer D Leblanca Carlisle. PA

Next we need to replace the 4th instance of a Space character, so in B1 enter the formula:

=SUBSTITUTE(A1," ","|",4)

This should give you:

                       A                                   B
1) Mr. Thomas B Fernandez New York. NY      Mr. Thomas B Fernandez|New York. NY
2) Mr. Barry K Olivia Houston. TX           Mr. Barry K Olivia|Houston. TX
3) Ms. Dorothy D Hawell Rocky Mount. NC     Ms. Dorothy D Hawell|Rocky Mount. NC
4) Mr. Thomas L Morg Deerpark. NY           Mr. Thomas L Morg|Deerpark. NY
5) Mrs. Jennifer D Leblanca Carlisle. PA    Mrs. Jennifer D Leblanca|Carlisle. PA

You can now use Text to Columns and split up the cell into it's separate parts using the pipe symbol.

A couple of problem, you will probably have, is a name with NO middle initial or a name not capitalized.
That will mess up the number of spaces in the string.
You may need to tweek the formula a bit.