Need to separate joint names

[soojosie]

I have a list with joint names and need to
1) Separate them
And then if not too difficult
2) Have Initials & Last Name in separate fields
Ideally
Lees Mark John & Lees Amy Kate -M J Field1, A K Field2 and Lees Field3

White Kate May - K M Field1, White Field2
Occassionally there are single name entries

Also need to separate address as Street address, City and Code all in single cell
Eg
1 May Street WELLINGTON WA 6425
City is in upper case but then so is the State, however state is only in about 50% of addresses. I need Street Address to be separate to City. City & Code together/seperate OK either way.
Any ideas?

 

[soojosie]

Hej AceMan1,
Yes I can now really see what a mountain of work it is. It is way, way too much for you to consider. But thanks anyway.

I have worked out this DATA >> text to Col function.
I have separated all the easy stuff.
ID#, Type, Names, Address1 and Address2
Then I Concatenated (just found this out tonight) the cols with Street#, StreetName and StreetType. Same with City, State, PostCode. So I can use all these macros to get it to a usable state.

My remaining concern and its a pretty big mountain all of its own is the Names - some people have 2 MiddleNames!! they mess up the whole system and even tho I don't need to use them both they seem difficult to get rid of and I still can't work out how to get just initials for FirstMiddleNames


I have emailed you a copy of the workbook - could you look at it for me?

Thanks again
Josie

 

[TheAceMan1]

[blue]I have worked out this DATA >> text to Col function.
I have separated all the easy stuff.[/blue]

For 2000 records! . . . . Wow . . . thats an accomplishment!

[blue]some people have 2 MiddleNames!![/blue]

This was one of the inconsistentcies I spoke about. I'm curious how you got around the inconsistent spacing?

I'll give the workbook a critique and post back . . . .

See Ya! . . . . . .

 

[soojosie]

Hej Aceman1,
I've just worked it all out, that is I think I have.
I was, as ever overlooking the obvious, I separated the names by using the “&” symbol with data >>text to col this way I have all the LastNames in 2 cols LastNames1 and LastNames2 did a quick parse on the initials, then Concatenated the 3 cols, no need to differentiate between those with I or 2 MiddleNames. Some have I initial other have 3.
It is a clumsy way of doing it but as it is done in cols now that I have the system a page can be done with cut and paste functions in a couple of minutes.

The trick was to move Address2 out and remove the empty rows in between. I’m going to use a macro to do this, once I have everything in consecutive rows I just drag all the formulae down with auto fill.

Have I explained this in a way that is understandable? Turns out I have many many more than 2000 records, but now I feel it's going to be O.K.

many, many thanks for all your help and support, made me feel that I was not alone with the impossible, really helped me sort things out rather then just panicking.

cheers
Josie)

 

[TheAceMan1]

soojosie . . . . .

Way ta go. . . . Nothing like solving things through your own devices.

If you need any future help to know where to find us.

[blue]You take care! . . . . . Ya Hear![/blue]

See Ya! . . . . . .