Number stored as text excel как исправить для столбца

Преобразование чисел-как-текст в нормальные числа

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:

Причем иногда такой индикатор не появляется (что гораздо хуже).

В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells), меняете формат на Числовой (Number), жмете ОК — и ничего не происходит! Совсем!

Возможно, «это не баг, а фича», конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию — один из них вам обязательно поможет.

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number):

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили  зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

По-сути, мы выполняем то же самое, что и в прошлом способе — умножение содержимого ячеек на единицу — но не формулами, а напрямую из буфера.

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data). На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next), а на третьем воспользуйтесь кнопкой Дополнительно (Advanced). Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Sub Convert_Text_to_Numbers()
    Selection.NumberFormat = "General"
    Selection.Value = Selection.Value
End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) — и моментально преобразовать псевдочисла в полноценные.

Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.

P.S.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

• Деление слипшегося текста по столбцам
• Вычисления без формул специальной вставкой
• Преобразование текста в числа с помощью надстройки PLEX

Игнорировать множественные ошибки «число сохранено как текст» одновременно

Ответы:

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your LOOKUP and MATCH functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge, excel takes a lot of time to push through; occasionally, it is so slow that you can see the cells getting updated one by one.

A quick way to deal with this is to create a temporary column and use the Value function. You may get #Value errors if the string does not represent a numeric value. I use the following formula to do the trick:

=IFERROR(A1*1,TRIM(A1))

This converts all the numbers to “numbers”, trims the text and takes care of the #Value errors if any. You can Copy-Paste-Special-Values over the existing column, and remove the temporary column if you want. This technique works if you have only the Unique Identifier column interfering with your lookups. Imagine your entire database imported into excel  as text (with that annoying singe quote in front), you’d be surprised how often that happens. I bet you would not have the patience to set up temporary columns for every field, would you?

I ran into this problem very often, so I buckled down and started thinking of ways to automatically clean up the columns. My first instinct was to deal with the problem one cell at a time. I created a user defined function that cleans up one cell, and then ran a For Each loop through all the cells in a range. This method was extremely slow, even slower than the built-in Convert To Number function. Then I figured using Arrays would speed things up immensely.  And that is when I realized something amazing; a single line solved this entire problem:

Selection.Value = Selection.Value

This got the job done in a flash.  It automatically got rid all the Convert To Number errors. Who knew excel was already programmed to automatically do this right? If only Microsoft thought to give users the ability to call it at will. However, note that this does not trim cells, and it does not handle dates well. Also, this wont work if you set the Cell’s Number Format to Text.

I ironed out the issues and wrote a sub that trims string, handles dates well and changes Case (Lower/Upper/Proper if you need it to).

Sub AutoTrim(Optional ByRef WhichRange As Range, _
    Optional ByVal ChangeCase As Boolean = False, _
    Optional ByVal ChangeCaseOption As VbStrConv = vbProperCase)

'Declare Sub Level Variables and Objects
Dim MessageAnswer As VbMsgBoxResult
Dim EachRange As Range
Dim TempArray As Variant
Dim RowCounter As Long
Dim ColCounter As Long

'Use the Active Selection if the user did not pass a Range Object to the
'WhichRange Argument
If WhichRange Is Nothing Then
    Set WhichRange = Application.Selection
End If

'If the Range has formulas, it will be converted into values
'Therefore, ask user for permission to proceed
If RangeHasFormulas(WhichRange) Then
    MessageAnswer = MsgBox("Some of the cells contain formulas. " _
        & "Would you like to proceed?", _
        vbQuestion + vbYesNo, "Struggling To Excel")
    If MessageAnswer = vbNo Then Exit Sub
End If

'Loop through each area, So we can loop through all the _
'rectangular boxes
For Each EachRange In WhichRange.Areas
    TempArray = EachRange.Value2
    'If Each range were a single cell, then EachRange.Value
    'would not be an array. Consequently, we have to deal with both
    'these situations separately
    If IsArray(TempArray) Then
        For RowCounter = LBound(TempArray, 1) To UBound(TempArray, 1)
            For ColCounter = LBound(TempArray, 2) To UBound(TempArray, 2)
                'First Check if it is a date
                'Excel Confuses mm/dd/yyyy and dd/mm/yyyy when we just use the
                'Selection.Value = Selection.Value technique. Copy Paste Special
                'Multiply by 1, also suffers from this problem.
                If IsDate(TempArray(RowCounter, ColCounter)) Then
                    TempArray(RowCounter, ColCounter) = _
                        CDate(TempArray(RowCounter, ColCounter))
                Else
                    'Check if it is a number
                    If IsNumeric(TempArray(RowCounter, ColCounter)) Then
                        'Convert it into a Double Variable if it is a number
                        TempArray(RowCounter, ColCounter) = _
                            CDbl(TempArray(RowCounter, ColCounter))
                    Else
                        'Otherwise it is Text. Trim it. I am using Excel's trim
                        'function because it clears double spaces also.
                        TempArray(RowCounter, ColCounter) = _
                            Application.Trim(TempArray(RowCounter, ColCounter))
                        'Finally, Change Case if the user wants to
                        If ChangeCase Then
                            TempArray(RowCounter, ColCounter) = StrConv( _
                            TempArray(RowCounter, ColCounter), ChangeCaseOption)
                        End If
                    End If
                End If
                
            Next ColCounter
        Next RowCounter
    Else
        'Deal with Single Cells separately
        If IsDate(TempArray) Then
            TempArray = CDate(TempArray)
        Else
            If IsNumeric(TempArray) Then
                TempArray = CDbl(TempArray)
            Else
                TempArray = Application.Trim(TempArray)
                If ChangeCase Then
                    TempArray = StrConv(TempArray, ChangeCaseOption)
                End If
            End If
        End If
    End If
    EachRange.Value2 = TempArray
