Parsing names and Zip code questions

[tcollins]

I do a lot of spreadsheets where the original may have a set up like this

Column A:
James Doe Jr
Jonathan Doe Jr

I need it set up with the first name in the first column and the last name and Jr put into the second column. I have tried text to columns but it puts each part of the name as separate columns and in reality, makes more work for me so instead somebody sent me this formula:

=LEFT(A1,(FIND(" ",A1)-1))&" "&IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1))," ",MID(A1,(FIND(" ",A1)+1),(FIND(" ",A1,FIND(" ",A1)+1))-(FIND(" ",A1))))

It works great except for one thing, sometimes it will put Jr with the last name or sometimes it keeps the first and last name in column A and puts Jr in it's own column so I have to go through and manually fix it which takes a lot of time when I have 8000 names or more to go through. Does anybody have any suggestions?

Also, I have a question about zip codes. I get spreadsheets that will have zipcodes where some of them have the regular zip along with the four digit extension but no dashes but it also has just the regular zip code with no extensions. So they look like this:

123451234
12345

What I am looking for is a way to put the dash in automatically and when there is no extension it puts in -0000. I have tried formatting them but instead of making the regular zip code look like: 12345-0000 it will do this:

00001-2345

Hope that makes sense. Again, it takes me a ton of time when I have to go in and manually add the dashes where they are needed. By the way I am using Excel 2003.

Any help would be appreciated.

Tracy

 

[Smitty]

Welcome to the Board!

For the first see: http://www.cpearson.com/excel/FirstLast.htm

For the second you can use a custom format: [<=99999]#####;#####-####

Hope that helps,

 

[tcollins]

Thank your for the welcome and the help.

The cpearson.com site wasn't exactly what I was looking for but it has helped me to tweak my original formula and see if I can get it to work so I am trying different things for that.

Also, I tried the custom format that you suggested but it won't format the column for me so then I thought maybe there was some hidden formatting so I tried putting the number 1 in the next column and then doing a paste special>multiply and then formatting it and it worked okay except any zip code that started with zero, excel dropped that zero. Any ideas how to fix this? I'm sure it's something simple, I just don't know what it is.

Tracy

 

[Smitty]

Also, I tried the custom format that you suggested but it won't format the column for me

Do you mean you applied the format, then nothing happened? That happens sometimes when you have pre-formatted data in a cell and try to apply something new.

Once you've applied the new format you can goto Data-->Text to columns-->Finish to apply the format.

 

[tcollins]

That worked great. Except, the zipcodes start with 0 and excel automatically drops them. How do I fix that?

Example:

071038152
07083

Become:

71038-8152
7083

I tried following the info on this thread:

http://www.mrexcel.com/forum/showthread.php?t=343028

but it still didn't work.

Tracy
<table x:str="" style="border-collapse: collapse; width: 53pt;" border="0" cellpadding="0" cellspacing="0" width="70"><col style="width: 53pt;" width="70"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 53pt;" width="70" height="17">
</td> </tr></table>

 

[Smitty]

Try this format instead:

[<=99999]00000;00000-0000

 

[tcollins]

Thanks. Worked great. I figured it was something fairly simple. Being self taught I don't know all the formulas and little tricks but I'm working on it.


Tracy