+ Reply to Thread
Results 1 to 12 of 12

Sort by 2nd word in a cell?

  1. #1
    Curious Lady
    Guest

    Sort by 2nd word in a cell?

    If I create a data base with a name cell that includes both the first and
    last name, can I sort by the first letter of the last name?

  2. #2
    Dave Peterson
    Guest

    Re: Sort by 2nd word in a cell?

    The first thing I'd do is to use two more columns and put the first and last
    names into their own columns.

    Data|Text to columns
    seems like a good way to start.

    Then sort by those helper columns.

    Curious Lady wrote:
    >
    > If I create a data base with a name cell that includes both the first and
    > last name, can I sort by the first letter of the last name?


    --

    Dave Peterson

  3. #3
    CLR
    Guest

    Re: Sort by 2nd word in a cell?

    If you have not created the DataBase yet, then by all means use separate
    columns for the first and last names.......it's so much better in the long
    run........if you already have such a list, then it's best to separate it
    with the "TextToColumns" feature or the "MID" formula..............

    Vaya con Dios,
    Chuck, CABGx3

    "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > If I create a data base with a name cell that includes both the first and
    > last name, can I sort by the first letter of the last name?




  4. #4
    Curious Lady
    Guest

    Re: Sort by 2nd word in a cell?

    Dave,

    I already do the two columns but have difficulty transferring it into Word
    (usually creating class rosters) without using mail merge. I am not sure
    what you mean by Data|Text to columns. I will try using the help but any
    quickie advice would be much appreciated. Thanks for your time.

    "Dave Peterson" wrote:

    > The first thing I'd do is to use two more columns and put the first and last
    > names into their own columns.
    >
    > Data|Text to columns
    > seems like a good way to start.
    >
    > Then sort by those helper columns.
    >
    > Curious Lady wrote:
    > >
    > > If I create a data base with a name cell that includes both the first and
    > > last name, can I sort by the first letter of the last name?

    >
    > --
    >
    > Dave Peterson
    >


  5. #5
    Curious Lady
    Guest

    Re: Sort by 2nd word in a cell?

    We already have the data base and I understand what you are telling me to do;
    however, I am not familiar with the "TextToColumns" feature or the "MID"
    formula. Where would I find this and can you suggest a good question to ask
    Mr. Wizard Help? Thank you for your time.

    "CLR" wrote:

    > If you have not created the DataBase yet, then by all means use separate
    > columns for the first and last names.......it's so much better in the long
    > run........if you already have such a list, then it's best to separate it
    > with the "TextToColumns" feature or the "MID" formula..............
    >
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > If I create a data base with a name cell that includes both the first and
    > > last name, can I sort by the first letter of the last name?

    >
    >
    >


  6. #6
    Gord Dibben
    Guest

    Re: Sort by 2nd word in a cell?

    You could use Data>Text to columns to break the cell into two columns then
    sort on the last name.


    Gord Dibben Excel MVP

    On Thu, 23 Jun 2005 16:31:02 -0700, Curious Lady <Curious
    Lady@discussions.microsoft.com> wrote:

    >If I create a data base with a name cell that includes both the first and
    >last name, can I sort by the first letter of the last name?



  7. #7
    CLR
    Guest

    Re: Sort by 2nd word in a cell?

    Save your data and use a copy for this exercize........
    Be sure the column to the right of your column is blank, or insert a new
    one...........
    Highlight the column with the names, then do Data > TextToColumns >
    Delimited > Next > check the SPACE box > finish
    This splits the surname over to the blank column

    or

    If your data in column A, put this in B1 and copy down.........
    =MID(A1,FIND(" ",A1,1)+1,99)
    This will put the surname in column B, leaving column A intact

    Vaya con Dios,
    Chuck, CABGx3



    "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > We already have the data base and I understand what you are telling me to

    do;
    > however, I am not familiar with the "TextToColumns" feature or the "MID"
    > formula. Where would I find this and can you suggest a good question to

    ask
    > Mr. Wizard Help? Thank you for your time.
    >
    > "CLR" wrote:
    >
    > > If you have not created the DataBase yet, then by all means use separate
    > > columns for the first and last names.......it's so much better in the

    long
    > > run........if you already have such a list, then it's best to separate

    it
    > > with the "TextToColumns" feature or the "MID" formula..............
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > If I create a data base with a name cell that includes both the first

    and
    > > > last name, can I sort by the first letter of the last name?

    > >
    > >
    > >




  8. #8
    Curious Lady
    Guest

    Re: Sort by 2nd word in a cell?

    Thanks a million. I am off to try it and if works you have no idea how much
    joy you will brought to the world. :-)

    "CLR" wrote:

    > Save your data and use a copy for this exercize........
    > Be sure the column to the right of your column is blank, or insert a new
    > one...........
    > Highlight the column with the names, then do Data > TextToColumns >
    > Delimited > Next > check the SPACE box > finish
    > This splits the surname over to the blank column
    >
    > or
    >
    > If your data in column A, put this in B1 and copy down.........
    > =MID(A1,FIND(" ",A1,1)+1,99)
    > This will put the surname in column B, leaving column A intact
    >
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    > news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > > We already have the data base and I understand what you are telling me to

    > do;
    > > however, I am not familiar with the "TextToColumns" feature or the "MID"
    > > formula. Where would I find this and can you suggest a good question to

    > ask
    > > Mr. Wizard Help? Thank you for your time.
    > >
    > > "CLR" wrote:
    > >
    > > > If you have not created the DataBase yet, then by all means use separate
    > > > columns for the first and last names.......it's so much better in the

    > long
    > > > run........if you already have such a list, then it's best to separate

    > it
    > > > with the "TextToColumns" feature or the "MID" formula..............
    > > >
    > > > Vaya con Dios,
    > > > Chuck, CABGx3
    > > >
    > > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > > If I create a data base with a name cell that includes both the first

    > and
    > > > > last name, can I sort by the first letter of the last name?
    > > >
    > > >
    > > >

    >
    >
    >


  9. #9
    Curious Lady
    Guest

    Re: Sort by 2nd word in a cell?

    It worked very well. Thank you. Can I then do the reverse action? If I
    have 2 columns, first name, last name, can I merge them into a column that
    contains the names together? Thank you again for your time.

    "CLR" wrote:

    > Save your data and use a copy for this exercize........
    > Be sure the column to the right of your column is blank, or insert a new
    > one...........
    > Highlight the column with the names, then do Data > TextToColumns >
    > Delimited > Next > check the SPACE box > finish
    > This splits the surname over to the blank column
    >
    > or
    >
    > If your data in column A, put this in B1 and copy down.........
    > =MID(A1,FIND(" ",A1,1)+1,99)
    > This will put the surname in column B, leaving column A intact
    >
    > Vaya con Dios,
    > Chuck, CABGx3
    >
    >
    >
    > "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    > news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > > We already have the data base and I understand what you are telling me to

    > do;
    > > however, I am not familiar with the "TextToColumns" feature or the "MID"
    > > formula. Where would I find this and can you suggest a good question to

    > ask
    > > Mr. Wizard Help? Thank you for your time.
    > >
    > > "CLR" wrote:
    > >
    > > > If you have not created the DataBase yet, then by all means use separate
    > > > columns for the first and last names.......it's so much better in the

    > long
    > > > run........if you already have such a list, then it's best to separate

    > it
    > > > with the "TextToColumns" feature or the "MID" formula..............
    > > >
    > > > Vaya con Dios,
    > > > Chuck, CABGx3
    > > >
    > > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > > If I create a data base with a name cell that includes both the first

    > and
    > > > > last name, can I sort by the first letter of the last name?
    > > >
    > > >
    > > >

    >
    >
    >


  10. #10
    Dave Peterson
    Guest

    Re: Sort by 2nd word in a cell?

    =A1&" "&B1

    or =B1&", "&A1

    just a note. It's usually lots easier to concatenate stuff (put it together)
    than to separate fields.

    Dr. James Throckmorton Smythe-Davis, III

    would be difficult to separate.

    Curious Lady wrote:
    >
    > It worked very well. Thank you. Can I then do the reverse action? If I
    > have 2 columns, first name, last name, can I merge them into a column that
    > contains the names together? Thank you again for your time.
    >
    > "CLR" wrote:
    >
    > > Save your data and use a copy for this exercize........
    > > Be sure the column to the right of your column is blank, or insert a new
    > > one...........
    > > Highlight the column with the names, then do Data > TextToColumns >
    > > Delimited > Next > check the SPACE box > finish
    > > This splits the surname over to the blank column
    > >
    > > or
    > >
    > > If your data in column A, put this in B1 and copy down.........
    > > =MID(A1,FIND(" ",A1,1)+1,99)
    > > This will put the surname in column B, leaving column A intact
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    > > news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > > > We already have the data base and I understand what you are telling me to

    > > do;
    > > > however, I am not familiar with the "TextToColumns" feature or the "MID"
    > > > formula. Where would I find this and can you suggest a good question to

    > > ask
    > > > Mr. Wizard Help? Thank you for your time.
    > > >
    > > > "CLR" wrote:
    > > >
    > > > > If you have not created the DataBase yet, then by all means use separate
    > > > > columns for the first and last names.......it's so much better in the

    > > long
    > > > > run........if you already have such a list, then it's best to separate

    > > it
    > > > > with the "TextToColumns" feature or the "MID" formula..............
    > > > >
    > > > > Vaya con Dios,
    > > > > Chuck, CABGx3
    > > > >
    > > > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > > > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > > > If I create a data base with a name cell that includes both the first

    > > and
    > > > > > last name, can I sort by the first letter of the last name?
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >


    --

    Dave Peterson

  11. #11
    CLR
    Guest

    Re: Sort by 2nd word in a cell?

    You're very welcome...........yes, it can be reversed........ the process is
    called CONCATENATION...........
    Assuming Firstname in Column A and Lastname in B, put one of these in
    C1.......
    =A1&" "&B1 will result in Firstname Lastname
    =B1&", "&A1 will result in Lastname, Firstname

    Vaya con Dios,
    Chuck, CABGx3


    "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    news:1D7A65DD-EE50-4753-983A-002A3AC6C9C3@microsoft.com...
    > It worked very well. Thank you. Can I then do the reverse action? If I
    > have 2 columns, first name, last name, can I merge them into a column that
    > contains the names together? Thank you again for your time.
    >
    > "CLR" wrote:
    >
    > > Save your data and use a copy for this exercize........
    > > Be sure the column to the right of your column is blank, or insert a new
    > > one...........
    > > Highlight the column with the names, then do Data > TextToColumns >
    > > Delimited > Next > check the SPACE box > finish
    > > This splits the surname over to the blank column
    > >
    > > or
    > >
    > > If your data in column A, put this in B1 and copy down.........
    > > =MID(A1,FIND(" ",A1,1)+1,99)
    > > This will put the surname in column B, leaving column A intact
    > >
    > > Vaya con Dios,
    > > Chuck, CABGx3
    > >
    > >
    > >
    > > "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    > > news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > > > We already have the data base and I understand what you are telling me

    to
    > > do;
    > > > however, I am not familiar with the "TextToColumns" feature or the

    "MID"
    > > > formula. Where would I find this and can you suggest a good question

    to
    > > ask
    > > > Mr. Wizard Help? Thank you for your time.
    > > >
    > > > "CLR" wrote:
    > > >
    > > > > If you have not created the DataBase yet, then by all means use

    separate
    > > > > columns for the first and last names.......it's so much better in

    the
    > > long
    > > > > run........if you already have such a list, then it's best to

    separate
    > > it
    > > > > with the "TextToColumns" feature or the "MID" formula..............
    > > > >
    > > > > Vaya con Dios,
    > > > > Chuck, CABGx3
    > > > >
    > > > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in

    message
    > > > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > > > If I create a data base with a name cell that includes both the

    first
    > > and
    > > > > > last name, can I sort by the first letter of the last name?
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  12. #12
    Curious Lady
    Guest

    Re: Sort by 2nd word in a cell?

    =A9&" "&B9&" "&D9&" "&E9
    And again it worked. Wonder what you do to know this. The above will
    separate the Dr.'s name. It took me a while to get the formula in
    correctly, not sure why, but then you can just use the fill. Interesting.
    Thanks again.

    "Dave Peterson" wrote:

    > =A1&" "&B1
    >
    > or =B1&", "&A1
    >
    > just a note. It's usually lots easier to concatenate stuff (put it together)
    > than to separate fields.
    >
    > Dr. James Throckmorton Smythe-Davis, III
    >
    > would be difficult to separate.
    >
    > Curious Lady wrote:
    > >
    > > It worked very well. Thank you. Can I then do the reverse action? If I
    > > have 2 columns, first name, last name, can I merge them into a column that
    > > contains the names together? Thank you again for your time.
    > >
    > > "CLR" wrote:
    > >
    > > > Save your data and use a copy for this exercize........
    > > > Be sure the column to the right of your column is blank, or insert a new
    > > > one...........
    > > > Highlight the column with the names, then do Data > TextToColumns >
    > > > Delimited > Next > check the SPACE box > finish
    > > > This splits the surname over to the blank column
    > > >
    > > > or
    > > >
    > > > If your data in column A, put this in B1 and copy down.........
    > > > =MID(A1,FIND(" ",A1,1)+1,99)
    > > > This will put the surname in column B, leaving column A intact
    > > >
    > > > Vaya con Dios,
    > > > Chuck, CABGx3
    > > >
    > > >
    > > >
    > > > "Curious Lady" <CuriousLady@discussions.microsoft.com> wrote in message
    > > > news:08965146-88F5-41E6-B7EB-93823593007C@microsoft.com...
    > > > > We already have the data base and I understand what you are telling me to
    > > > do;
    > > > > however, I am not familiar with the "TextToColumns" feature or the "MID"
    > > > > formula. Where would I find this and can you suggest a good question to
    > > > ask
    > > > > Mr. Wizard Help? Thank you for your time.
    > > > >
    > > > > "CLR" wrote:
    > > > >
    > > > > > If you have not created the DataBase yet, then by all means use separate
    > > > > > columns for the first and last names.......it's so much better in the
    > > > long
    > > > > > run........if you already have such a list, then it's best to separate
    > > > it
    > > > > > with the "TextToColumns" feature or the "MID" formula..............
    > > > > >
    > > > > > Vaya con Dios,
    > > > > > Chuck, CABGx3
    > > > > >
    > > > > > "Curious Lady" <Curious Lady@discussions.microsoft.com> wrote in message
    > > > > > news:74348498-6961-4C79-9DA9-338AFCF858E6@microsoft.com...
    > > > > > > If I create a data base with a name cell that includes both the first
    > > > and
    > > > > > > last name, can I sort by the first letter of the last name?
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1