Next EachRange

End Sub

All you need to do now is select your entire Database, just run this macro, sit back and relax.

Update

• Another popular solution to this problem is to just type “1” in any cell; copy it; and paste-special-value it with the Multiply operator. While this handles Numbers like a charm, it fails to convert dates properly.
• I heard complaints that my code did not handle numbers with commas and decimal points. Consequently I amended the code.
• The macro now uses the following logic:
  • First Check if the string is a date. If it is, apply the CDate function.
  • Else, check if it the string is Numeric, if It is, apply the CDbl function. This function takes care of the numbers with commas and decimal points.
  • If the string is neither a date, nor a number, it must be a string. Therefore, Trim it with Excel’s trim function. I chose to use Excel’s trim function, because it also removes double spaces.

Download

Let me know how you like it. I added a bunch of cover macros in there that illustrates how this sub could be used. There is a sheet named JustValues that has a very crude sample data set. I have copy pasted it in the Test sheet for you to try the cover macros.

I have not tried this macro on a system that uses the m/d/yyyy date system, do let me know if it doesn’t work on your computer. Also if you have dates in the d/m/yy format, you would benefit from reading this article.

Bonus

I had to write a small supporting function that determines if any of the cells in a range have formulas in it. You may need to use something like this in your applications.

Function RangeHasFormulas(ByRef WhichRange As Range) _
    As Boolean

'Declare Function Level Variables
Dim TempVar As Variant

'initialize Variables
TempVar = WhichRange.HasFormula

'Check if the Variant Variable is Null (This avoids an error)
If IsNull(TempVar) Then
    'If Null, some cells have Formulas
    RangeHasFormulas = True
Else
    If TempVar = True Then
        'If True, all cells have Formulas
        RangeHasFormulas = True
    Else
        'If False, none of the cells have formulas
        RangeHasFormulas = False
    End If
End If

End Function

If the input column is already formatted as text before data entry, that is not the source of difficulty. You say that is so, so moving on…

If all you do is look up a value using the input values, and the values being searched have also been formatted as text before their own entry, Excel will do this. Period.

There is a failure point available here: Absolutely all the data entry in both columns must occur AFTER the columns are set as text.

Unfortunately, if you ever force Excel to regard them as numbers after they are properly (see above) entered, you’ve ruined it. So, for example, if you want to be «rigorous» and clean your data for the searching by making sure it all has the same number of characters or what-have-you, and do that by using, say, TEXT() to apply an exact format to the values, you are forcing them through the 15 significant digits mill and will see them truncated as you do. That’s just one way they can be forced to be regarded as numbers without you really thinking about it. (For example, making sure all have 17 digits by using, say, TEXT(A1,"00000000000000000"), so as to avoid frustrations that come from certain ways bar code readers present their output (ways that are chosen by users, mind you, or present because they are not selected away, not failure on the readers’ parts).

Moving on, I will cite the fact that you say adding deinitive non-numrical text makes things come out all right. Yes, because Excel is no longer treating them as numbers. That suggests something, a «monkey in the middle» that is taking properly formatted and entered data and treating it as a number before your lookups are performed.

So we come to the likely point of failure here: (quoting from your questions)

«However I also formatted for duplicates in that column to avoid errors of duplicate entries.»

Dude… just what does that mean? Whatever in the world does it include? For example, there simply isn’t ANY «formatting for duplicates» functionality in Excel. So this suggests, pretty strongly, you are NOT looking up in the stored data directly, and/or not using the bar code reader entered data, DIRECTLY. It suggests that you have helper columns that have a version of one or more of these columns undergoing IF() testing to «remove» duplicates by presenting blanks («») in the helper column.

VERY suggestive that the problem is these formulas treat the entries as numbers at some point rather than always treating them as text, start to finish. And…

Even more of a problem…

Those helper columns cannot be formatted as text or their formulas can’t work. So even if you never treat either the entered data or the stored lookup data as numbers in this duplicate hunt, the results of the formulas are beign treated as numbers!

So their outputs are numbers.

Hence the truncation.

I’d give all that a strong look-see.