Как предотвратить изменение последнего числа на ноль в Excel?

Почему Excel рассматривает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст

Я пытаюсь обработать некоторые данные в Excel. Эти данные включают цифровые номера счетов и другие длинные числовые строки (например, идентификаторы мобильных телефонов). Я не выполняю никаких математических операций над этими строками, поэтому хочу, чтобы Excel воспринимал их как обычный текст.

Вот что выводит меня из себя (это Excel 2010)

1. Возьмите длинное число, например 1240800388917, и вставьте его в ячейку на новом листе.
2. Формат ячеек по умолчанию в Excel общий, поэтому строка представлена в научном листе как 1.2408 E+12
3. Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат текста.

Ячейка всегда отображается в научной папке, даже если форматирование задано как текст.

Теперь, если выполнить эти шаги в другом порядке:

1. Форматирование пустой ячейки в виде текста. Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат Текст
2. Возьмите длинное число, например, 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, и число больше не будет меняться на ноль после ввода чисел в выбранные ячейки.