Как предотвратить изменение последнего числа на ноль в Excel?
Почему Excel рассматривает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст
Я пытаюсь обработать некоторые данные в Excel. Эти данные включают цифровые номера счетов и другие длинные числовые строки (например, идентификаторы мобильных телефонов). Я не выполняю никаких математических операций над этими строками, поэтому хочу, чтобы Excel воспринимал их как обычный текст.
Вот что выводит меня из себя (это Excel 2010)
- Возьмите длинное число, например 1240800388917, и вставьте его в ячейку на новом листе.
- Формат ячеек по умолчанию в Excel общий, поэтому строка представлена в научном листе как 1.2408 E+12
- Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат текста.
Ячейка всегда отображается в научной папке, даже если форматирование задано как текст.
Теперь, если выполнить эти шаги в другом порядке:
- Форматирование пустой ячейки в виде текста. Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат Текст
- Возьмите длинное число, например, 1240800388917, и вставьте его в текстовую ячейку
Теперь отображает ячейку как линию, а не как научный отчет.
Результаты, оставленные в научной нотации, даже если ячейка отформатирована как текст, кажутся мне нерабочими. Предлагаются следующие обходные пути: использовать функцию импорта CSV и установить формат как текстовый, добавить пробел в начале каждой строки чисел и т. д.
Существует ли простой хороший способ легко сохранить эти строки отформатированными в виде текста?
Почему Excel это делает?
13 ответов
Я помню, как жевал данные, когда проверял массивные числа в Excel (больше никогда). Когда вы вводите длинные числа в Excel, попробуйте сделать следующее: 12345678901234567890 (20 цифр), Excel преобразует это для вас, что означает, что 20-значное число, которое вы только что ввели, будет сокращено примерно до пятнадцати значащих цифр.
Это не округление, а усечение. (12345678901234567890 — это 1,23456789012346E+19, но Excel показывает 1,234567889012345E+19)
Это происходит в точке входа, и как только это сделано, все дальнейшие детали теряются. Поэтому, даже если вы скажете Excel, что вы действительно хотите, чтобы это был текст, а не число, вам не повезет, и ваша первая последовательность не сработает.
Я проверил это в Excel 2010. Затем щелкните правой кнопкой мыши, выберите Format Cells, Custom и выберите вариант, в котором указано 0 (второй вариант в разделе General).
Апостроф ‘ перед числом заставит Excel рассматривать число как текст (включая левое выравнивание по умолчанию). И если ошибки отмечены, они будут отображаться в виде числа, сохраненного как текст ошибки в ячейке.
Я обнаружил, что в Office 2010, если сначала отформатировать столбец, а потом вставить данные, большие числа отображаются правильно.
Попробуйте это. Это работает для меня, Excel 2013.
Где a1 содержит числовое значение. Затем скопируйте столбец и вставьте его в исходный столбец.
вы можете форматировать ячейки, как обычай > #
После того, как вы вставили данные в столбец, вы можете применить его перед сохранением таблицы.
Я много работаю со штрих-кодами (UPC), и excel по умолчанию форматирует их в научную нотацию. Это сводит меня с ума! Почему нет такой опции, я никогда не узнаю.
Excel 2010: Это работает по одному столбцу за раз, а не по одной строке за раз. Выберите столбец или подмножество столбцов, на вкладке «Данные» выберите «текст в столбцы» и прокрутите вправо до «готово». «Больше никаких научных обозначений.
Я согласен со многими пользователями Excel, что это преступление, что нет способа избежать научных обозначений!
Это старый вопрос, но именно на него я наткнулся после поиска в Google, который не ответил на мой вопрос (почему форматирование текста не работает и почему 15-значное число уменьшается после форматирования текста).
Короткий ответ: вы не можете работать с номером по своему усмотрению после ввода числового значения и нажатия клавиши Enter . После этого число будет усечено до 15 цифр и показано как индексный номер.
Это не баг, а особенность.
Все, что вы делаете после этого, будет использовать усеченное значение, поэтому никакие приемы форматирования не помогут. Однако вы можете использовать возможность преобразования экспоненциальной записи в текст (щелкните на заголовке столбца, выберите Текст в данные, затем Делимитированные, Далее, снимите флажки со всех разделителей, выберите Текст в формате данных столбца и затем Завершить), чтобы преобразовать значения в текст и сразу отобразить как 15-значное число. Вы можете иметь больше чисел, но они будут сокращены до 15 цифр, поэтому остальные будут отброшены.
для того, чтобы иметь номер в таблице ровно то, как вы набрали его в вы должны хранить его в виде текста. Таким образом, вы должны предшествовать ему апострофом или форматировать ячейки как text до ввод / копирование в него значений. Если это «иногда не работает», то, извините, вы либо делаете что-то неправильно, либо у вас есть какая-то автозамена (если вы используете апостроф и большое число > 15 цифр Excel рассматривает его как неправильное значение и помечает ячейку предупреждающим сообщением, поэтому это замечание не является личным или критикой).
Другой способ сделать это в массовых числах — импортировать значения из текстового файла. В большинстве случаев это единственное возможное решение. Если вы импортируете файл, а не просто открываете его, Excel будет изначально обрабатывать тип CSV и обрезать длинные числа до 15 цифр.
Теперь к последнему вопросу. Слишком узкие ячейки Excel будут отображать большое число в научной нотации, если вы введете его в текстовом формате. Это побочный эффект алгоритмов Excel, и он раздражает. Достаточно сделать ячейку шире, и вы всегда будете видеть полное значение.
Как предотвратить изменение последнего числа на ноль в Excel?
Как предотвратить изменение последнего числа на ноль в Excel?
В Excel, как правило, количество вводимых чисел ограничено 15 символами, если числовая строка длиннее 15 символов, и число будет отображаться как 0 с 16-го символа, как показано ниже. Эта проблема должна вызвать проблемы у многих финансовых сотрудников, потому что номера кредитных карт, которые им нужно ввести в листы, обычно длиннее 15 номеров. Здесь я расскажу о приемах, которые помогут предотвратить изменение числа на ноль в Excel.
Остановить изменение последнего числа на 0 с добавлением одинарной кавычки
Чтобы число не превратилось в ноль, перед его вводом в ячейку можно добавить одну перевернутую запятую.
Выберите пустую ячейку и введите ‘ в нее, а затем введите числа, и вы увидите, что все числа точно отображаются в ячейке. Смотрите скриншот:
Остановить изменение последнего числа на 0 с помощью форматирования ячеек
Помимо добавления одинарных кавычек друг за другом, чтобы избежать перехода числа в ноль, вы также можете отформатировать ячейки как текст перед вводом цифр.
1. Чтобы отобразить диалоговое окно Формат ячеек, нажмите Ctrl + 1 на ячейках, в которые нужно ввести числа с более чем 15 цифрами.
2. Во всплывающем Формат ячеек диалога под Число Вкладка и выберите Текст из Категория список. Смотрите скриншот:
3. Затем нажмите OK, и число больше не будет меняться на ноль после ввода чисел в выбранные ячейки.