+ Reply to Thread
Results 1 to 4 of 4

Wrongly formatted Excel generates endless stack in a .txt file

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    Planet Earth
    MS-Off Ver
    2016
    Posts
    96

    Wrongly formatted Excel generates endless stack in a .txt file

    Hello

    I have an Excel file (Please find attached). But due to wrong formatting over thousands of values it builds up a big stack (endless) as it associates multiple different models and their information with that keyword.

    When I use the VBA to export the columns into a .txt file, it targets column B, C, D. It should do the trick, however a keyword is messing everything up, stacking the information that should be associated with the car model. (please see example.txt) to understand what it currently does when exporting the data into the .txt as an output.

    In principle, it just 'steals' the information from the associated car model, as there are multiple entries of this "New" keyword which get associated hence it builds up and 'steals' from other car models.

    At first I thought of finding and replacing the "New" keyword with the actual car model, but as there are many variations with thousands of entries, I guess it's not that doable manually.

    I was thinking of a VBA to repair the Excel formatting such as:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    Not sure about how to fix this, or even if it is fixable, as there are hundreds if not thousands of variations of model with their information'stolen' by this keyword...

    Any help would be greatly appreciated
    Thanks!
    Attached Files Attached Files
    Last edited by Gordon85; 08-05-2020 at 05:21 PM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Wrongly formatted Excel generates endless stack in a .txt file

    Is this how you wanted?
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Wrongly formatted Excel generates endless stack in a .txt file

    An alternative solution is with Power Query/Get and Transform found on the Data Tab

    MCode

    Please Login or Register  to view this content.
    Review PQ
    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps

    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    01-30-2020
    Location
    Planet Earth
    MS-Off Ver
    2016
    Posts
    96

    Re: Wrongly formatted Excel generates endless stack in a .txt file

    Thank you both, much appreciated! Both methods work as desired!
    Rep+

    Solved
    Last edited by Gordon85; 08-06-2020 at 12:30 AM.

+ 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. [SOLVED] Excel wrongly treats A and A* as duplicate
    By amsin21 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2020, 04:53 AM
  2. Reformatting a Wrongly Formatted Time field to a decimal (i.e. 8:30 PM to 8.3 hours)
    By angelwalter1214 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-25-2017, 11:54 AM
  3. Convert a CSV file into a formatted Excel file
    By douglasvrs in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-08-2009, 05:09 PM
  4. Excel XP file goes into endless loop in Excel 2003
    By insomniac_ut in forum Excel General
    Replies: 4
    Last Post: 02-01-2007, 03:06 AM
  5. [SOLVED] Convert CSV File Into Formatted Excel Document
    By Andy in forum Excel General
    Replies: 3
    Last Post: 04-28-2006, 03:15 PM
  6. Excel 2000 file when opened in Excel XP generates errors
    By Gert Unterhofer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2006, 06:10 AM
  7. [SOLVED] save excel file formatted as f12.3
    By inquirer in forum Excel General
    Replies: 2
    Last Post: 06-13-2005, 08:05 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