Как в эксель вставить 20 значное число

Обновлено: 07.07.2024

Здравствуйте!
Копирую из Вордовской таблицы 20-ти значное число в Эксель.
В Экселе оно приобретает экспоненциальную форму. При этом округляет число, обнуляя младшие разряды. Игры с форматами ячеек не помогли.
Пример.
Число в таблице Ворда: 40702810702300000430
Результат копирования в Эксел: 4,07028E+19
В получивчемся числе уже обнулены три младших разряда, т.е. оно имеет значение 40702810702300000000.
Что делать? Как быть?

А вам нужно с ним вычисления производить или просто чтобы оно лежало в ячейке?

Я так понимаю, это номер банковского счета и никакие арифметические действия с ней производиться не будут.
Выберите формат ячейки "текстовый", а потом вставляйте.

Совершенно верно. Это номер банковского счёта. Вычислений с ним не производится, но он нужен для идентификации клиента. Т.е. требуется его точное значение.
К сожалению, выписка из банка конвертируется только в Ворд и представляет собой таблицу, в одном из столбцов которой указывается счёт клиента.
Копирование необходимой части выписки из Ворда в Эксель происходит вручную, а дальше средствами ВБА выписка причёсывается до нужной формы. Предварительное форматирование ячеек, в которые попадают номера счетов (в том числе и превращение их в текст) не дают искомого результата. Т.е. таблица из Ворда как бы накрывает исходное форматирование ячейки. Другими словами переформатирует её.
Прикладываю файл с кусочком исходной таблицы Ворд.

Действительно, если апостроф поставить в исходную Вордовскую таблицу, то при копировании не происходит округления, т.к. такая вордовская яйчека воспринимается Экселем как текст. Только какой-то осадок от корявости решения

Если изменить формат на текстовый предварительно, а потом вставить число в строку формул, то должно получиться

Да, так получается. Но это весьма муторное занятие. Таблица копируется целиком, а затем придётся отдельно номера счетов по одному копировать через строку формул. Выписка может содержать 20-30 строк.

Ну тогда наверное можно попробовать "поиск и замена"
407028 поменять на '407028, "заменить все" еще в Word перед вставкой в Excel.

Вариант-1
Прописывайте лидирующий апостроф в ячейки таблицы Word макросом
Word'а перед копированием в буфер.
Вариант-2
1. Форматируете столбец в Excel как текстовый (это необходимо хотя и не очевидно)
2. Копируете данные в Word в Clipboard
3. Вставляете скопированные данные с помощью "специальная вставка - текст в кодировке UNICODE"
Хвосты не теряются.

Пытаюсь вписать в ячейку Эксель длинное число (расчетный счет банка) и постоянно выскакивает такая формула: 4,08028E+19 (см. скрин). Что нужно сделать, чтобы вбить длинное число?

Смена формата ячейки (на числовой) НЕ помогает.

Такое представление вбитого в ячейку Экселя числа (а 20 знаков, состоящих исключительно из цифр (номер расчетного счета банка не подразумевает использования других, кроме цифр, символов), воспринимается программой именно как число) свойственно данной программе: по умолчанию в ней используется формат "Общий", но если в ячейке одни цифры, то формат автоматом переключается на "Числовой" и записанное большое число (в данном случае - 20-значное) преобразуется при отображении в экспоненциальную форму числа, состоящую из основы и показателя степени числа 10, на которую надо умножить упомянутую основу (что и видно на скриншоте).

Чтобы избежать такого поведения электронной таблицы, надо выбрать формат ячейки таблицы, как "Текстовый" - тогда число не будет преобразовываться.

Да, число е - это экспонента, о чем другие не сказали. — 4 месяца назад

20 знаков расчетного счета Эксель воспринимает как слишком длинное число и. сокращает его до приемлемых (по его меркам) размеров. Чтобы этого не происходило, можно воспользоваться двумя способами.

