Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Splitting apart one chunk into columns (Excel xp)

    I am getting a .csv file that has 16 dates in it. They cannot give it to me with commas between but just a space.

    This appears in B91
    07/22/02 07/01/02 07/29/02 08/05/02 08/12/02 08/19/02 08/26/02 07/29/02 09/02/02 09/09/02 09/16/02 09/23/02 09/02/02 09/30/02 10/07/02 10/14/02
    This appears in B92
    07/28/02 07/28/02 08/04/02 08/11/02 08/18/02 08/25/02 09/01/02 09/01/02 09/08/02 09/15/02 09/22/02 09/29/02 09/29/02 10/06/02 10/13/02 10/20/02

    I need to split the dates apart to appear in B91 7/22/02 - 7/28/02

    D91 07/01/02 - 07/28/02

    F91 07/29/02 - 08/04/02

    so on every other column for 16 dates. I am writing a macro to do this but need help on this piece. Any help is greatly appreciated. thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Splitting apart one chunk into columns (Excel xp)

    In D91 enter:
    =MID($B91,(COLUMN()-4)*9+1,8)&" - "&MID($B92,(COLUMN()-4)*9+1,8)

    and copy it thru the 16 columns

    Then paste-special the values

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting apart one chunk into columns (Excel xp)

    Steve's scheme is simpler, bur you said that you wanted a macro, so HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim i As Integer
    ' Parse Dates
    Range("B91:B92").TextToColumns Destination:=Range("B91"), _
    DataType:=xlDelimited, Space:=True
    ' Combine Dates
    For i = 2 To Range("B91").End(xlToRight).Column
    Columns(i).AutoFit ' Make room for date
    Cells(91, i) = "'" & Cells(91, i).Text & " - " & Cells(92, i).Text
    Cells(92, i).Clear
    Columns(i).AutoFit
    Next i
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Splitting apart one chunk into columns (Excel xp)

    My motto:
    Why create a macro, when you create a convoluted formula.
    Steve

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splitting apart one chunk into columns (Excel xp)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/nope.gif border=0 alt=nope width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting apart one chunk into columns (Excel xp)

    Because I usually put way too many IF's in it and Steve slaps my wrist! <img src=/S/tongue.gif border=0 alt=tongue width=15 height=15>

    Regards
    Peter

Posting Permissions

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