+ Reply to Thread
Results 1 to 7 of 7

Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    41

    Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Hello,

    This is really driving me crazy; have tried just about everything I can think of.
    Using Excel 2007.

    I have some Time data in a column.
    It's in GMT, and looks like, e.g., 1430 which would be 2:30 PM

    All the manipulation which I want to do with the GMT time drops any leading zeros. I want them to be preserved, of course, as there
    must always be 4 digits for GMT to be meaningful.

    e.g. 0030 would become just 30. Not good.

    Have tried formatting the GMT column as number, general, and text; nothing works.
    Doesn't seem to be any GMT format in Special or Custom.

    How can I tell Excel to keep all the leading zeros ?

    BTW: not to complicate my question any, but the column of GMT data came from a spreadsheet that is not mine.
    The column was formatted as a zip code. My guess is that the creator also had problems with leading zeros, and found, possibly,
    that this was the only format that allowed
    him to show the GMT. My problem is trying to do some manipulation with the column, and some simple math on it, and still have it show the leading zeros.

    Some of what I want to do is to merge/append two columns; one with start time, and the other with end time.
    I tried using: Cell 1 & " " & Cell 2 as an example, but it also drops the leading zeros when I try this.

    I just want to create a third normal size cell column with, e.g., 0030 1200.
    I don't want both values in a Larger cell, likes the Merge function seems to do.
    Any hints on this specific application would be most appreciated also.

    Much thanks, really appreciate the help,
    Bob

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Try this...
    With
    A1: 30
    B1: 123

    1) Use a custom number format for your cells of 0000
    CTRL+1...to open the formatting window
    ...Category: custom
    ...Type: 0000
    ...Click: OK

    Now, A1 will display 0030 and B1 will display 0123

    2) This formula will put both formatted "times" in one cell
    Please Login or Register  to view this content.
    With the above data, the cell will display:
    0030 0123

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    03-28-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    41

    From OP: Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Hi,

    Just a quick thanks.
    Exactly what I was looking for.
    Much appreciated.

    Best regards,
    Bob

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Always glad to help a fellow Bostonian.

  5. #5
    Registered User
    Join Date
    03-28-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    41

    For Ron: Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Hi Ron,

    Thanks for help; much appreciated.

    Tried out the formula you suggested, and no luck.

    I can via the Custom format change the times to the regular 4 digit GMT format O.K.
    But, when i enter the formula in the bar, it (the formula) appears in the cell rather
    than the result.

    Also, when I save it in .csv format, the 4 digits are not preserved, and it reverts back.

    I think the problem is probably not your formula per se, but the saving in .csv is messing everything
    up. But it is a .csv formatted file that I need.

    Might you if you get a chance bang a few GMT three digit values in, do a Custom 0000 on them, and then try out your formula; you'll probably see what I mean.
    Also, the Saving it as a .csv problem, and preserving the modified 4 digit GMT's.

    Or, as usual, it's something I am doing wrong. If so, I'll blame it on old age, which I've found is a great excuse for most everything.

    Thanks again,
    Bob

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: For Ron: Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Excel has no native way to communicate to a CSV file that you want your numbers to have leading zeros.
    Here's one approach to consider:
    • Format the GMT cells as TEXT and actully input the leading zeros
    Please Login or Register  to view this content.
    • This will be the formula:
    Please Login or Register  to view this content.
    And the end result in the CSV file (when viewed in notepad) will be:
    Please Login or Register  to view this content.
    Note: If you open the CSV file in Excel, it will "helpfully" remove the leading zeros from the first 2 fields.
    If that's an issue let us know.

  7. #7
    Registered User
    Join Date
    03-28-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    41

    For Ron: Re: Leading Zeros: Keeping Of. And, Re Appending Two Columns Of Data

    Hi Ron:

    Just another quick thanks.Nice of you to help, and much appreciated.

    The .csv apparently louses everything up.

    But, will work on it, with your suggestions.

    Bob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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