leading numbers
I have a cell where different numbers are always entered. Most of the time, this field contains a leading 0. With a leading 0, the text is left-aligned, but with any other leading number, the text is right-aligned. I have a formula where this cell should be compared to another cell. However, this no longer works when the text is right-aligned. Why is that?
I have also noticed that there is a ' when the text contains a leading 0.
Answers
-
The ' means the string will be treated as text rather than a number. This means:
1) The leading zero is not stripped out as it would be if this were a number.
2) The cell is left-aligned (as it is text) not right-aligned (as if it were a number).
3) You cannot do math on the number with the leading 0 (as it is text).
If you need to compare a number that is a number with one that is text you can convert the text one to a number using the VALUE function. However, this will strip the leading zeros.
-
I
Is it possible to convert all cells in this column into text?
So that a new entry is always automatically formatted into text.
The problem is that there are many people working in this sheet by writing numbers into that column,
which are not aware that a different format can lead to problems.
But the cell information should always automatically be text format because in another sheet it compares the text value to anither column with only text values.
-
Hi @Sabrain
You cannot specify that a column is text not numbers.
A workaround is to make an extra column and enter a formula in it to take the values in the first column and convert them to text. You co this by adding nothing to your number using this formula:
=[Column of Numbers]@row + ""
You can make the formula a column formula so that is is applied to all rows in that column and then lock and hide it so it doesn't confuse anyone or get overwritten.
Then, use the "Numbers converted to text" column for your comparisons.
I hope that works for you.
-
Thank you! That was very helpfull
-
No problem @Sabrain , glad I could help.
