+ Reply to Thread
Results 1 to 6 of 6

Delimited Text-to-Columns in a Macro

  1. #1
    Registered User
    Join Date
    09-28-2017
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Delimited Text-to-Columns in a Macro

    Hello,
    I have a reporting tool where all data sets produce dates as general. I need to manually change these for pivot etc.

    I am looking for a cure all macro to run as reports may have more than one date column and it will be in a different position on each report.
    I want to find all "dates" and reformat them so I can use them for grouping in pivots.

    I think my issue is in the destination range section however any input or alternate methods would be gratefully received.

    My current VBA -

    Please Login or Register  to view this content.
    Thanks
    Last edited by Jollyoli; 09-28-2017 at 09:08 AM.

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delimited Text-to-Columns in a Macro

    Maybe:

    A different approach?

    Please Login or Register  to view this content.
    Please read Forum Rules No. 3. When posting code to the Forum use code tags.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Delimited Text-to-Columns in a Macro

    Hi jollyoli

    Welcome to the Forum.
    In future please view the forum rules before posting. Your post does not comply with rule #3.
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    09-28-2017
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Re: Delimited Text-to-Columns in a Macro

    Hi John,
    I gave this a go and not to sound like an ingrate however formatting does not cut it.
    The only way to get the output into a form that allows me to do grouping in pivots is through the Text to Columns function.
    Thanks for your efforts.

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delimited Text-to-Columns in a Macro

    Can you attach a file with some sample data? Preferably with a sheet showing "BEFORE" and one showing "AFTER or RESULTS"?

  6. #6
    Registered User
    Join Date
    09-28-2017
    Location
    Edinburgh
    MS-Off Ver
    2010
    Posts
    3

    Re: Delimited Text-to-Columns in a Macro

    Find example attached.
    'Raw Export' is direct feed from report.
    'Pivot Error' is screen shot of what happens when I try to group a pivot on date from the 'Raw Export' data.
    'Data Fixed' is after doing text to columns.
    'Grouped Pivot' is an example of a working grouped pivot from the 'Data Fixed' data.

    To add annoyance the Raw Export data recognises dates enough to use date related formulas however my end user group all use pivots to analyse their data sets.

    Thank you.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Delimited Text To Columns Utlizing a User Form Text Box Can't Manually Enter Data
    By simpdogg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2016, 03:58 PM
  2. [SOLVED] Importing delimited text into columns vice rows
    By Barchetta in forum Excel General
    Replies: 7
    Last Post: 07-16-2014, 07:27 PM
  3. Text to columns Delimited
    By melx333 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2014, 07:46 AM
  4. Text to columns fixed width and delimited
    By bradvh0023 in forum Excel General
    Replies: 27
    Last Post: 05-17-2011, 02:02 PM
  5. Space delimited text to multiple columns
    By mogdog1981 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-09-2010, 08:55 AM
  6. [SOLVED] Text to columns delimited by carriage return
    By EMG03 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2005, 04:05 PM
  7. Text to Columns in excel for delimited files - allow trimming opt.
    By D. Young in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-25-2005, 01:06 PM

Tags for this Thread

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