Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

You can use text to column, choose as delimiter a space. To subsequently get
rid of the period do an <Edit<Replace

--
Regards,
Dave


"Charles" wrote:

Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

Hi,

Use Data = Text to Columns = Delimited [Next] = space

Ewan

"Charles" wrote:

Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charles" wrote in message
...
Hi All:

I have a list of names stored in MS Excel. Last name is in first column
but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle
initial
to third column. The problem is that the length of first name is not
same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

or, in case the first name has a space in it (bobby sue, ray allen, john
paul, etc.)

=IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,2),"")

"Bob Phillips" wrote:

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charles" wrote in message
...
Hi All:

I have a list of names stored in MS Excel. Last name is in first column
but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle
initial
to third column. The problem is that the length of first name is not
same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

Hi Dave:

That way works well. Thanks.


Charles


"David Billigmeier" wrote:

You can use text to column, choose as delimiter a space. To subsequently get
rid of the period do an <Edit<Replace

--
Regards,
Dave


"Charles" wrote:

Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

Thanks a lot, Ewan. This way works well.

Charles


"ewan7279" wrote:

Hi,

Use Data = Text to Columns = Delimited [Next] = space

Ewan

"Charles" wrote:

Hi All:

I have a list of names stored in MS Excel. Last name is in first column but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle initial
to third column. The problem is that the length of first name is not same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

Hi Bob:

Thank you very much. I think this formua should be more useful. But could
you tell me how I can use it. I have never used such kind of function
formula before. Thanks.


Charles


"Dominic" wrote:

or, in case the first name has a space in it (bobby sue, ray allen, john
paul, etc.)

=IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,2),"")

"Bob Phillips" wrote:

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charles" wrote in message
...
Hi All:

I have a list of names stored in MS Excel. Last name is in first column
but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle
initial
to third column. The problem is that the length of first name is not
same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

Charles,

I'm sure Bob can give you a much better solution and more eloquent
explanation, but here are my two cents.

The formula:

=IF(AND(ISNUMBER(FIND(" ",B2)),ISNUMBER(FIND(".",B2))),RIGHT(B2,2),"")

Looks for a space and a period in cell B2. If it finds both a space and
period, it then returns the RIGHT-MOST 2 characters in cell B2. If it does
not find a space and a period, it will return a blank. This should work
assuming that all middle initials in your data have a period and are one
letter. If not, it might need to be tweaked a bit.

To use this, insert a new column next to your first name column (in the
formula, the first name column is assumed to be "B"). Type the formula in B2
(assumed to be the first row of data) and copy down for all the rows you have
data in.

Does that work?

HTH


"Charles" wrote:

Hi Bob:

Thank you very much. I think this formua should be more useful. But could
you tell me how I can use it. I have never used such kind of function
formula before. Thanks.


Charles


"Dominic" wrote:

or, in case the first name has a space in it (bobby sue, ray allen, john
paul, etc.)

=IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,2),"")

"Bob Phillips" wrote:

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charles" wrote in message
...
Hi All:

I have a list of names stored in MS Excel. Last name is in first column
but
first name and middle initial are in second column. The file looks like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move middle
initial
to third column. The problem is that the length of first name is not
same.
Definitely I cannot use Text to Column. Any help or suggestions are very
appreciated.


Charles

The only point I would add is the use of ISNUMBER and FIND. If FIND gets a
match in the target string with the lookup string, it returns the offset
into the target string of the lookup string. If no match is found, it
doesn't return 0, it returns an error, so ISNUMBER(FIND simply tests if a
successful match has been made. Then

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

just takes the character after that matched offset, and just takes 99
(MID(B2, offset+1,99)) more characters on the basis that that will mop up
all the remaining characters.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dominic" wrote in message
...
Charles,

I'm sure Bob can give you a much better solution and more eloquent
explanation, but here are my two cents.

The formula:

=IF(AND(ISNUMBER(FIND(" ",B2)),ISNUMBER(FIND(".",B2))),RIGHT(B2,2),"")

Looks for a space and a period in cell B2. If it finds both a space and
period, it then returns the RIGHT-MOST 2 characters in cell B2. If it does
not find a space and a period, it will return a blank. This should work
assuming that all middle initials in your data have a period and are one
letter. If not, it might need to be tweaked a bit.

To use this, insert a new column next to your first name column (in the
formula, the first name column is assumed to be "B"). Type the formula in
B2
(assumed to be the first row of data) and copy down for all the rows you
have
data in.

Does that work?

HTH


"Charles" wrote:

Hi Bob:

Thank you very much. I think this formua should be more useful. But
could
you tell me how I can use it. I have never used such kind of function
formula before. Thanks.


Charles


"Dominic" wrote:

or, in case the first name has a space in it (bobby sue, ray allen,
john
paul, etc.)

=IF(ISNUMBER(FIND(" ",B2)),RIGHT(B2,2),"")

"Bob Phillips" wrote:

=if(ISNUMBER(FIND(" ",B2)),MID(B2,FIND(" ",B2)+1,99),"")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charles" wrote in message
...
Hi All:

I have a list of names stored in MS Excel. Last name is in first
column
but
first name and middle initial are in second column. The file looks
like:

Last name First Name
West Joe
Hellygard David J.
Huang Nancy H.

Now I want to separate first name and middle initial and move
middle
initial
to third column. The problem is that the length of first name is
not
same.
Definitely I cannot use Text to Column. Any help or suggestions
are very
appreciated.


Charles