+ Reply to Thread
Results 1 to 7 of 7

Fromatted column from a formula not being copied and leading zeros (never ending issue)

  1. #1
    Registered User
    Join Date
    12-06-2013
    Location
    La Jolla, California
    MS-Off Ver
    Excel 365 & 2010
    Posts
    27

    Fromatted column from a formula not being copied and leading zeros (never ending issue)

    I import a text file via VBA to a sheet called "TextFile". There are headings in row 1.
    Columns A-G have imported data with the exception of column D which is blank.

    I have another sheet called "Reformatted" (no headings) into which I want TextFile!An:Cn then a reformatted column E.
    I do a VLOOKUP on the Master sheet to change what is in column E from the TextFile to either a 10, OT, or DT and put the results in column L of the TextFile.

    The L column in the TextFile is correct with OT, a text 10, or a DT depending on the VLOOKUP. But, when I copy that column from the TextFile sheet to the Reformatted sheet I get all 0s in the E column. That's my first issue.

    The never ending leading issue that I sometimes resolve but it's a mystery how it was resolved as my VBA is only FAIR to AWFUL.
    I want the result "Reformatted" sheet to have leading 0s in column F and retained when I export the "Reformatted" sheet to a text file result.
    I'm not getting the "Reformatted" sheet to show leading zeros, so the resulting output doesn't either.

    Thanks for any help in advance.

    If anyone has the patience for this...the third issue is that column A in the TextFile is TEXT. I comes into the sheet with double quotes around it. Therefore, when it's output as a text file, Excel add more quotes and now I have """" before and after the text in column A. I either want one set of " or NONE. Phew!

    Please Login or Register  to view this content.
    Last edited by krweaver; 03-07-2018 at 11:42 PM.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    Try

    Please Login or Register  to view this content.
    Kind regards
    Leo

  3. #3
    Registered User
    Join Date
    12-06-2013
    Location
    La Jolla, California
    MS-Off Ver
    Excel 365 & 2010
    Posts
    27

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    Thank you for your prompt reply. Unfortunately, that didn't do the trick.
    In this post, I've attached the INPUT file I'm using and the result of your macro. As you can see from the output, all of the quotes are still there and the leading zeros are not there and there's also an extra 1st row...

    and I have, for some reason, all of these other lines with just commas in them (how that happened I have no clue).

    For some reason I cannot attach a file. So, here are the images:

    LexPlumbInput3062018.txt

    Reformatted_LexPlumbInput3062018.txt
    Last edited by krweaver; 03-08-2018 at 11:27 AM.

  4. #4
    Registered User
    Join Date
    12-06-2013
    Location
    La Jolla, California
    MS-Off Ver
    Excel 365 & 2010
    Posts
    27

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    This is the macro that saves the sheet as a text file:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-06-2013
    Location
    La Jolla, California
    MS-Off Ver
    Excel 365 & 2010
    Posts
    27

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    Well, simplistically, I deleted the first row and now have only to deal with all the quotes in the 1st field (column) of the output and why there are all those rows with commas.

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

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    hard to say without the excel file

    Please Login or Register  to view this content.
    give me good result

    Kind regards
    Leo
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-06-2013
    Location
    La Jolla, California
    MS-Off Ver
    Excel 365 & 2010
    Posts
    27

    Re: Fromatted column from a formula not being copied and leading zeros (never ending issue

    GREAT. Works as hoped for. Needed one minor adjustment for the L column, but it's PERFECT. Thank you !!!

+ 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] powery query issue keeping leading zeros
    By JEAN1972 in forum Excel General
    Replies: 2
    Last Post: 11-25-2017, 01:38 AM
  2. [SOLVED] Losing leading zeros when copied to another sheet
    By robbo1172 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-13-2017, 06:30 PM
  3. [SOLVED] Formatting cells to have leading and ending zeros
    By karstens in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2014, 03:10 PM
  4. Add leading zeros in same column numbers and text
    By rpinxt in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-29-2014, 04:41 PM
  5. split column into two columns and keep leading zeros
    By BuzzMicron in forum Excel General
    Replies: 5
    Last Post: 12-29-2011, 07:05 PM
  6. Filter column with cells with leading zeros
    By erktp in forum Excel General
    Replies: 2
    Last Post: 08-24-2011, 02:21 AM
  7. [SOLVED] How do you remove leading zeros from a column in an Access databas
    By Wes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2006, 05:10 PM

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