Excel: Text to Columns (parsing) help

[Julie]

After I copy and paste data into Excel I will be parsing the name column and
not all names will have a middle initial and some names may have a two part
last name, such as: Van Dyke. Since I will be using the space as a
delimiter, a name could be divided into four parts while others might only
have two. This is what I see happening, but if I've missed another shortcut,
please let me know.

As I understand it, I would need to add three new columns to cover the
greatest possibilities and label them accordingly. However, if the first new
column is middle initial and the next two columns are for last name, when in
actuality I really want both parts of the last name to be in one column,
then when I proceed with the parsing, the last name of some of the contacts
will end up in the middle initial column. I would then need to cut and paste
into the appropriate column. This is all okay with me since it's still a
tremendous time saving feature, but I was wondering if perhaps there is a
better protocol. For instance, would you recommend scanning the list before
parsing and removing the space between the two part last names, such as:
VanDyke? Do you have any recommendations for best practice here?

any help is much appreciated

thank you

Julie

 

[Bob Tulk]

Is there anything that comes after the last name (e.g.
address, city, etc.)? If so, then that could be more
work. If it's just names (First, some middle initials and
last names), I would suggest using some formulas instead.

Let's say you have the following in A1 and A2 respectively:

George Bush
George W Bush

You would want to have B1 and B2 to both become George; D1
and D2 become Bush and C1 be blank and C2 be W.

I'll give you the formulas for row 1 and you can then just
copy them. When it's all done, you can just copy and do a
paste special - values to get then names (and not the
formulas).

B1 =LEFT(A1,FIND(" ",A1,1)-1)
C1 =IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+1)),"",MID
(A1,FIND(" ",A1,1)+1,1))
D1 =TRIM(RIGHT(A1,LEN(A1)-LEN(B1)-LEN(C1)-1))

The first on gets the characters up to the first blank
space. The second returns nothing if there is only 1
blank space in A1 and the middle initial if there is are
2. Finally, D1 uses the answers in B and C to return the
last the last name.

Bob Tulk
MOUS (97 & XP)

 

[hgrove]

Julie wrote...
...

As I understand it, I would need to add three new columns to
cover the greatest possibilities and label them accordingly.
However, if the first new column is middle initial and the next
two columns are for last name, when in actuality I really want
both parts of the last name to be in one column, then when I
proceed with the parsing, the last name of some of the contacts
will end up in the middle initial column. I would then need to cut
and paste into the appropriate column. This is all okay with me
since it's still a tremendous time saving feature, but I was
wondering if perhaps there is a better protocol. For instance,
would you recommend scanning the list before parsing and
removing the space between the two part last names, such as:
VanDyke? Do you have any recommendations for best practice
here?

You're close, but it'd be better to replace the spaces between multipl
word last names (and I've seem some with 4 words, German, Spanish an
American Indian) with underscores rather than deleting them. Also add
space and an underscore after first names with no middle names an
replace spaces with underscores between multiple middle names (e.g.
George Herbert Walker Bush). Then parse on spaces, and finally replac
underscores with spaces in all 3 columns