Mataupu
If the text format was set for any cells on the sheet (this could be done by the user or the program when uploading data to Excel), then the numbers entered later into these cells Excel starts to consider as text. Sometimes such cells are marked with a green indicator, which you have most likely seen:
And sometimes such an indicator does not appear (which is much worse).
In general, the appearance of numbers-as-text in your data usually leads to a lot of very unfortunate consequences:
- sorting stops working normally – “pseudo-numbers” are squeezed out, and are not arranged in order as expected:
- type functions VLOOKUP (VLOOKUP) do not find the required values, because for them the number and the same number-as-text are different:
- when filtering, pseudo-numbers are selected erroneously
- many other Excel functions also stop working properly:
- ma isi tulaga faapena
It is especially funny that the natural desire to simply change the cell format to numeric does not help. Those. you literally select cells, right click on them, select Fa'asologa o sela (Foma'i Selula), change the format to Numera (numera), squeeze OK — and nothing happens! At all!
Perhaps, “this is not a bug, but a feature”, of course, but this does not make it easier for us. So let’s look at several ways to fix the situation – one of them will definitely help you.
Method 1. Green indicator corner
If you see a green indicator corner on a cell with a number in text format, then consider yourself lucky. You can simply select all cells with data and click on the pop-up yellow icon with an exclamation mark, and then select the command Liu i Numera (Convert to number):
All numbers in the selected range will be converted to full numbers.
If there are no green corners at all, then check if they are turned off in your Excel settings (File – Options – Formulas – Numbers formatted as text or preceded by an apostrophe).
Method 2: Re-entry
If there are not many cells, then you can change their format to numeric, and then re-enter the data so that the format change takes effect. The easiest way to do this is by standing on the cell and pressing the keys in sequence F2 (enter edit mode, the cell starts blinking cursor) and then ulu. Also instead of F2 you can simply double-click on the cell with the left mouse button.
It goes without saying that if there are a lot of cells, then this method, of course, will not work.
Metotia 3. Fuafuaga
You can quickly convert pseudo-numbers to normal ones if you make an additional column with an elementary formula next to the data:
Double minus, in this case, means, in fact, multiplying by -1 twice. A minus by a minus will give a plus and the value in the cell will not change, but the very fact of performing a mathematical operation switches the data format to the numerical one we need.
Of course, instead of multiplying by 1, you can use any other harmless mathematical operation: division by 1 or adding and subtracting zero. The effect will be the same.
Metotia 4: Fa'apipi'i Fa'apitoa
This method was used in older versions of Excel, when modern effective managers went under the table there was no green indicator corner yet in principle (it appeared only in 2003). The algorithm is this:
- enter 1 in any empty cell
- kopi ai
- select cells with numbers in text format and change their format to numeric (nothing will happen)
- right-click on cells with pseudo-numbers and select command Fa'apipi'i fa'apitoa (Pa'i Fa'apitoa) po'o le fa'aoga o le keyboard shortcut Ctrl + Alt V
- in the window that opens, select the option O tulaga faatauaina (Fa'ataua) и Faʻatele (Faatele)
In fact, we do the same thing as in the previous method – multiplying the contents of the cells by one – but not with formulas, but directly from the buffer.
Metotia 5. Tusi i koluma
If the pseudonumbers to be converted are also written with incorrect decimal or thousands separators, another approach can be used. Select the source range with data and click the button Tusi i koluma (Text to columns) tab Faʻamatalaga (Aso). In fact, this tool is designed to divide sticky text into columns, but, in this case, we use it for a different purpose.
Skip the first two steps by clicking on the button sosoo ai (Sosi), and on the third, use the button Faʻaopoopoga (Avanoa). A dialog box will open where you can set the separator characters currently available in our text:
A maeʻa kiliki faʻauma Excel will convert our text to normal numbers.
Metotia 6. Macro
If you have to do such transformations often, then it makes sense to automate this process with a simple macro. Press Alt+F11 or open a tab atinae (Atiae) Ma kiliki le Autu vaaia. In the editor window that appears, add a new module through the menu Fa'aofi - Module ma kopi le code lea iina:
Sub Convert_Text_to_Numbers() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub
Now after selecting the range, you can always open the tab Atina'e - Macros (Fa'atupu - Macros), select our macro in the list, press the button momoʻe (Tamoe) – and instantly convert pseudo-numbers into full-fledged ones.
You can also add this macro to your personal macro book for later use in any file.
PS
The same story happens with dates. Some dates may also be recognized by Excel as text, so grouping and sorting will not work. The solutions are the same as for numbers, only the format must be replaced with a date-time instead of a numeric one.
- Dividing sticky text into columns
- Calculations without formulas by special pasting
- Convert text to numbers with the PLEX add-on