Hi,
I'm using Excel to convert relatively unformatted text into something more
structured. My problem is specifically with converting a full name into
separate fields. The data looks something like:
Smith, Sam S
Jones_ Jim J Jr.
Taylor Tom T.
Neat Ned
Friendly, Dr

You get the picture.
It doesn't matter to me if I use VB or a worksheet formula, or some other
mechanism. This can't be the first time that anyone has converted
concatinated names into seperate fields. Any advice?
--
Programmer on Budget

The first step will probably be to "clean" the data. If you really have some
names separated by commas, underscores, spaces, etc. it would be helpful to
pick one and replace the others with that category. I am thinking that
spaces will be easiest since you might want to keep the spaces for some names
like Von Flue James.

Once you have them separated with spaces you can use the VBA split function
to break each name into an array separated by spaces.

Names = split(Name(x))

Then you can manipulate the Names array to pull out the first and last names.

Dan


"Budget Programmer" wrote:

Hi,
I'm using Excel to convert relatively unformatted text into something more
structured. My problem is specifically with converting a full name into
separate fields. The data looks something like:
Smith, Sam S
Jones_ Jim J Jr.
Taylor Tom T.
Neat Ned
Friendly, Dr

You get the picture.
It doesn't matter to me if I use VB or a worksheet formula, or some other
mechanism. This can't be the first time that anyone has converted
concatinated names into seperate fields. Any advice?
--
Programmer on Budget

if u are not using vb then
save the name in a single cell
then insert as many columns u want to the Right side of the cell
select all cells (newly inserted+cells containing name)
and use "text to column " on data menu
--
hemu


"Dan Hatola" wrote:

The first step will probably be to "clean" the data. If you really have some
names separated by commas, underscores, spaces, etc. it would be helpful to
pick one and replace the others with that category. I am thinking that
spaces will be easiest since you might want to keep the spaces for some names
like Von Flue James.

Once you have them separated with spaces you can use the VBA split function
to break each name into an array separated by spaces.

Names = split(Name(x))

Then you can manipulate the Names array to pull out the first and last names.

Dan


"Budget Programmer" wrote:

Hi,
I'm using Excel to convert relatively unformatted text into something more
structured. My problem is specifically with converting a full name into
separate fields. The data looks something like:
Smith, Sam S
Jones_ Jim J Jr.
Taylor Tom T.
Neat Ned
Friendly, Dr

You get the picture.
It doesn't matter to me if I use VB or a worksheet formula, or some other
mechanism. This can't be the first time that anyone has converted
concatinated names into seperate fields. Any advice?
--
Programmer on Budget