Number displayed as text difficulties

[Walter Briscoe]

I am using Excel 2003 on Windows Vista
I have data like "205", "444", "012", "007", "000" which I want to
display in a column.
By default, Excel displays them right-justified as 205, 444, 12, 7, 0.
I want them right-justified as 205, 444, 012, 007, 000.
I use
With Columns(5)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
End With

Each cell is marked with a green triangle at the top left corner, which
I understand means "number defined as text".
I want to eliminate those triangles.

Prefixing each value with a quote works. e.g. write "000" as "'000".
I found this advised, but have no reference to a relevant help entry.
I prefer not to alter the data

Unchecking "Tools\Options\Number stored as text" is more than I want.

I saw Range("A1").Errors(xlNumberAsText).Ignore = True somewhere.
Columns(5).Errors(xlNumberAsText).Ignore = True gets a 1004 error.

For Each X In Range("E1", "E" & Cells.SpecialCells(xlCellTypeLastCell).Row)
X.Errors(xlNumberAsText).Ignore = True
Next
works, but seems unreasonable.

Errors seems to apply only to a single cell range. Why?

I had no solution when I started writing this. ;(
I now need understanding.

 

[Claus Busch]

Hi Walter,

Am Mon, 7 May 2012 10:49:40 +0100 schrieb Walter Briscoe:

I am using Excel 2003 on Windows Vista
I have data like "205", "444", "012", "007", "000" which I want to
display in a column.
By default, Excel displays them right-justified as 205, 444, 12, 7, 0.
I want them right-justified as 205, 444, 012, 007, 000.

use custom number format "000"(without the quotes)
Then your values are numbers that you can calculate with. Alignment is
right and you have no errors


Regards
Claus Busch

 

[Claus Busch]

Hi Walter,

and you only have to enter 7 to get the result 007


Regards
Claus Busch

 

[Walter Briscoe]

In message <[email protected]> of Mon, 7 May 2012 13:08:09
in microsoft.public.excel.programming, Claus Busch <claus_busch@t-
online.de> writes

Hi Walter,

[commenting on the suggestion of Columns(5).NumberFormat = "000"]

and you only have to enter 7 to get the result 007

Danke Claus,
I am not comfortable with that as my data is naturally 3 digit strings.
It does however seem to be the easiest way of handling it.
I will go with it and leave the following questions unanswered:

1) Why does "'000" display as 000?
2) Why is Range("A1").Errors.Item(xlNumberAsText).Value = True OK, but
Columns(5).Errors.Item(xlNumberAsText).Value = True gets 1004?

I do not understand NumberFormat as well as I ought.
I find help on it obscure.

 

[Claus Busch]

Hi Walter,

Am Mon, 7 May 2012 16:42:04 +0100 schrieb Walter Briscoe:

1) Why does "'000" display as 000?

to 1)
because numberformat "000" has 3 significant digits


Regards
Claus Busch