Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 6th 12, 03:03 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 5
Default Keeping Leading Zeros; How Please ?

---- I apologize if this is a duplicate posting; having problems with
my Postings via eternal-September ----

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., 0430 which would be 4:30 AM

All the manipulation which I want to do with the GMT time drops any
leading zeros.

e.g. 0030 would become just 30

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.

Much thanks,
Bob

  #2   Report Post  
Old January 7th 12, 04:02 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 143
Default Keeping Leading Zeros; How Please ?

On Jan 6, 10:03*am, Robert11 wrote:
---- I apologize if this is a duplicate posting; having problems with
my Postings via eternal-September ----

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., 0430 *which would be 4:30 AM

All the manipulation which I want to do with the GMT time drops any
leading zeros.

e.g. 0030 would become just 30

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.

Much thanks,
Bob


Format cells.... Number Custom 0000
  #3   Report Post  
Old January 7th 12, 06:15 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default Keeping Leading Zeros; How Please ?

"Robert11" wrote:
I have some time data in a column.
It's in GMT, and looks like, e.g., 0430 which would
be 4:30 AM
All the manipulation which I want to do with the GMT
time drops any leading zeros.

[....]
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.


If that were your only problem, you could simply format your other columns
or cells, the results of your "manipulations", as Special Zip Code as well.

Of course, formatting as Custom 0000 would be better. That's really all
Special Zip Code is. (Well, it is Custom 00000.)

But note that when you enter time in the form 0430, Excel does __not__
recognize that as 4:30 (hh:mm). Consequently, if you simply add 0531, the
result is __not__ 1001 as you might expect.

If you are aware of that issue and you already take care of it with your
"manipulations", fine.

Otherwise, it might be better to convert the column of original data as
Excel time. You can choose to continue to display it without a colon by
using the format Custom [hh]mm.

To convert the original data, create a parallel column with formulas of the
form =--(INT(A1/100)&":"&MOD(A1,100)) formatted as Time or as Custom [hh]mm.

The double negative causes Excel to interpret the normally text result as
numeric time.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
leading zeros Dave[_11_] New Users to Excel 5 July 25th 09 08:44 AM
How to copy a number into a text cell, keeping leading zeros? Basher Bates Charts and Charting in Excel 2 February 23rd 08 03:26 PM
Keeping leading zeros in imported data (one digit month as 05) exito816 Excel Discussion (Misc queries) 2 September 28th 07 10:35 PM
Keeping leading 0's CLR Excel Discussion (Misc queries) 3 March 3rd 06 10:45 PM
save text field w/ leading zeros in .csv format & not lose zeros? Ques Excel Discussion (Misc queries) 1 May 4th 05 06:21 PM


All times are GMT +1. The time now is 05:40 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017