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

259086
Michael Levy

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

Вот что сводит меня с ума (это Excel 2010):

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

Ячейка по-прежнему отображается в научной нотации, хотя формат текста был установлен.

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

  1. Отформатируйте пустую ячейку как текст. Щелкните правой кнопкой мыши по ячейке, выберите «Формат ячеек» и установите формат «Текст».
  2. Возьмите длинное число, например 1240800388917, и вставьте его в ячейку с форматированным текстом.

Теперь ячейка отображается в виде строки, а не в научной записи.

Результаты, оставшиеся в научной нотации, даже если ячейка отформатирована как текст, кажутся мне неработающими. Я видел предложенные обходные пути, такие как: использовать импорт CSV и установить формат текста, добавить символ пробела в начало каждой числовой строки и другие.

Есть ли простой способ обойти эти строки в формате текста?

С какой стати Excel это делает?

Связанные SU вопросы, которые я нашел: Как вы можете сделать так, чтобы Excel 2007 прекратил форматировать большие числа в качестве научной записи? и ожидается ли это поведение Excel с большим шестнадцатеричным числом?

96
Длинный номер `1240800388917` скопирован из другой ячейки? Или просто текст, как из Блокнота? wilson 12 лет назад 0

13 ответов на вопрос

137
Anonymous

Это работает для меня в Excel 2010. Просто выберите столбец, щелкните правой кнопкой мыши, Форматировать ячейки, Пользовательский и выберите параметр, который говорит 0 (второй вариант ниже общего).

Это намного лучше, чем принятый ответ, который гласит: «Вам не повезло»! У меня был файл CSV, в котором была эта проблема, и решение для пользовательского форматирования работало отлично. davidair 9 лет назад 19
должен быть принят ответ, кто-то должен / может измениться? qwertzman 8 лет назад 4
Это все еще кажется усеченным в моем тесте. Используя Excel 2013, я ввел следующее в ячейку 12345678901234567890. Когда я нажимаю клавишу ввода, это отображается как 1.23457E + 19, а базовое значение изменяется на 12345678901234500000, поэтому происходит потеря данных. Это не отвечает на оригинальный вопрос. Michael Levy 8 лет назад 9
Проблема в том, что ваши цифры не отформатированы как текст, а как число, когда вы пытаетесь это сделать. И это открывает множество других предостережений. Joris Meys 8 лет назад 0
Here I am a year later revisiting the same answer and wishing I could upvote it again. billynoah 7 лет назад 1
Этот ответ исправляет форматирование, но не сохраняет введенные данные. Вот почему я не могу принять это как правильный ответ. Я разместил короткое видео, чтобы показать. Когда вы следуете этим указаниям, а затем вводите длинное целое число, например 12345678901234567890, вы теряете точность при обработке его как числового, даже если оно отображается в ненаучной записи. Excel обрабатывает его как значение 12345678901234500000. Только обрабатывая его как текст, вы можете сохранить все цифры. Смотрите https://youtu.be/7EDcR7TQpYs Michael Levy 5 лет назад 2
19
tanantish

Ах, воспоминания о данных, возвращающихся с того времени, когда я проводил массовую перекрестную проверку чисел в Excel (больше никогда). Когда вы вводите длинные числовые строки в Excel, попробуйте, скажем, 12345678901234567890 (20 цифр), Excel обычно преобразует их для вас. Это означает, что число из 20 цифр, которое вы только что набрали, было сокращено до примерно пятнадцати значащих цифр.

Также обратите внимание: преобразование является усечением, а не округлением. (12345678901234567890 - 1,2345678901234 6 E + 19, но в Excel появится 1,2345678901234 5 E + 19)

Это происходит в точке входа, поэтому, как только она будет введена, любые дополнительные детали будут потеряны, и поэтому даже если вы скажете Excel, что на самом деле имели в виду текст, а не число, вам отчасти не повезло, и, следовательно, почему Ваша первая последовательность не работает.

Да, я вижу такое поведение, когда моя числовая строка имеет более 15 значащих цифр. 12345678901234567890 становится 1.23456789012345E + 19, как вы говорите. Когда я щелкаю по ячейке, в окне редактирования отображается строка, усеченная и дополненная нулями как 12345678901234500000. Странно то, что она делает то же самое для чисел, которые не превышают 15-значный предел Excel. Если я вставлю 12345678901234, он отобразится как 1.23457E + 13, но когда я щелкну по нему, в окне редактирования все еще будет отображаться исходная строка. Что странно, если я отформатирую эту ячейку в текст, она все еще отображается в научной записи. Раздражает. Michael Levy 12 лет назад 0
Это на самом деле немного странно - установка формата с общего на текст не сразу отобразит его как исходную строку, но если вы войдете и отредактируете данные (как, например, просто нажмите в зону редактирования и нажмите ) это будет обновляться, чтобы быть именно тем, что вы положили в. tanantish 12 лет назад 6
Если я щелкну по ячейке, то увижу, что в Excel есть все число, но независимо от того, сделаю ли я все ячейки текстом, он все равно захочет использовать научную запись. Так расстраивает! The Muffin Man 10 лет назад 0
Быстрый взлом: установите форматирование в соответствующих столбцах, скопируйте вставку в блокнот, затем в Excel. Brian 9 лет назад 11
Я решил эту проблему, установив формат ячейки «Текст» * перед * вставкой данных в Excel (аналогично тому, что @Brian сказал в другом комментарии). Derek Kurth 6 лет назад 0
Это НЕ ДОЛЖНО БЫТЬ ПРИНЯТОМ ОТВЕТА *: см. Следующий ниже. javadba 6 лет назад 6
Итак, вы вспомнили и повторили проблему. Теперь, как вы это исправите? user3932000 5 лет назад 0
12
dav

