software

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.CharlesYou can use text to column, choose as delimiter a space. To subsequently get
rid of the period do an lt;Editgt;lt;Replacegt;

--
Regards,
Davequot;Charlesquot; wrote:

gt; Hi All:
gt;
gt; I have a list of names stored in MS Excel. Last name is in first column but
gt; first name and middle initial are in second column. The file looks like:
gt;
gt; Last name First Name
gt; West Joe
gt; Hellygard David J.
gt; Huang Nancy H.
gt;
gt; Now I want to separate first name and middle initial and move middle initial
gt; to third column. The problem is that the length of first name is not same.
gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; appreciated.
gt;
gt;
gt; Charles
gt;

Hi,

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

Ewan

quot;Charlesquot; wrote:

gt; Hi All:
gt;
gt; I have a list of names stored in MS Excel. Last name is in first column but
gt; first name and middle initial are in second column. The file looks like:
gt;
gt; Last name First Name
gt; West Joe
gt; Hellygard David J.
gt; Huang Nancy H.
gt;
gt; Now I want to separate first name and middle initial and move middle initial
gt; to third column. The problem is that the length of first name is not same.
gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; appreciated.
gt;
gt;
gt; Charles
gt;

=if(ISNUMBER(FIND(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Charlesquot; gt; wrote in message
...
gt; Hi All:
gt;
gt; I have a list of names stored in MS Excel. Last name is in first column
but
gt; first name and middle initial are in second column. The file looks like:
gt;
gt; Last name First Name
gt; West Joe
gt; Hellygard David J.
gt; Huang Nancy H.
gt;
gt; Now I want to separate first name and middle initial and move middle
initial
gt; to third column. The problem is that the length of first name is not
same.
gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; appreciated.
gt;
gt;
gt; Charles
gt;
or, in case the first name has a space in it (bobby sue, ray allen, john
paul, etc.)

=IF(ISNUMBER(FIND(quot; quot;,B2)),RIGHT(B2,2),quot;quot;)

quot;Bob Phillipsquot; wrote:

gt; =if(ISNUMBER(FIND(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Charlesquot; gt; wrote in message
gt; ...
gt; gt; Hi All:
gt; gt;
gt; gt; I have a list of names stored in MS Excel. Last name is in first column
gt; but
gt; gt; first name and middle initial are in second column. The file looks like:
gt; gt;
gt; gt; Last name First Name
gt; gt; West Joe
gt; gt; Hellygard David J.
gt; gt; Huang Nancy H.
gt; gt;
gt; gt; Now I want to separate first name and middle initial and move middle
gt; initial
gt; gt; to third column. The problem is that the length of first name is not
gt; same.
gt; gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; gt; appreciated.
gt; gt;
gt; gt;
gt; gt; Charles
gt; gt;
gt;
gt;
gt;

Hi Dave:

That way works well. Thanks.Charlesquot;David Billigmeierquot; wrote:

gt; You can use text to column, choose as delimiter a space. To subsequently get
gt; rid of the period do an lt;Editgt;lt;Replacegt;
gt;
gt; --
gt; Regards,
gt; Dave
gt;
gt;
gt; quot;Charlesquot; wrote:
gt;
gt; gt; Hi All:
gt; gt;
gt; gt; I have a list of names stored in MS Excel. Last name is in first column but
gt; gt; first name and middle initial are in second column. The file looks like:
gt; gt;
gt; gt; Last name First Name
gt; gt; West Joe
gt; gt; Hellygard David J.
gt; gt; Huang Nancy H.
gt; gt;
gt; gt; Now I want to separate first name and middle initial and move middle initial
gt; gt; to third column. The problem is that the length of first name is not same.
gt; gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; gt; appreciated.
gt; gt;
gt; gt;
gt; gt; Charles
gt; gt;

Thanks a lot, Ewan. This way works well.

Charlesquot;ewan7279quot; wrote:

gt; Hi,
gt;
gt; Use Data =gt; Text to Columns =gt; Delimited [Next] =gt; space
gt;
gt; Ewan
gt;
gt; quot;Charlesquot; wrote:
gt;
gt; gt; Hi All:
gt; gt;
gt; gt; I have a list of names stored in MS Excel. Last name is in first column but
gt; gt; first name and middle initial are in second column. The file looks like:
gt; gt;
gt; gt; Last name First Name
gt; gt; West Joe
gt; gt; Hellygard David J.
gt; gt; Huang Nancy H.
gt; gt;
gt; gt; Now I want to separate first name and middle initial and move middle initial
gt; gt; to third column. The problem is that the length of first name is not same.
gt; gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; gt; appreciated.
gt; gt;
gt; gt;
gt; gt; Charles
gt; gt;

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.Charlesquot;Dominicquot; wrote:

gt; or, in case the first name has a space in it (bobby sue, ray allen, john
gt; paul, etc.)
gt;
gt; =IF(ISNUMBER(FIND(quot; quot;,B2)),RIGHT(B2,2),quot;quot;)
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =if(ISNUMBER(FIND(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Charlesquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi All:
gt; gt; gt;
gt; gt; gt; I have a list of names stored in MS Excel. Last name is in first column
gt; gt; but
gt; gt; gt; first name and middle initial are in second column. The file looks like:
gt; gt; gt;
gt; gt; gt; Last name First Name
gt; gt; gt; West Joe
gt; gt; gt; Hellygard David J.
gt; gt; gt; Huang Nancy H.
gt; gt; gt;
gt; gt; gt; Now I want to separate first name and middle initial and move middle
gt; gt; initial
gt; gt; gt; to third column. The problem is that the length of first name is not
gt; gt; same.
gt; gt; gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; gt; gt; appreciated.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Charles
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

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(quot; quot;,B2)),ISNUMBER(FIND(quot;.quot;,B2))),RIGHT(B2,2),quot;quot;)

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 quot;Bquot;). 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?

HTHquot;Charlesquot; wrote:

gt; Hi Bob:
gt;
gt; Thank you very much. I think this formua should be more useful. But could
gt; you tell me how I can use it. I have never used such kind of function
gt; formula before. Thanks.
gt;
gt;
gt; Charles
gt;
gt;
gt; quot;Dominicquot; wrote:
gt;
gt; gt; or, in case the first name has a space in it (bobby sue, ray allen, john
gt; gt; paul, etc.)
gt; gt;
gt; gt; =IF(ISNUMBER(FIND(quot; quot;,B2)),RIGHT(B2,2),quot;quot;)
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =if(ISNUMBER(FIND(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Charlesquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi All:
gt; gt; gt; gt;
gt; gt; gt; gt; I have a list of names stored in MS Excel. Last name is in first column
gt; gt; gt; but
gt; gt; gt; gt; first name and middle initial are in second column. The file looks like:
gt; gt; gt; gt;
gt; gt; gt; gt; Last name First Name
gt; gt; gt; gt; West Joe
gt; gt; gt; gt; Hellygard David J.
gt; gt; gt; gt; Huang Nancy H.
gt; gt; gt; gt;
gt; gt; gt; gt; Now I want to separate first name and middle initial and move middle
gt; gt; gt; initial
gt; gt; gt; gt; to third column. The problem is that the length of first name is not
gt; gt; gt; same.
gt; gt; gt; gt; Definitely I cannot use Text to Column. Any help or suggestions are very
gt; gt; gt; gt; appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Charles
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

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(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)

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)

quot;Dominicquot; gt; wrote in message
news
gt; Charles,
gt;
gt; I'm sure Bob can give you a much better solution and more eloquent
gt; explanation, but here are my two cents.
gt;
gt; The formula:
gt;
gt; =IF(AND(ISNUMBER(FIND(quot; quot;,B2)),ISNUMBER(FIND(quot;.quot;,B2))),RIGHT(B2,2),quot;quot;)
gt;
gt; Looks for a space and a period in cell B2. If it finds both a space and
gt; period, it then returns the RIGHT-MOST 2 characters in cell B2. If it does
gt; not find a space and a period, it will return a blank. This should work
gt; assuming that all middle initials in your data have a period and are one
gt; letter. If not, it might need to be tweaked a bit.
gt;
gt; To use this, insert a new column next to your first name column (in the
gt; formula, the first name column is assumed to be quot;Bquot;). Type the formula in
B2
gt; (assumed to be the first row of data) and copy down for all the rows you
have
gt; data in.
gt;
gt; Does that work?
gt;
gt; HTH
gt;
gt;
gt; quot;Charlesquot; wrote:
gt;
gt; gt; Hi Bob:
gt; gt;
gt; gt; Thank you very much. I think this formua should be more useful. But
could
gt; gt; you tell me how I can use it. I have never used such kind of function
gt; gt; formula before. Thanks.
gt; gt;
gt; gt;
gt; gt; Charles
gt; gt;
gt; gt;
gt; gt; quot;Dominicquot; wrote:
gt; gt;
gt; gt; gt; or, in case the first name has a space in it (bobby sue, ray allen,
john
gt; gt; gt; paul, etc.)
gt; gt; gt;
gt; gt; gt; =IF(ISNUMBER(FIND(quot; quot;,B2)),RIGHT(B2,2),quot;quot;)
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =if(ISNUMBER(FIND(quot; quot;,B2)),MID(B2,FIND(quot; quot;,B2) 1,99),quot;quot;)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Charlesquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi All:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a list of names stored in MS Excel. Last name is in first
column
gt; gt; gt; gt; but
gt; gt; gt; gt; gt; first name and middle initial are in second column. The file looks
like:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Last name First Name
gt; gt; gt; gt; gt; West Joe
gt; gt; gt; gt; gt; Hellygard David J.
gt; gt; gt; gt; gt; Huang Nancy H.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Now I want to separate first name and middle initial and move
middle
gt; gt; gt; gt; initial
gt; gt; gt; gt; gt; to third column. The problem is that the length of first name is
not
gt; gt; gt; gt; same.
gt; gt; gt; gt; gt; Definitely I cannot use Text to Column. Any help or suggestions
are very
gt; gt; gt; gt; gt; appreciated.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Charles
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;