Find and Replace

[kjxavier]

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:

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! (pls mention if it can be better done with MS Excel)

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

[macropod]

If the goal is simply to insert a tab before the assumed city in the sample data, that could be accomplished with a wildcard Find/Replace, where:
Find = ([A-Z]*[A-Z]*[A-Z]*[A-Z]*)([A-Z][!^13]@^13)
Replace = \1^t\2
And, if cleaning up the split words is required, that could be accomplished with just:
Find = ( )([a-z])
Replace = \2
Note that this would also fix the split words in the 2nd problem.

While the process offered works for the limited examples supplied, there are many other situations in which the same process will fail. For example:

Mr.Albert Van der Meer New York.NY
Mrs.Ann M St John Rhode Is.NY
where 'Van der Meer' or 'St John' is the surname. The process messes up both surnames, joining the 'Van der' together as one word when it should remain two words and putting the tab between 'St' and 'John' in the 'St John' surname! The process also won't work if you have only one given name or initial for the person. I'm sure I could find plenty of other situations where the result would be incorrect, too.

The process only appears to work - because the sample data are not sufficiently comprehensive. To do the job properly requires a lookup table of cities and states.