Number displayed as text difficulties
 

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. ;)
--
Walter Briscoe

Number displayed as text difficulties
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Number displayed as text difficulties
 

Hi Walter,

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Number displayed as text difficulties
 

In message 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.
--
Walter Briscoe

Number displayed as text difficulties
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2