Способ первый, самый быстрый и легкий. Перед первой цифрой (в нашем случае это цифра 4) поставьте значок апострофа (он обычно находится на той же клавише, что и буква Ё и выглядит так - `). Значок этот виден не будет, но Эксель теперь станет рассматривать все написанное в ячейке в текстовом формате и это даст вам возможность вписать все 20 цифр.

Способ второй. Заходим во вкладку "Настройки" и меняем формат ячейки на текстовой.

Это не формула, это стандартная математическая запись большого числа в формате, понятном компьютеру.

В вашем случае это число 4.0802 умноженное на десять в семнадцатой степени.

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

Что делать? Или копаться в настройках Экселя, насильно запретив ему округлять, или просто увеличить (удлиннить) поле, котором указываете реквизиты, может двадцать символов туда просто не влезает. Или попробуйте рзаписывать реквизиты, разделяя цифры дефисами. Стандартный формат записи расчётного счёта

ААА-ББ-ВВВ-Г-ДДД-ЕЕЕ ЕЕЕЕ, хотя, в 2011 году формат мог быть и иным.

И непонятно, зачем вы пользуетесь именно этой программой, есть бухгалтерские программы специально заточенные под такие фокусы.

EXCEL хранит и выполняет вычисления с точностью 15 значащих цифр. Поэтому, не получится ввести 20-и значное число без того, чтобы EXCEL автоматически округлил его с точностью до 100 тыс. Используем текстовый формат для отображения таких чисел.

То, что вычисления в EXCEL производятся с точностью 15 значащих цифр, проявляется, например, при записи в ячейку целого двадцатизначного числа.

После нажатия клавиши ENTER происходит округление целого числа до 15 значащих цифр, последние цифры заменяются нулями (см. Строку формул на рисунке ниже). К числу будет применен экспоненциальный формат (см. ячейку А1 на рисунке ниже).

Увеличить точность вычислений в EXCEL нельзя. Однако, если не предполагается участие чисел в математических операциях, то для их хранения можно использовать текстовый формат. Такое решение подходит, например, для хранения номеров банковских счетов.

Примечание. При подсчете таких значений могут возникнуть сложности (см. статью Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL).

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.

Преобразование чисел в текст при импорте текстовых данных

Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query). В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

Откройте вкладку Данные, нажмите кнопку Получить данные и выберите вариант Из текстового/CSV-файла. Если вы не видите кнопку Получить данные, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите кнопку Импорт.

Если какие-либо столбцы нужно преобразовать в текст, выделите их, щелкнув заголовок, затем выберите Главная > Преобразовать > Тип данных > Текст.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

В диалоговом окне Изменение типа столбца выберите команду Заменить текущие, и Excel преобразует выделенные столбцы в текст.

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

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

В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста. В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

На ленте откройте вкладку Power Query и выберите Получение внешних данных > Из текста.

Совет: Чтобы выбрать несколько столбцов, щелкните их левой кнопкой мыши, удерживая нажатой клавишу CTRL.

По завершении нажмите кнопку Закрыть и загрузить, и Excel вернет данные запроса на лист.

Применение пользовательского формата для сохранения начальных нулей

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

Почему excel меняет цифры

Автор Александр Привизенцев задал вопрос в разделе Программное обеспечение

Добрый день.
Как передать длинное число (20 знаков) из экселя в фокс?
на самом деле ситуация такая.. у меня есть строка, состоящее из 13-и значного числа.. а в экселе есть поле с числом 20-ти знаков.. так вот каким бы образом сравнить эти два числа.

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший.. Ограничение в фокспро - 18 символов. Можно сравнивать не числа, а строки, например.

я знаю.. и я пытаюсь сравнивать строки.. просто я не могу из экселя в фокс передать необходимое значение..
например в экселе число 18200620401600000060, а в фокс передается как 1.82+Е, соответственно сравнить с строкой в фоксе"182006" уже не получается. к сожалению.. вот именно эту проблему я не могу решить..может вы мне подскажете?

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший.. Тут не могу подсказать. У меня экселя нет. Может быть поможет изменение формата ячейки? > просто я не могу из экселя в фокс передать необходимое значение.
каким образом передаются значения?

нет.. к сожалению.. это не помогает.. а идей каких-нить нет..о преобразовании.. я уже на самом деле пыталась делить 20-ти значное число на 1000, дабы уменьшить разрядность.. но это не выход((((

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший..

Даже в Excel не может быть 20 значащих цифр - попробуй ввести в
ячейку скажем 12345678901234567890 - Excel отсечёт 5 младших разрядов,
заменив их на 0.
когда ты в фоксе получаешь нечто типа 1,234567890123450E+19 то это и есть
12345678901234500000 - т.е. эти числа вполне можно сравнивать - ИМЕННО как
числа.
Если в Excеl хранится ТЕКСТ в виде числа, то никаких проблем с его
получением нет - он через Cells(x,y).Value и придёт как текст.
Получить собственно ТЕКСТ - т.е. то в каком виде отображается нечто в ячейке
тоже можно - это свойство Cells(x,y).Text - просто я не вижу в этом особого
смысла - ведь то-же чисто может быть отформатировано так что будут пробелы
между тройками цифр, буковки разные ненужные и т.п.
P.S. Я не понимаю как вообще можно сравнивать 13-значное число с 20-значным.
Сразу могу сказать что они не равны И 13-значное всегда меньше, если они
оба положительны

передаются обычным:
with m.oe
.workbooks.open(xlsfile)
.DisplayAlerts=.f.
with .workbooks(1).sheets(1)
dat=.Cells(m.x,2).Value
.

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший.. Igor Korolyov
когда ты в фоксе получаешь нечто типа 1,234567890123450E+19 то это и есть
12345678901234500000 - т.е. эти числа вполне можно сравнивать - ИМЕННО как
числа.

не сравниваются.. еще раз..может не понятно написала.. я могу е правильно поставить задачу.
на саомм деле мне надо найти вхождения символов строки из 13 в 20-ти значную. но, строка с 20 значным есть число( в экселе это строка из 20 символов состоит из одних чисел и меет данная ячейка имеет формат: ДОполнительный-почтовый индекс). а когда я передаю в фокс.. он действительно передается в таком вот виде 1,234567890123450E+19 .. и не могу произвести определения вхождения подстроки в эту строку.

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший..

Если формат этого "числа" так как оно выглядит в Excel тебя полностью
устраивает - пользуй Text вместо Value. А вообще очень странное решение -
хранить не-число в формате числа. Может создатель листа не в курсе
ограничений Excel по точности, или не знает как ввести в ячейку число так,
чтобы оно воспринималось как строка?

Создатель файла Excel виноват. точнее, ввиду того что их окола 100 этих создателей.. и никаких унифицированных форм не существует.. каждый раз данное длинное число представляется в разнообразных формах, в т.ч. как "число" и как "число, преобразованное в строку", соответственно мне надо предусмотреть все варианты.
за text спасибо, попробую.

------------------
..Если ты выполняешь работу и не стремишься к большему, ты или лентяй или сумасшедший..

Когда числа с большим количеством цифр, то всегда использую число в EXCEL как текст. Только EXCEL часто бывает капризный, надо хорошо формат ячеек перевести в текст, иначе при переводе в DBF числа могут переводиться не текстом, а числом.
А вообще не люблю прямой импорт из EXCEL в DBF в программе VFP, лучше сначала сконвертировать файл EXCEL в DBF в самом EXCEL (если файлов не много).

Читайте также: