"David (Normandy)" wrote in message
...

"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.


David in that column, there will only be numbers, no text and the 0 will be
operational at the beginning of the number, i.e. 067. Take that the column
will have for example 087, 001, 093, 43, 23, 77, etc, I feel that as there
is no text I will not have a problem .............. I hope :-))

Your comments 'before' I plough tooooooooo much in would be appreciated,
ALSO, what tests would you recommend to 'sort' my numbers given that Col 1
will be text, col 2 will be text, col 3 will be the numbers ..... as text to
pick up the 0, and the rest text as in names etc.? I would wish to produce
tables/lists in different sequences of Col 1, then col 3 numbers then other
cols as text OR dates.

Massive amount of info to be fed in and I don't wish to have to go through
it all again :-))

(I think I may have bitten off more than I can chew. Maybe that is why the
book has never been published!!!!)

Kind regards

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 in message
...
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?


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

I think you've missed the point. It looks like Mike wants to insert text
that look like numbers, for example telephone numbers. They all go into the
one column. However telephone numbers aren't 'numbers' they are text, so
either the column should be formatted as such or he would need to put a
single apostrophe before every single entry to keep them consistent. Mixing
numbers and text in the same column is a bad idea.

David.

"®óñ© © ²°¹°-°³" wrote in message
...
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?


Hopefully none :-)

Text will be in one column, followed by a column of numbers which include
this wretched 0 at the beginning of 'some' numbers, then other columns with
text.

Thanks for your help and interest ;-)

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'" wrote in message
...


"David (Normandy)" wrote in message
...

"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.


David in that column, there will only be numbers, no text and the 0 will
be operational at the beginning of the number, i.e. 067. Take that the
column will have for example 087, 001, 093, 43, 23, 77, etc, I feel that
as there is no text I will not have a problem .............. I hope :-))

Your comments 'before' I plough tooooooooo much in would be appreciated,
ALSO, what tests would you recommend to 'sort' my numbers given that Col 1
will be text, col 2 will be text, col 3 will be the numbers ..... as text
to pick up the 0, and the rest text as in names etc.? I would wish to
produce tables/lists in different sequences of Col 1, then col 3 numbers
then other cols as text OR dates.

Massive amount of info to be fed in and I don't wish to have to go through
it all again :-))

(I think I may have bitten off more than I can chew. Maybe that is why the
book has never been published!!!!)

Kind regards

Mike

Mike, if you want to display 'numbers' that have leading zeros then they are
not actually numbers they are text. Numbers are something you can do
calculations on - add up, multiply etc. I don't think you want to do any
calculations with these?

1. Select the entire column (click the column letter at the top to highlight
the entire column).
2. Click Format Cells
3. Click the Number tab
4. Click the category 'text'

Job done. You can type the numbers in without having to type a single quote
in front of each one.

David.

"David (Normandy)" wrote in message
...

"'Mike'" wrote in message
...


"David (Normandy)" wrote in message
...

"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.


David in that column, there will only be numbers, no text and the 0 will
be operational at the beginning of the number, i.e. 067. Take that the
column will have for example 087, 001, 093, 43, 23, 77, etc, I feel that
as there is no text I will not have a problem .............. I hope :-))

Your comments 'before' I plough tooooooooo much in would be appreciated,
ALSO, what tests would you recommend to 'sort' my numbers given that Col
1 will be text, col 2 will be text, col 3 will be the numbers ..... as
text to pick up the 0, and the rest text as in names etc.? I would wish
to produce tables/lists in different sequences of Col 1, then col 3
numbers then other cols as text OR dates.

Massive amount of info to be fed in and I don't wish to have to go
through it all again :-))

(I think I may have bitten off more than I can chew. Maybe that is why
the book has never been published!!!!)

Kind regards

Mike

Mike, if you want to display 'numbers' that have leading zeros then they
are not actually numbers they are text. Numbers are something you can do
calculations on - add up, multiply etc. I don't think you want to do any
calculations with these?

