Name Parsing

[George Andrews]

Excel 2000 W98SE

I have a large database which contains entries in the name field with
entries as below.

MR WILLIAM MARTIN SWEENEY
MRS FAITH ANNIE ELIZABETH PERRY
MR BERNARD ALBERT HUNT
MR JOHN FAIRHURST

I need to convert this filed into three different fielcd with new formats to
be able to create a pesonalized mail piece. The results I need are as
follows:

a) Title plus surname e.g.

Mr Sweeney
Mrs Perry
Mr Hunt
Mr Fairhurst

b) Title + Initials + Surname

Mr W M Sweeney
Mrs F A E Perry
Mr B A Hunt
Mr J Fairhurst

c) Title + First Name +Surname
Mr Sweeney
Mrs Perry
Mr Hunt
Mr Fairhurst

The capitalization of the different words is important and so is the spacing
between the different characters.

Previously I have used the text to columns function, then the various text
functions to extract the first letter of every word, change the case etc.
The real problem has been the different number of words that make up the
different fields. I am sure that there is either a simple VBA procedure of
an array formula (which I have yet to conquer) that can sort this problem
out easily.

Any guidance would be appreciated.

George

[David McRitchie]

Hi George,
Chip Pearson has something that might be set up to this, probably
pretty close if not. I know he bothered doing Titles.
http://www.cpearson.com/excel/FirstLast.htm
You have everything there except for simplest of all to take the
leftmost single characters for initials (LEFT).

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"George Andrews" <abct...@yahoo.com> wrote in message news:9m133o$h8s$1...@diana.bcn.ttd.net...

[George Andrews]

I have had a look at this and the problem is that the example has the
different names split with commas, whilst I have different length names and
only spaces between the words.

Any other ideas?

George

"David McRitchie" <dmcri...@msn.com> wrote in message
news:uD6Kgy0KBHA.1920@tkmsftngp04...

[Peo Sjoblom]

I know it's possible to parse them all out but I am not
going to put that thing together. Here's a quick and dirty
solution that assumes nobody will use more than three
names apart from the surname/last name, only a super snob
will use more than that! <bg>

First make sure there are 4 blank columns next to column A
(I am assuming that the name list is in A starting in A1).
If not, insert new columns to make sure that's the case.
Do Data>Text to Columns, when the wizard starts make sure
that the Delimited box is checked and click next, check
the other delimiter box and click in the box and press
space, then click Finish. Now the names should be in
separate columns. In column (using your sample) F cell F1
put this formula and copy down..

=A1&" "&INDIRECT(ADDRESS(ROW(1:1),COUNTA(A1:E1)))

that will give you title, first name and last name..

in Column G cell G1 put this formula and copy down

=IF(COUNTA(A1:E1)=5,A1&" "&LEFT(B1,1)&" "&LEFT(C1,1)
&" "&LEFT(D1,1)&" "&E1,IF(COUNTA(A1:E1)=4,A1&" "&LEFT(B1,1)
&" "&LEFT(C1,1)&" "&D1,A1&" "&LEFT(B1,1)&" "&C1))

this is the part that assumes that no more than 1st, 2nd
and 3rd names/initials are used...

Finally in column H, cell H1 put this and copy down

=A1&" "&B1&" "&INDIRECT(ADDRESS(ROW(1:1),COUNTA(A1:E1)))

Not a very good solution, yet somewhat of a solution <g>

Regards,

Peo Sjoblom

>.
>

[David McRitchie]

Hi George,
You might look at my page for SepTerm and SepLast macros
http://www.geocities.com/davemcritchie/excel/join.htm
for some more ideas.

HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"George Andrews" <abct...@yahoo.com> wrote in message news:9m16f2$lad$1...@diana.bcn.ttd.net...

[George Andrews]

Thank you all very much, your guidance has helped resolve the problem
sufficiently although not completely.

Cheers.

George