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

Question

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

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

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

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

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

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

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

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

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

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

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

Answer 1

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

Answer 2

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

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

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

Answer 3

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

Answer 4

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

Answer 5

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

=""&a1

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

Answer 6

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

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

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

Answer 7

Я знаю возраст вопроса, но это тот, который я получил после поиска в 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. Просто сделайте ячейку шире, и вы будете видеть полную ценность каждый раз.

Answer 8

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

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

Answer 9

В Excel 2013 то же самое произошло со мной, и я просто зашел в «Формат ячеек -> Число», а затем убедился, что для десятичного знака установлено значение "0".

Answer 10

1. Скопируйте / вставьте проблемное поле в новый лист в столбце A

2. Скопируйте / вставьте столбец A в текстовый файл

3. Отформатируйте столбец B, чтобы текст и вставить текстовый файл в столбец B

4. В колонке С:

   =IF(ISNUMBER(SEARCH("+",B1)),A1,"")
   

5. Преобразовать столбец C в числовой формат без десятичных знаков

6. Скопируйте / вставьте столбец C в текстовый файл

7. Отформатируйте столбец D в текст и вставьте текстовый файл в столбец D.

8. В колонке 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)))
   

9. Скопируйте / вставьте столбец E в блокнот

10. Удалить данные в проблемном поле и отформатировать столбец в текст

11. Копировать / вставить данные из текстового файла в проблемное поле

Answer 11

Это работало для меня в Excel 2013, включая копирование ячеек в другие программы. Фильтрация данных помогла, особенно для шагов редактирования.

1. Выберите нужные ячейки.
2. Примените числовой формат "Текст". (Попробуйте Alt, H, N, затем "Текст".)

Затем для каждой желаемой ячейки:

1. Войдите в режим редактирования. (Попробуйте F2.)
2. Подтвердите изменения нажатием Enter (хорошо для столбцов) или Tab (хорошо для строк). Не изменяйте содержимое.

Ваш номер будет отображаться нормально, даже в 100 цифрах. знак равно

Answer 12

Excel расстраивает из-за такой работы с CSV/ объемными данными. Работайте с LibreOffice, если это возможно, он этого не делает, то есть, если вы открываете тот же самый лист (.xlsx) в Libreoffice, большие "числа", которые фактически являются строками, не отображаются в научной нотации (что является абсурдным решением). Excel). Файловая точность LibreOffice великолепна: снова откройте тот же .xlsx в Excel, и научная запись мгновенно "работает", как обычно.

Answer 13

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

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