How the range will sort if you treat numbers stored as text as text
Numbers stored as numbers are sorted first, and then numbers stored as text. For example, after sorting in ascending order, the range would appear that you have two sets of numbers. Also numbers stored as text sort left to right, character by character. For example 11 appears before 5 because 1 is before 5 in the first character.
Data | Data type |
---|---|
1 | Number |
10 | Number |
11 | Number stored as text |
5 | Number stored as text |
How the range will sort if you treat numbers stored as text as numbers
Both numbers stored as text and numbers are sorted together. For example, after sorting in ascending order the range would appear to have one set of numbers.
Data | Data type |
---|---|
1 | Number |
5 | Number stored as text |
10 | Number |
11 | Number stored as text |
Convert numbers stored as text to numbers
Note Some accounting programs display negative values with the negative sign (–) to the right of the value. To convert the text strings to values, you must return all of the characters of the text string except the rightmost character (the negation sign), and then multiply the result by –1. For example, if the value in cell A2 is "156–" the following formula converts the text to the value –156.
Data | Formula |
---|---|
156- | =LEFT(A2,LEN(A2)-1)*-1 |