Number displayed as text difficulties

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

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. ;)
 
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.
Click to expand...

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
 
Hi Walter,

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


Regards
Claus Busch
 
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,
Click to expand...

[commenting on the suggestion of Columns(5).NumberFormat = "000"]
and you only have to enter 7 to get the result 007
Click to expand...

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.
 
Hi Walter,

Am Mon, 7 May 2012 16:42:04 +0100 schrieb Walter Briscoe:
1) Why does "'000" display as 000?
Click to expand...

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


Regards
Claus Busch
 
Back
Top