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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks