Change the format of a mailing list imported from Corel WP

[ExcelReRe]

I have a list of addresses that I received from an old version of Corel Word Perfect. I managed to get it converted to excel by going through word 2007. This was emailed to me as a project for the County I work for. I really need the help!

in excel the addresses were formatted as follows:

Dr. Marvin Harris
1699 hwy 435
Anytown, TN 55555

John Allen Baitman
Baitman Bait Co
55 high street
thistown Tn. 55535

Mr. Allen Kwick, Jr.
104 Highland
Apt 12
anothertown TN 55698

Is there any way that I can have excel auto sort them into columns that I setup

Last Name - First Name - Company - Street Address - Address 2 - City - State - Zip



I have been trying to do this manually but there is over 600 addresses...

Also some entries have leading spaces and I cant figure out how to remove them.

Thanks for any help!!!

[MarvinP]

Hi ExcelReRe,

Welcome to the forum and to a problem I've had many times. There is no perfect formula (that I've found) for this problem. You can =Trim() your data to remove leading and trailing spaces. You might also =Clean() your data as you'll never see those unprintable characters hiding in the text.

Now for a partial answer:

On the first row where Dr. Harris is, lets say he is in A1 you put a formula in B1 = A2, In C1 = A3, In D1 = A4 etc.
You then copy the formulas in B1 to G1 down to the next row that has a new name.
This saves some time but still ends up needing to comb through your data by hand in the end.

There are other tricks to extract the city state and zip into their own fields.

Be aware that you will need to copy and paste values only to another sheet or columns in your sheet before you delete column A data.

I hope this helps some. If everyone had the same format for keeping addresses it would be a lot easier.

[ExcelReRe]

Lets say the address are all formatted the same. What could I do to make it easier. And can you elaborate more on the =a1 =b2 I am trying that but its really not working.

[MarvinP]

Attach the file with the first 5 names changed on your list. I'll fill in the formulas and attach it back. Having an example with formulas is a lot easier than trying to put them in text. Leave in 20 names for a better answer.

Click "Go Advanced" below and then on the paperclip icon. This will give us some data to work with and a few formulas to see.

The problem is normally that Address 2 isn't in many addresses. This messes up things. The first name, last name data has Mr. or nothing so it is hard to know one from another.

[ExcelReRe]

I have got all the addresses corrected and now all I have left to do is :

I have two columns column A has just the last name and Column B has the whole name
How can I remove from Column B what is in Column A?

Last Name - First Name
Byrd - Jimmy Byrd
Turner - Johhny Allen Turner

Is this possible?