Formatting problem

M

Marc

In my excel sheet there is a General formatted column
When entering numbers in to the column it stores as numbers with aligning
right side
Then I format the entire column as text, after that all values are left
aligned
Then I entered a new value in to the column, it stores with green colour
hyphen
Previously entered values are not like that. If I edit the previously
entered value then that value also stores with green colour hyphen.
What is the reason for this and how to avoid this
Please Help
 
M

Mike H

Marc,

This is Excel telling you that you have numbers stored as text. To get rid
of the green triangle:-

Tools|Options - Error Checking tab - Un-check 'numbers stored as text'

Mike
 
M

Marc

Thanks Mike
But the problem is already there


Mike H said:
Marc,

This is Excel telling you that you have numbers stored as text. To get rid
of the green triangle:-

Tools|Options - Error Checking tab - Un-check 'numbers stored as text'

Mike
Click to expand...
 
G

Gary''s Student

To fix existing cells, selec them and run:

Sub marc()
For Each r In Selection
With r
.NumberFormat = "General"
.Value = .Value
End With
Next
End Sub
 
M

Marc

Thanks Gary
But my entire column is formatted as Text
According to your code it is formatted in to General
I want to keep the column in Text format and make every cell value in to
text
 
B

Billy Liddel

When you select a cell with a green arrow you can use the mouse to highlight
a dropdown box. Among the options are Ignore this error (numbers stored as
text). When you click ignore the arrows will disapear. save the file and when
you reopen there will be no more error messages.

Peter Atherton
 
G

Gary''s Student

A one line change!

Sub marc()
For Each r In Selection
With r
.NumberFormat = "@"
.Value = .Value
End With
Next
End Sub
 
Top