Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula to seperate values in cell. (2003)

    I cannot work out to do a formula so that the names are moved to a separate cell to the right, any help would be appreciated.
    See screenshot
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formula to seperate values in cell. (2003)

    Assuming that the name always follows the first space:
    <code>=RIGHT(A1,LEN(A1)-FIND(" ",A1))</code>

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula to seperate values in cell. (2003)

    Thatís great thanks, how do l do the same with the site code (9 digits long), l tried but no luck, see screenshot.
    Attached Images Attached Images

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formula to seperate values in cell. (2003)

    As it is always 9 digits long you can use the following.
    <code>=LEFT(A1,9)</code>

    The first formula for the names can also be simplified if the name always starts at character 11.
    <code>=RIGHT(A1,LEN(A1)-10)</code>

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula to seperate values in cell. (2003)

    Many thanks for your help, thatís great!

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula to seperate values in cell. (2003)

    If you would like to separate the data into three separate columns as shown in the images below, then you can do the following:

    1- Select column A (the column containing the data), and make sure the next two columns are empty (insert new columns if they are not).
    2- Select "Text to Columns" from the data menu.
    3- In the resulting dialog box, put a tic mark next to Delimited. Click on Next.
    4- In the dresulting dialog box, put a check next to Space and Tab. Click on Finish

    Your data should now be split as shown in the second image below.
    Attached Images Attached Images
    Legare Coleman

Posting Permissions

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