Remove all characters following the first character in a string
 

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

Assuming the middle names are in Column B, in another column (say D1) enter
the formula =Left(B1) and copy it down.

Hope this helps
Rowan

"RC" wrote:

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

Do you need to keep the original middle names?

If no, I'd select that column
data|text to columns
fixed width
draw a line between the first and second character
(and remove any that excel guessed)
And choose to ignore (skip) the second column.

If you need to keep the data, I'd use another column with a formula like:

=left(c1,1)

and drag down.



RC wrote:

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

--

Dave Peterson

Hi,

Add another column and insert this formula there and copy it down to
entire column.

=LEFT(B1,1)

where B1 is the Middle name. This formula will return the first
character of the middle name which can be used as a middle name.

If you want the middle name column to be replaced, copy this formula
column and paste as value in the middle name column.

Regards

Govind.

RC wrote:

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

Thanks Rowan, since there several columns of data on either side of those
middle names, I can insert a column, use the formula you recommended in the
"new" column, and remove the "old" column with the middle names when finished.
Thanks again,
RC

"Rowan" wrote:

Assuming the middle names are in Column B, in another column (say D1) enter
the formula =Left(B1) and copy it down.

Hope this helps
Rowan

"RC" wrote:

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

Thanks Dave, this worked out great, as the middle names were no longer needed.
RC

"Dave Peterson" wrote:

Do you need to keep the original middle names?

If no, I'd select that column
data|text to columns
fixed width
draw a line between the first and second character
(and remove any that excel guessed)
And choose to ignore (skip) the second column.

If you need to keep the data, I'd use another column with a formula like:

=left(c1,1)

and drag down.



RC wrote:

In a spreadsheet containing a long list of names, with the last, first, and
middle names in separate columns, what is the easiest way to replace each
middle name with only a middle initial?

--

Dave Peterson