Один апостроф перед числом заставит Excel воспринимать число как текст (включая выравнивание по левому краю по умолчанию). И если у вас есть ошибки помечены, он будет отображаться как число, сохраненное как текстовая ошибка в ячейке.

Это не всегда так. Excel иногда хранил мои данные даже с апострофом. Cerin 9 лет назад 0
Конечно, справедливость - никогда не стоит недооценивать способность Excel (или Microsoft) делать что-то совершенно необъяснимое или повторяемое. Однако апострофный трюк обычно работает так, как рекламируется. dav 9 лет назад 0
ты можешь сделать это навалом? У меня есть данные с 60000 ячеек, которые я хочу преобразовать в текст. vaibhavcool20 6 лет назад 0
10
M Akin

Я обнаружил, что в Office 2010, если сначала отформатировать столбец, а затем вставить данные, большие цифры будут правильно отображаться.

Это, вероятно, потому что он хранится в виде строки. Что произойдет, если вы сделаете другую ячейку, скажем, ее содержимое, умноженное на 2? Peter Mortensen 11 лет назад 0
Это работает в Excel 2016 / Office 365. Я выбрал все ячейки, отформатировал их в текст, а затем вставил свои скопированные данные, и они не отображаются с назойливой научной нотацией. Спасибо! frezq 7 лет назад 0
10
Rok

Попробуйте это ... это работает для меня, Excel 2013.

=""&a1 

где a1 содержит числовое значение. После этого скопируйте столбец и вставьте его в исходный столбец.

Это работает и в Excel 2010. Paul G 10 лет назад 0
В качестве бокового узла: `=" 1240800388917 "` делает то же самое nixda 10 лет назад 2
5
Graham

Вы можете отформатировать ячейки как Custom> #

Это можно применить после вставки данных в столбец, но вы должны сделать это перед сохранением электронной таблицы.

Я много работаю со штрих-кодами (UPC) и Excel по умолчанию отформатирует их как научные обозначения. Сводит меня с ума! Почему это не вариант, я никогда не узнаю.

Это не преобразует числа в текст, хотя, что может вызвать проблемы позже. Но хорошо... Joris Meys 8 лет назад 0
3
gsoClarke

Excel 2010: это работает один столбец за раз, а вовсе не для строки. Выберите столбец или подмножество столбца, на вкладке «Данные» выберите «Текст в столбцы» и перейдите вправо к «Готово». Нет больше научных обозначений.

Я согласен со многими пользователями Excel, это преступно, что нет возможности предотвратить научную запись!

В моем тесте на Excel 2007 мне пришлось дважды нажать «Далее>», затем выбрать «Текст» и нажать «Готово». Вы можете применить это к строкам путем транспонирования, преобразования и транспонирования обратно. Scott 10 лет назад 1
3
AcePL

Я знаю возраст вопроса, но это тот, который я получил после поиска в Google и который не ответил на мой вопрос (почему форматирование как текст не работает и почему число сокращается до 15 цифр после форматирования печатать).

Краткий ответ: вы не можете работать со своим номером так, как вам нравится ПОСЛЕ того, как вы ввели числовое значение и нажали Enter. В тот момент, когда вы это сделаете, число будет усечено до 15 цифр и представлено как экспоненциальное.

Это не ошибка, это особенность.

Все, что вы делаете после этого, будет использовать усеченное значение, так что никакая хитрость форматирования не поможет. Однако вы можете использовать Text to columnsопцию для преобразования экспоненциальной нотации в текст (щелкните заголовок столбца, нажмите Text to data, затем Delimited, Nextснимите отметку со всех полей разделителей, выберите « TextВ» Column data formatи «затем» Finish), чтобы значения были преобразованы в текст и сразу отображались как 15-значное число. Но это будет только 15 цифр, поэтому, если у вас был более длинный номер, отдых теряется.

Для того чтобы номер в электронной таблице был точно таким, как вы его ввели, вы должны сохранить его в виде текста. Таким образом, перед вводом / копированием значений в него необходимо добавить апостроф или отформатировать ячейки как текст . Если это «иногда не работает», то, извините, вы либо делаете что-то не так, либо у вас включено автозамена (если вы используете апостроф и большое число> 15 цифр, Excel воспринимает это как неправильное значение и помечает ячейку предупреждением) сообщение, так что это замечание не является ни личным, ни критикой).

Другой способ сделать это в больших количествах - импортировать значения из текстового файла. Смею сказать, что это единственный способ для большинства ситуаций. И обязательно импортируйте файл, а не просто откройте его, так как Excel воспринимает csvтип как его собственный формат и обрезает большие числа до 15 цифр как само собой разумеющееся.

Теперь к последнему вопросу. Если вы введете большое числовое значение в ячейку Excel, отформатированную в виде текста, она все равно будет отображаться в научной нотации, если ячейка недостаточно широка. Это раздражает, но побочный эффект некоторых внутренних алгоритмов Excel. Просто сделайте ячейку шире, и вы будете видеть полную ценность каждый раз.

1
Christopher Chipps

In Excel 2013, the same thing happened to me and I just went to Format Cells -> Number then made sure the decimal place is set to "0".

аналогично анонимному ответу выше, но выбор «Количество» каким-то образом имеет больше смысла, чем выбор «пользовательский» ... плюс он находится выше в списке. во всяком случае, он делает то же самое, но мне это нравится больше billynoah 8 лет назад 1
0
Dima

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

Автоматически вся ваша электронная таблица будет использовать текст. Вам не нужно делать это по клеткам.

Увы, это не работает. Текст будет просто в научной записи, вот и вся проблема ... Joris Meys 8 лет назад 0

Похожие вопросы