1. Select the entire column (click the column letter at the top to
highlight the entire column).
2. Click Format Cells
3. Click the Number tab
4. Click the category 'text'

Job done. You can type the numbers in without having to type a single
quote in front of each one.

David.

No calculations David. Just numbers which have to be put in numerical order.
:-))

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

place an apostrophe before the zero


"'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

"'Mike'" wrote in message
...



"David (Normandy)" wrote in message
...

"'Mike'" wrote in message
...


"David (Normandy)" wrote in message
...

"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.


David in that column, there will only be numbers, no text and the 0 will
be operational at the beginning of the number, i.e. 067. Take that the
column will have for example 087, 001, 093, 43, 23, 77, etc, I feel that
as there is no text I will not have a problem .............. I hope :-))

Your comments 'before' I plough tooooooooo much in would be appreciated,
ALSO, what tests would you recommend to 'sort' my numbers given that Col
1 will be text, col 2 will be text, col 3 will be the numbers ..... as
text to pick up the 0, and the rest text as in names etc.? I would wish
to produce tables/lists in different sequences of Col 1, then col 3
numbers then other cols as text OR dates.

Massive amount of info to be fed in and I don't wish to have to go
through it all again :-))

(I think I may have bitten off more than I can chew. Maybe that is why
the book has never been published!!!!)

Kind regards

Mike

Mike, if you want to display 'numbers' that have leading zeros then they
are not actually numbers they are text. Numbers are something you can do
calculations on - add up, multiply etc. I don't think you want to do any
calculations with these?

1. Select the entire column (click the column letter at the top to
highlight the entire column).
2. Click Format Cells
3. Click the Number tab
4. Click the category 'text'

Job done. You can type the numbers in without having to type a single
quote in front of each one.

David.

No calculations David. Just numbers which have to be put in numerical
order. :-))

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



Mike,

Thi gets back to what I said at 09:50, comming from Church before i got into
agricultural mode, and a lot of water has gone undre the bridge since then.

Your own response to Adrian corssed mine in the ether, o it may have been
missed;

If you dont want to do any numerical operations, you would be better off
with a table in Word, but it appears you need to sort these, which is a
numerical operation, and so needs numbers.

You can format numbers all to have the same string length before the decimal
point, that is have leading zeros, as i aid before by:

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.

You can probably put blank between the groups of three didgit as well, in
the custom format window, i suggest you look at the help files.

There i an Excel newsgroup, i havent looked at it for years, but it is
probably way above what you are trying to achieve here.

I hope your RNEBA reunion goes well!

John

"'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






Robert Harvey has it right. Highlight the column that you wish to
format, then select 'Format Cells' and then 'custom'. Then select the
first in the list and change it in the little window under 'Type:' to
000 or 0000 or whatever, and there you are. Numbers will display with
as many leading zeros as are not otherwise occupied, and you can
perform the usual maths or sort functions on them. No need for
apostrophes or text formatting or whatever. The format you've just
created will also appear as a new format in the custom list for future
use.


--
Chris

E-mail: christopher[dot]hogg[at]virgin[dot]net

"Chris Hogg" wrote in message
...
"'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






Robert Harvey has it right. Highlight the column that you wish to
format, then select 'Format Cells' and then 'custom'. Then select the
first in the list and change it in the little window under 'Type:' to
000 or 0000 or whatever, and there you are. Numbers will display with
as many leading zeros as are not otherwise occupied, and you can
perform the usual maths or sort functions on them. No need for
apostrophes or text formatting or whatever. The format you've just
created will also appear as a new format in the custom list for future
use.


--
Chris

I have just tried that Chris and when I move to the next cell the 0 drops
off :-((

Seems the format, cells, text is the best one to use. I just highlight the
complete column before I start and away I go. It will only be one column of
5 or 6 (at the moment) but there will be 1000's of entries of which I would
guess only 2% will lead with a 0, but they HAVE to be sortable with the rest
of the columns when I do change column priority :-)

There will be no mathematical calculations

Many thanks everyone

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'" 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

To keep the data as numerical, you can make a special format:

1) Select the column

