I would like to downloaded data (from our county's data base) and create a
mailing list. The downloaded data needs to be converted into First Name,
Middle Initial, Last Name.

For instance, here is a typical downloaded strings:

Grant Ulysses S.
Truman Harry S.

I want to get it to First Name, Middle Initial, Last Name. The result would
be used in mail merge and imported into Word. The final product would be:

First Name Middle Initial Last Name
Ulysses S. Grant
Harry S. Truman

How can this be executed in Excel? What functions are needed and what are
the formulas?

(I figures out how to handle the first space but cannot determine the next
formulas.)

Thanks for your help.
--
Dave
Cincinnati, OH

See Chip Pearson's site for assistance with different permutations.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

On Thu, 5 Nov 2009 16:18:01 -0800, OHDookie
wrote:

I would like to downloaded data (from our county's data base) and create a
mailing list. The downloaded data needs to be converted into First Name,
Middle Initial, Last Name.

For instance, here is a typical downloaded strings:

Grant Ulysses S.
Truman Harry S.

I want to get it to First Name, Middle Initial, Last Name. The result would
be used in mail merge and imported into Word. The final product would be:

First Name Middle Initial Last Name
Ulysses S. Grant
Harry S. Truman

How can this be executed in Excel? What functions are needed and what are
the formulas?

(I figures out how to handle the first space but cannot determine the next
formulas.)

Thanks for your help.

Hi Dave,

You will have to do this in severl steps. (Excel 2003)

First, import the data into your spreadsheet.
Highlight your data. If it is in column A just highlight all of the data in
column A
Then select Data from the menu bar
Select Text to Columns from the pull down menu
Make sure that the Delimited option is selected
Make sure that the Space and Comma options are selected in the check boxes.
Select Finish which will separate your data into columns A, B, and C

Next you have to create helper columns in columns D, E, and F which will
have the following formulas in them:
Cell D2 (as an example) = B2
Cell E2 =C2
Cell F2 =LEFT(A2,SEARCH(",",A2,1)-1)

Copy the formulas down however many rows you have. In columns D, E,and F
you should have what you are looking for. You can copy these cells and
paste over cells in columns A, B, and C by Selecting Edit Copy and then Edit
Paste Special -- Values.
If this helps, please press Yes

JCS

"OHDookie" wrote:

I would like to downloaded data (from our county's data base) and create a
mailing list. The downloaded data needs to be converted into First Name,
Middle Initial, Last Name.

For instance, here is a typical downloaded strings:

Grant Ulysses S.
Truman Harry S.

I want to get it to First Name, Middle Initial, Last Name. The result would
be used in mail merge and imported into Word. The final product would be:

First Name Middle Initial Last Name
Ulysses S. Grant
Harry S. Truman

How can this be executed in Excel? What functions are needed and what are
the formulas?

(I figures out how to handle the first space but cannot determine the next
formulas.)

Thanks for your help.
--
Dave
Cincinnati, OH

Hi Dave,

In my instructions, I forgot to add press the Next button when converting
the text to columns process. It will be apprent to you when to do so.

JCS

"JCS" wrote:

Hi Dave,

You will have to do this in severl steps. (Excel 2003)

First, import the data into your spreadsheet.
Highlight your data. If it is in column A just highlight all of the data in
column A
Then select Data from the menu bar
Select Text to Columns from the pull down menu
Make sure that the Delimited option is selected
Make sure that the Space and Comma options are selected in the check boxes.
Select Finish which will separate your data into columns A, B, and C

Next you have to create helper columns in columns D, E, and F which will
have the following formulas in them:
Cell D2 (as an example) = B2
Cell E2 =C2
Cell F2 =LEFT(A2,SEARCH(",",A2,1)-1)

Copy the formulas down however many rows you have. In columns D, E,and F
you should have what you are looking for. You can copy these cells and
paste over cells in columns A, B, and C by Selecting Edit Copy and then Edit
Paste Special -- Values.
If this helps, please press Yes

JCS

"OHDookie" wrote:

I would like to downloaded data (from our county's data base) and create a
mailing list. The downloaded data needs to be converted into First Name,
Middle Initial, Last Name.

For instance, here is a typical downloaded strings:

Grant Ulysses S.
Truman Harry S.

I want to get it to First Name, Middle Initial, Last Name. The result would
be used in mail merge and imported into Word. The final product would be:

First Name Middle Initial Last Name
Ulysses S. Grant
Harry S. Truman

How can this be executed in Excel? What functions are needed and what are
the formulas?

(I figures out how to handle the first space but cannot determine the next
formulas.)

Thanks for your help.
--
Dave
Cincinnati, OH