Separate Middle Initial From First Name

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi,

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

Ewan

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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),"")

 

[Guest]

Hi Dave:

That way works well. Thanks.


Charles

 

[Guest]

Thanks a lot, Ewan. This way works well.

Charles

Hi,

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

Ewan

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)