2) Click Format - Cells

3) Choose Custom

4) Put 0### in the field 'Type' (the number of #'s will be the amount of
numbers you want after the 0)

5) Presto :~))

HTH Jenny

"JennyC" wrote in message
...
"'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

To keep the data as numerical, you can make a special format:

1) Select the column

2) Click Format - Cells

3) Choose Custom

4) Put 0### in the field 'Type' (the number of #'s will be the amount of
numbers you want after the 0)

5) Presto :~))

HTH Jenny



Thanks Jenny.

The number of #'s will change, does it matter? Looking at the data so far, I
'think' it will be 01, 02, to 09 then 10, 11, and on, BUT it might go to 099
and possibly even 0999. Other numbers will be conventional without an 0 in
front up to four numbers such as 1009.

The problem is going to lie in sorting these with data in previous and later
columns which will be alphabetical sorts but taking the numbers with them.

Does that make sense???????

For example:

L 099 Jenny
B 321 Mike
A 888 Keith

Sort col one and the letters will reverse to ABL A to the top with 888 Keith
Sort col three and A goes under L 099 and Jenny in line 2

See what I mean? ALL columns must sort.

At a rough guess, 6000 entries in about 8 columns into three different sorts
and printed :-(

As I said somewhere else, I think have bitten off more than I can chew and
this is why the book has never been published ;-))

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'" wrote in message
...


"JennyC" wrote in message
...
"'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

To keep the data as numerical, you can make a special format:

1) Select the column

2) Click Format - Cells

3) Choose Custom

4) Put 0### in the field 'Type' (the number of #'s will be the amount of
numbers you want after the 0)

5) Presto :~))

HTH Jenny



Thanks Jenny.

The number of #'s will change, does it matter? Looking at the data so far,
I 'think' it will be 01, 02, to 09 then 10, 11, and on, BUT it might go to
099 and possibly even 0999. Other numbers will be conventional without an
0 in front up to four numbers such as 1009.

No problem - add as many #'s as you like. It wil only show the actual number
of figures you have
example 0####### wil show 01 up to and includind 09999999

The problem is going to lie in sorting these with data in previous and
later columns which will be alphabetical sorts but taking the numbers with
them.

Does that make sense???????

For example:

L 099 Jenny
B 321 Mike
A 888 Keith

Sort col one and the letters will reverse to ABL A to the top with 888
Keith
Sort col three and A goes under L 099 and Jenny in line 2

See what I mean? ALL columns must sort.

You can sort more columns at one time.
1) Select ALL the columns in the file
2) Click Data - Sort
3) There you will be able to sort one or more columns as required


At a rough guess, 6000 entries in about 8 columns into three different
sorts and printed :-(

As I said somewhere else, I think have bitten off more than I can chew
and this is why the book has never been published ;-))
Mike

Whats the book about then ?
Jenny

In reply to 'Mike' ) who wrote this in
, I, Marvo, say :

Shouldn't your email address read 3s&6d?

:-)

"Uncle Marvo" wrote in message
...
In reply to 'Mike' ) who wrote this in
, I, Marvo, say :

Shouldn't your email address read 3s&6d?

:-)



My, you are a youngster then :-))

3d & 6d mean nothing to you? Woolies mean nothing to you? ;-)

Innocence of youth ;-)

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

In reply to 'Mike' ) who wrote this in
, I, Marvo, say :

"Uncle Marvo" wrote in message
...
In reply to 'Mike' ) who wrote this in
, I, Marvo, say :

Shouldn't your email address read 3s&6d?

:-)



My, you are a youngster then :-))

I wish.

3d & 6d mean nothing to you? Woolies mean nothing to you? ;-)

3/6d does. I thought Woolies was a penny, once :-)

Innocence of youth ;-)

I wish.