Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Break apart field (2k)

    Hi,

    Is there a way that I can split some exported data into two fields?
    The data per cell is in the format of:

    Simon Williams (14/5/1986)
    Alan Wilson (18/2/1965)

    The Dates are always in brackets.

    I'd like to display the text in one column, and the date in another (without the brackets)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Break apart field (2k)

    Say that the data are in column A, starting in A2.
    In B2, enter the formula
    <code>
    =TRIM(LEFT(A2,FIND("(",A2)-1))
    </code>
    In C2, enter the formula
    <code>
    =DATEVALUE(MID(A2,FIND("(",A2)+1,LEN(A2)-FIND("(",A2)-1))
    </code>
    You'll probably see a number such as 31456 in C2; if so, format C2 as a date.
    Select B2:C2, and fill down as far as needed.

    Note: you can, of course, use other columns instead.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break apart field (2k)

    Thanks, that works a treat!
    My only other issue is some of the dates are displayed as (10/4/2006-15/4/2006). Currently VALUE# is displayed as an error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Break apart field (2k)

    Change the second formula to
    <code>
    =MID(A2,FIND("(",A2)+1,LEN(A2)-FIND("(",A2)-1)
    </code>
    The value will now be a text value, not a real date.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break apart field (2k)

    As normal, thank you very much!

Posting Permissions

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