Почему Excel рассматривает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст
Я пытаюсь обработать некоторые данные в Excel. Данные включают числовые номера счетов и другие длинные числовые строки (например, идентификаторы сотовых телефонов). Я не делаю математические операции над этими строками, поэтому я хочу, чтобы Excel воспринимать их как обычный текст.
вот то, что делает меня орехи (это Excel 2010):
- возьмите длинное число, например 1240800388917, и вставьте его в ячейку на новом листе.
- Формат ячеек Excel по умолчанию общий, поэтому строка представлено в научной записи как 1.2408 E+12
- щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат текста
ячейка по-прежнему отображается в научной записи, даже если формат был установлен в текст.
теперь, если я делаю шаги в другом порядке:
- форматировать пустую ячейку как текст. Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат Text
- возьмите длинное число, например 1240800388917 и вставьте его в текстовую ячейку
теперь ячейка отображается в виде строки, а не в научной записи.
результаты, оставшиеся в научной нотации, даже если ячейка отформатирована как текст, просто кажутся мне сломанными. Я видел предлагаемые обходные пути, такие как: используйте импорт CSV и установите формат в текст, добавьте пробел в начало каждой числовой строки и другие.
есть простой хороший способ легко держать эти строки, отформатированные как текст?
Почему Excel делает это?
связанные вопросы SU, которые я нашел: как заставить Excel 2007 перестать форматировать большие числа как научную нотацию? и это поведение Excel с большим шестнадцатеричным числом ожидается?
13 ответов
Ах, воспоминания о данных жевать назад от того, когда я сделал некоторые массивные числа перекрестной проверки в Excel (никогда больше).. Когда вы пробиваете длинные числовые строки в Excel, попробуйте сказать, 12345678901234567890 (20 цифр), Excel будет вообще преобразование это для вас, что означает, что 20-значный номер, который вы только что постучали, был сокращен, чтобы быть всего около пятнадцати значащих цифр.
Примечание: преобразование является усечением, в отличие от округления. (12345678901234567890 is 1.23456789012346E+19, но excel покажет вам 1.23456789012345E+19)
Это происходит в точке входа, поэтому, как только это происходит, любая дополнительная деталь теряется, и поэтому, даже если вы сообщите Excel, что вы действительно имели в виду, что это текст, а не число, вам не повезло, и, следовательно, почему ваша первая последовательность не работает.
вы можете форматировать ячейки, как обычай > #
Это может быть применено после вставки данных в столбец, но вы должны сделать это прежде, чем вы сохраните таблицу.
Я много работаю со штрих-кодами (UPC), и excel по умолчанию форматирует их как научную нотацию. Сводит меня с ума! Почему это не вариант, который я никогда не узнаю.
Excel 2010: это работает по одному столбцу за раз, а не по строке. Выберите столбец или подмножество столбца, на вкладке Данные выберите " текст в столбцы "и перейдите вправо к "готово"."Больше никаких научных обозначений.
Я согласен со многими пользователями Excel, это преступление, что нет возможности предотвратить научную нотацию!
Я знаю о возрасте вопроса, но это тот, который я приземлился после поиска google, и который не ответил на мой вопрос (почему форматирование текста не работает и почему число сокращается до 15 цифр после форматирования текста).
короткий ответ: вы не можете работать на свой номер, как вам нравится, после того, как вы ввели числовое значение и постучал Enter
. В тот момент, когда вы это сделаете, номер будет усечен до 15 цифр и представлен в виде показательный.
это не ошибка, это функция.
все, что вы сделаете после этого, будет использовать усеченное значение, поэтому никакой трюк форматирования не поможет.
Тем не менее, вы можете использовать Text to columns
возможность преобразования экспоненциальной записи в текст (нажмите на заголовок столбца, нажмите Text to data
, потом Delimited
,Next
, снимите все флажки разделителей, выберите Text
in Column data format
а то Finish
) для преобразования значений в текст и немедленно отображается как 15-значное число. Но, это будет только 15 цифр, так что если у вас больше количество, остальные проиграл.
для того, чтобы иметь номер в таблице ровно то, как вы набрали его в вы должны хранить его в виде текста. Таким образом, вы должны предшествовать ему апострофом или форматировать ячейки как text до ввод / копирование в него значений. Если это "иногда не работает", то, извините, вы либо делаете что-то неправильно, либо у вас есть какая-то автозамена (если вы используете апостроф и большое число > 15 цифр Excel рассматривает его как неправильное значение и помечает ячейку предупреждающим сообщением, поэтому это замечание не является личным или критикой).
другой способ сделать это в массовых числах-импортировать значения из текстового файла. Я осмелюсь сказать, что это единственный способ для большинства ситуаций. И обязательно импортируйте файл, а не просто откройте его, как Excel обрабатывает csv
тип, как это родной формат и обрезает большие числа до 15 цифр, как само собой разумеющееся.
теперь, до последнего вопрос. Если ввести большое числовое значение в ячейку Excel в текстовом формате, она будет отображаться в научной нотации до тех пор, пока ячейка недостаточно широка. Это раздражает, но побочный эффект некоторых внутренних алгоритмов Excel. Просто сделайте ячейку шире, и вы будете видеть полную стоимость каждый раз.
в Excel 2013, то же самое случилось со мной, и я просто пошел форматировать ячейки -> число затем убедился, что десятичный знак установлен в "0".
копировать / вставить проблемное поле в новый лист в столбце a
копировать / вставить столбец A в текстовый файл
форматировать столбец B в текст и вставить текстовый файл в столбец B
В Столбце C:
=IF(ISNUMBER(SEARCH("+",B1)),A1,"")
преобразовать столбец C в числовой формат без десятичных знаков
копировать/вставить столбец C в текстовый файл
форматировать столбец D в текст и вставить текстовый файл в столбец D
В Столбце E:
=IF(IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2))=0,"",IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2)))
копировать/вставить столбец E в блокноте
удалить данные в поле проблема и формат столбца в текст
копировать/вставить данные из текстового файла в проблемное поле
это работало для меня в Excel 2013, включая копирование и вставку ячеек в другие программы. Фильтрация данных помогла, особенно для шагов редактирования.
- выберите нужные ячейки.
- применить числовой формат "текст". (Попробуйте Alt, H, N, затем "Text".)
затем, для каждого нужные ячейки:
- войти в режим редактирования. (Попробуйте F2.)
- подтвердите редактирование с помощью Enter (хорошо для столбцов) или Tab (хорошо для строк). Не изменяйте содержимое.
ваш номер будет отображаться нормально, даже на 100 цифр. =)
Excel расстраивает для работы CSV / массовых данных, как это. Работа с LibreOffice, если это возможно, он не делает этого, то есть, если вы откроете тот же лист (.xlsx) в Libreoffice большие "числа", которые на самом деле являются строками, Не отображаются в научной нотации (что является абсурдным решением Excel). Точность файла LibreOffice велика: откройте то же самое .xlsx снова в Excel, и научная нотация мгновенно "работает", как обычно.
Другие вопросы microsoft-excel microsoft-excel-2010 cell-format