Text To Columns Question

[bh24524]

I am about to set up a macro for a report I need to download weekly that has employee address information in order to stay current on their addresses for a mail merge I use weekly. Basically there are unneeded columns and then sorting that needs done. Unfortunately, the report comes with their First and last name in the same column and it is put as first name first. I have to use the text to columns feature to separate their first and last name at each occurrence of a "space" Here is the problem: Some people might have a name with a space like "Mary Ann" or "Mary Jane". Another person has a last name with spaces like "Van Atta" and other might have Jr or Sr afgter their last name. The issue with that is in the column that has the last names, you will have "Ann" in there from "Mary Ann" or "Van" from the last name "Van Atta" You would then have "Mary Ann's" Last name in a third column as well as "Atta" in that same column and Jr and Sr. Obviously with this, I couldn't use a formulas to have the last names column be first since you have part of a first name in there. Is there some type of workaround I can use for this?

 

[Joe4]

I feel your pain!

Unfortunately, programming isn't magic, it is very literal. It can only do what you tell it to.
So, if you can not write down on pen and paper all the exact rules it should follow, then you aren't really going to be able to program anything to do it.

This is a common problem I see often (and have experienced myself). It is hard to clean up badly formatted data.
Usually, the best you can do it to program something that handles most of the cases, and then handle the rest manually.
Or, try to get the data provider to change the data format into something that is more usable!

 

[Fluff]

Hia
How about something like this

Sub SplitName()
                 
    With Range("G1:G25")
        .Formula = "=IF(ISERROR(LEFT(F1, FIND("" "",F1) - 1)),"""",LEFT(F1, FIND("" "",F1) - 1))"
        .Offset(, 1).Formula = "=IF(ISERROR(RIGHT(F1, LEN(F1)-FIND("" "",F1))),"""",RIGHT(F1, LEN(F1)-FIND("" "",F1)))"
        .Value = .Value
        .Offset(, 1).Value = .Offset(, 1).Value
    End With

End Sub

This looks at column F & puts first name in G & last name(s) in H

 

[Joe4]

Fluff,

That doesn't address the issues they are raising:

Here is the problem: Some people might have a name with a space like "Mary Ann" or "Mary Jane". Another person has a last name with spaces like "Van Atta" and other might have Jr or Sr afgter their last name. The issue with that is in the column that has the last names, you will have "Ann" in there from "Mary Ann" or "Van" from the last name "Van Atta" You would then have "Mary Ann's" Last name in a third column as well as "Atta" in that same column and Jr and Sr. Obviously with this, I couldn't use a formulas to have the last names column be first since you have part of a first name in there. Is there some type of workaround I can use for this?

That will always split the name after the first space. So names like "Mary Ann Smith" would not split properly.

 

[Fluff]

Got you.
Misunderstood the OP, I was thinking of Mary Ann as First name/surname rather than just as first name