I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a Stand

Mike.
Numbers are for purposes of calculation. If you need a leading zero showing
then I suggest your numbers are rather 'text' than numbers. In which case
format the column as "text" you can then have leading zeros and still sort
properly.

David.


"'Mike'" wrote in message
...
I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data Sort properly.

Can anyone help please?

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a
Stand

Hi Mike

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'"
wrote:

I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?

At a guess (don't have time this morning to 'play' !) -
but right-click the cell and select 'format cells' (on my very elderly
copy of Excel).

There should be an option there that will do what you want - maybe
'text' - you need to convince Excel that it deosn't need to remove the
leading zero...

Hope this helps
Adrian

Mike

"Adrian" wrote in message
...
Hi Mike

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'"
wrote:

I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data
Sort properly.

Can anyone help please?

At a guess (don't have time this morning to 'play' !) -
but right-click the cell and select 'format cells' (on my very elderly
copy of Excel).

There should be an option there that will do what you want - maybe
'text' - you need to convince Excel that it deosn't need to remove the
leading zero...

Hope this helps
Adrian

Mike

BRILLIANT!!!!

Very many thanks to both of you. It works ;-))

I have a load of data which I can now add to and write a book, the data of
which can be transposed into many different tabular ways before going to the
printers :-))

Thanks again

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a Stand

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'"
wrote:

I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?

Mike

I'm sure there is a format that allows leading zeroes but I can't
check just now. If you can't find it try the
microsoft.public.excel.misc newsgroup.

Steve

--
Steve Wolstenholme Neural Planner Software Ltd

EasyNN-plus. The easy way to build neural networks.

http://www.easynn.com

"'Mike'" wrote in message
...

"Adrian" wrote in message
...
Hi Mike

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'"
wrote:

I wish to put numbers in a cell which commence with a '0'. For example
067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data
Sort properly.

Can anyone help please?

At a guess (don't have time this morning to 'play' !) -
but right-click the cell and select 'format cells' (on my very elderly
copy of Excel).

There should be an option there that will do what you want - maybe
'text' - you need to convince Excel that it deosn't need to remove the
leading zero...

Hope this helps
Adrian

Mike

BRILLIANT!!!!

Very many thanks to both of you. It works ;-))

I have a load of data which I can now add to and write a book, the data of
which can be transposed into many different tabular ways before going to
the printers :-))

Thanks again

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a
Stand




I might be a bit late, but here i my two pennyworth.

Format cells (by right click or the menu bar), this gives a multi tabbed
window, one of the tabs is "Number", choose this.

Now scroll down to "Custom", and it the "type" put:

000

or 0000.000, or whatever you need.

Now back to my industrial scale weeding following the rain!

Cheers

John

"'Mike'" wrote in message
...

"Adrian" wrote in message
...
Hi Mike

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'"
wrote:

I wish to put numbers in a cell which commence with a '0'. For example
067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data
Sort properly.

Can anyone help please?

At a guess (don't have time this morning to 'play' !) -
but right-click the cell and select 'format cells' (on my very elderly
copy of Excel).

There should be an option there that will do what you want - maybe
'text' - you need to convince Excel that it deosn't need to remove the
leading zero...

Hope this helps
Adrian

Mike

BRILLIANT!!!!

Very many thanks to both of you. It works ;-))

I have a load of data which I can now add to and write a book, the data of
which can be transposed into many different tabular ways before going to
the printers :-))

Thanks again

Mike


As an add on to this, I have just shown Joan what you have taught me and she
is thrilled to bits as well. She has a vast collection of Gardening Books
and is Collating then, but was having trouble with the ISBN numbers. She can
now steam ahead with that project.

Thanks from Joan as well.

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a Stand

On 29/07/2007 09:24:02, "'Mike'" wrote:
I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data Sort properly.

Can anyone help please?

They will help at
microsoft.public.excel.misc

On 29 Jul, 09:24, "'Mike'" wrote:
I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?

All you need to do is create a custom format. Select the cells, then
format-cells - Number - select Custom - then (for example) type in
000000 as a format.

wrote in message
ups.com...
On 29 Jul, 09:24, "'Mike'" wrote:
I wish to put numbers in a cell which commence with a '0'. For example
067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data
Sort properly.

Can anyone help please?

All you need to do is create a custom format. Select the cells, then
format-cells - Number - select Custom - then (for example) type in
000000 as a format.


In this first instance, I have been able to highlight the complete column
then format column as text. I then went down the column changing the O's to
0's :-)) and it worked :-)

The webmaster on our site has done one way, but when I send the next load of
data to go on the site, it will be in the new format :-))

Of course the data for the book will already be in the new format :-)) and
any I add can be new :-)

Thanks everyone for the help.

Mike


--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a Stand

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'" wrote
and included this (or some of this):

I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?

'067 will show as 067.

It will however be text, not a number.


--
®óñ© © ²°¹°-°³

"®óñ© © ²°¹°-°³" wrote in message
...
On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'" wrote
and included this (or some of this):

I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data
Sort properly.

Can anyone help please?

'067 will show as 067.

It will however be text, not a number.



That's great. It sorts too :-))

Many thanks

Mike



--
The Royal Naval Electrical Branch Association.
'THE' Association if you served in the Electrical Branch of the Royal Navy
Reunion Bournemouth August/September 2007
www.rneba.org.uk
"Navy Days" Portsmouth 25th - 27th July 2008. RN Shipmates will have a Stand

®óñ© © ²°¹°-°³ wrote:

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'" wrote
and included this (or some of this):


I wish to put numbers in a cell which commence with a '0'. For example 067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not Data
Sort properly.

Can anyone help please?


'067 will show as 067.

It will however be text, not a number.

As the guy said you simply type a single apostrophe before the number
you wish to format as text. Eg '456.88. No need to bother with format menus.

"Frank Booth Snr" wrote in message
...
®óñ© © ²°¹°-°³ wrote:

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'" wrote
and included this (or some of this):


I wish to put numbers in a cell which commence with a '0'. For example
067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data Sort properly.

Can anyone help please?


'067 will show as 067.

It will however be text, not a number.

As the guy said you simply type a single apostrophe before the number you
wish to format as text. Eg '456.88. No need to bother with format menus.

Strongly disagree. The problems with just using a single apostrophe to force
numbers to be text occur when you try to sort the column. Things end up in
the wrong place as you are mixing numbers and text. It can also lead to
computational errors depending on how complex the spreadsheet is.

David.

On Sun, 29 Jul 2007 17:16:17 +0200, "David \(Normandy\)"
wrote and included this (or some of this):


"Frank Booth Snr" wrote in message
...
®óñ© © ²°¹°-°³ wrote:

On Sun, 29 Jul 2007 09:24:00 +0100, "'Mike'" wrote
and included this (or some of this):


I wish to put numbers in a cell which commence with a '0'. For example
067
but when I go to the next cell, the number reverts to just 67.

I can get over it by putting an 'O' (capital o), but this then does not
Data Sort properly.

Can anyone help please?


'067 will show as 067.

It will however be text, not a number.

As the guy said you simply type a single apostrophe before the number you
wish to format as text. Eg '456.88. No need to bother with format menus.

Strongly disagree. The problems with just using a single apostrophe to force
numbers to be text occur when you try to sort the column. Things end up in
the wrong place as you are mixing numbers and text. It can also lead to
computational errors depending on how complex the spreadsheet is.

So don't try to use numbers and text in the same column.

If Mike wants to sort a text column, he inputs a text column and
nothing else. And he keeps numbers in a separate column.

What's the problem?


--
®óñ© © ²°¹°-°³