Parsing names and Zip code questions

tcollins

New Member
Joined
Aug 18, 2008
Messages
7
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
 

tcollins

New Member
Joined
Aug 18, 2008
Messages
7
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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Aug 18, 2008
Messages
7
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>
 

tcollins

New Member
Joined
Aug 18, 2008
Messages
7
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
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top