Эксель убирает два последних нуля проблема
Есть типовая обработка - Загрузка данных из табличного документа, с помощью которой нужно загрузить цены из экселя.
Все работает совершенно замечательно за исключением тех случаев, когда код номенклатуры состоит из числа и лидирующих нулей. Например 00000000255. В этом случае после открытия файла в обработке отображается 255 вместо 00000000255. Естессно цена для этой номенклатуры не загружается.
В самом экселе, в ячейке отображается 00000000255, но если открыить ее на редактирование, то отобразится 255. Формат у этой ячейки какой-то нестандартный, вот такой: 00000000000;[Красный]-00000000000.
Если указать текстовый формат, то ведущие нули сразу же обрезаются, но если после этого ввести эти нули руками, то - о чудо они сохраняются.
На самом деле никакого чуда нет - текстовый формат хранит значение в том виде как оно введено.
Собственно в чем вопрос, можно ли как-то преобразовать такие значения в текстовые? Может есть какой-то макрос или еще какой-то способ?
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;
Хмм.. прежде чем советовать, хотелось бы узнать что автор может изменить -формат Экселя или "есть типовая обработка"(цы) Если ни то ни другое изменить нельзя - то типовую обработку выгрузить во внешнюю и пририсовать свои бантики и рюшечки.
(9) Спасибо, очень полезная статья. Теперь осталось придумать как с этой бедой бороться.
А у меня как раз строка и преобразовывать ее в число нет никакого желания, потому как есть коды с буковками.
(12) А вот вы откуда знаете на этапе разработки - сколько лидирующих нулей имеется?
если ломать обработку лень, можно еще попытаться в файле нафигачить формат Текстовый и в ячейки вхерачить "00000000255" прямо с кавычками(. )
(13) Открою тебе большой секрет: мне об этом сообщил автор в (11) и в (0) :)
В обработке есть чудо механизм "события". В событии при загрузке этого поля ставь "Формат" и будет тебе счастье
(13) тут надо знать длину поля, куда потом пойдет загрузка
(17) Да есть такой механизм. В каком событии мне нужно поставить формат, чтобы найти в справочнике номенклатуры позицию с кодом 00000000255, если в табличном документе из которого нужно загрузить, этот код записан как 255?
(19) Ёлки-моталки! Там работы на час (максимум). Ты же вторые сутки мозг имеешь.
+ (19) По коду номенклатуры идет поиск, при загрузке из табличного документа. Ну то есть на вкладке настройка, в колонке режим загрузки, для номенклатуры установлено искать по коду
+ (22) . с помощью кувалды, лома и такой-то матери, а хочется гармонии.
(23) прочитай из метаданных длину поля-получателя, накинь слева 30 нулей и возьми ПРАВ от строки на длину поля-получателя
будет железно и гармонично :)
(кстати) Если по метаданным в коде 11 символов - это вовсе не значит что "реальные" коды справочника будут формироваться длиной в 11 символов. Автонумерация, например, формирует коды/номера с учётом длины поледнего кода/номера.
(24) я так и сделал, только не 30 нулей, 11 по длине кода
(25) я проверил длина везде одинаковая, это как раз критерий - если прочитанное из экселя меньше определенной длины, значит нужно дополнить нулями
(27) я делал нечто похожее однажды, строку нулей формировал в цикле, по длине метаданного
Наведи порядок в своей работе используя конфигурацию 1C "Управление IT-отделом 8"
Если я вставляю 04562, он автоматически усекает ведущий ноль и сохраняет его как 4562.
Я хочу, чтобы он хранил его как 04562.
Если вы используете MS Excel для редактирования ячеек, в которых вам нужно хранить числа с начальным нулем, просто отформатируйте и определите содержимое ячейки как текст. Если вы сохраняете его программно, вы можете поместить значение в кавычку " для того же самого.
обновление
Я только что заметил, что если я перейду в "формат ячеек" и выберите пользовательский формат и поместите, скажем, 0000 в формат, любое число, которое я помещаю в ячейку, это меньше 4 цифр, он все еще поддерживает 4 длина цифры с ведущим '0', как 0001, 0099, 0120, 9999
пока все это точный обходные пути для преобразования числа в текст так, что ведущие нули будут сохранены, это не ответ на вопрос.
ответ на ваш вопрос заключается в том, что если вы хотите номер с ведущими нулями в Excel, вы не можете иметь его, по крайней мере во время импорта.
разработчики Microsoft по какой-то причине построили Excel для чтения в 03 Как число и усечения ведущего нуля, в результате чего 3. Нет способа предотвратить этот.
кстати, Google Таблицы делает то же самое.
таким образом, короткий ответ заключается в том, что нет приложения электронной таблицы (по крайней мере, я знаю), где вы можете прочитать в csv-файле, который может иметь, скажем, 00453 как число и импортировать его как 00453, а не 453.
щелкните ячейку и на вкладке Главная в группе число нажмите кнопку вызова диалогового окна рядом с номером.
в списке категория щелкните пользовательский, а затем в поле Тип введите формат номера, например 000-00-0000 для кода номера социального страхования или 00000 для пятизначного почтового индекса код.
вы можете сохранить его, используя следующий формат.
в excel, вы можете
1) Установите формат в "текст", выбрав нужную ячейку, щелкнув правой кнопкой мыши, выбрав"формат ячейки". Затем на вкладке " номер "выберите"текст". Каждый раз, когда вы вводите номер, он будет читаться как текст
2) перед любым числом с апострофом ( ' ) и Excel примет его "как есть"
Если вы используете VBA, вы можете сделать после
1) Выберите ячейку, которую вам нужно отформатировать, и измените опцию формата номера
когда вы читаете данные (из ячейки в Excel или из VBA), он будет содержать любые начальные нули, которые были введены.
Я подумал, что было бы полезно сделать шаг за шагом для некоторых людей, которым может понадобиться немного больше помощи, чтобы найти, где выбрать эти вещи.
выберите нужный квадрат или квадраты, нажав на них / выделив их.
оставайтесь на Home вкладка вверху. Полностью на правой стороне, на втором поле справа, есть опция, которая говорит Format . Нажмите на это.
нажмите на Format Cells. полностью внизу. Первая вкладка должна сказать Number . На том Number tab, есть опция, которая говорит Text . Щелкнуть по нему. Теперь нажмите OK внизу, и сделали.
Если вы просто дважды щелкните текстовый файл csv, чтобы открыть его, Office 2010 откроет его и усечет все ведущие нули, потому что это общее форматирование каждого столбца по умолчанию. Вместо этого вы можете импортировать файл: перейдите на вкладку "Данные" - параметры "получить внешние данные" включают "из текста" - откроется мастер, в котором вы можете пометить файл для импорта, просмотреть его и определить формат для любого столбца, содержащего нули свинца, как " текст."Когда вы импортируете таким образом, ведущие нули консервированный.
вы также можете просто префикс вашего значения с ', хотя это помечает ячейку, пока вы не сделаете ее текстовым полем.
вот мое лучшее и простое решение. Потому что Excel изменит номера, если у вас уже есть введенные данные. Работает только при вводе после изменение формата.
- Добавить новый столбец
- выделите столбец и измените формат на текст, щелкнув правой кнопкой мыши и отформатируйте ячейки
- возьмите почтовые индексы или номера clumn и скопируйте его
- вставить в блокнот (чтобы избавиться от форматирования)
- скопировать в блокнот данные и вставить в новый столбец Excel
Я не знаю, является ли это идеальной работой, но я попробовал это в первый раз, и это сработало.
на левой ячейке вы не видите ведущий 0 и я хотел добавить, ведущей 0 . Исправление заключается в использовании формулы ="0"&A3(Cell number)
(не забудьте установить свой, и он должен отлично работать см. пример ниже.
добавление апострофа перед числом, которое начинается с нуля, сохранит полное число, но не опубликует Апостроф. Например: если ваш номер 08753 excel будет усекать число до 8753, если вы не введете ' 08753. Затем Excel заполнит ячейку 08753. Полегче!
Если сумма, которую вы собираетесь ввести, будет иметь фиксированное количество цифр, скажем, например, почтовый индекс длиной 5 цифр, то вы можете применить индивидуальный формат к этой ячейке с маской: 00000. При этом независимо от того, какое количество, которое не превышает 5 цифр в длину, вы вводите, ведущие нули будут сохранены.
- Right-clic на ячейке для форматирования.
- Clic в формате ячеек.
- выберите вкладку номер.
- Clic на настроить выбор.
- Clic по любым параметрам, перечисленным в списке справа.
- Clic по типу texbox и перезаписать его содержимое с помощью "00000".
- Clic on Ok.
проблема актуальна хранимых значений и отображения значений. Все эти "исправления" презентации не затрагивают потерю ведущих нулей в сохраненном значении.
поэтому, если вы вводите значение в ячейку или импортируете конечный результат, вы не можете сохранить начальные нули в сохраненном значении независимо от ограничений форматирования в ячейке.
многие говорили, что это не простой маневр. Вот предложение.
скажите, что ваши ошибочные почтовые индексы (без ведущих 0) находятся в столбце A.
- вставьте фиктивный столбец, давайте использовать B.
- в столбце B поместите формулу, =A1*1 и скопируйте вниз, чтобы захватить все молнии. После этого вы должны увидеть ведущие нули, однако вы не законченный. Их все равно не прочтут.
- скопируйте столбец B, затем вставьте содержимое столбца B в a новый рабочая тетрадь.
- сохраните новую книгу как .CSV-файл (при сохранении нажмите на Поле "Сохранить как тип"и выберите CSV с разделителями-запятыми).
- откройте новый CSV-файл, который вы только что создали.
- скопируйте, затем вставьте содержимое непосредственно поверх вашего дерьма неправильный формат zip-коды. (это означает, что вы не можете иметь вставить местоположение фильтруется, имейте это в виду). Столбец содержание должно совпадать или ваши данные будут не совпадать.
- нажмите и выберите все zip коды, которые вы только что вставили.
- выберите Формат ячеек и выберите: специальный, затем почтовый индекс
- надеюсь, ваши молнии будут содержать 5-значное форматирование и должны быть умеет читать в формулах и тому подобное.
это долгое исправление и сработало для меня. Надеюсь, это сработает для вас!
Если вы импортируете / открываете a .csv-файл с ведущими нулями используйте Open Office или Libre Office. Когда вы откроете файл, он представит диалоговое окно для указания таких вещей, как разделитель столбцов. Вы также можете выбрать один из столбцов и указать тип как "текст", который сохранит начальные нули.
Если вы сохраните его как a .xlsx файл, он может быть открыт с Excel и сохранит ведущий 0.
похоже, что должен быть способ указать это при импорте в Excel, но я не смог его найти.
Обычно при вводе числа с ведущими нулями в ячейку ведущие нули автоматически исчезают, как показано ниже. Если вы хотите сохранить начальные нули входных чисел, вам могут помочь методы, описанные в этой статье.
Отформатируйте ячейку так, чтобы ведущие нули не пропадали при вводе знака апострофа
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Вы можете сохранить ведущие нули входящего числа, предварительно поставив знак апострофа. Пожалуйста, сделайте следующее.
1. Перед вводом числа с ведущими нулями введите апостроф отметка в камере. Смотрите скриншот:
2. Затем введите в ячейку нужный номер с ведущими нулями. И все ведущие нули сохраняются, как показано на скриншоте ниже.
Отформатируйте ячейку так, чтобы ведущие нули не пропадали при форматировании ячейки как текста
Вы также можете отформатировать ячейки как текст, чтобы в ячейках отображались ведущие нули. Пожалуйста, сделайте следующее.
1. Выделите ячейки, в которых должны сохраняться ведущие нули входных чисел, затем щелкните Главная > Формат номера выпадающий значок> Текст. Смотрите скриншот:
Теперь выбранные ячейки отформатированы как текст. И все ведущие нули будут сохранены перед входными числами. Смотрите скриншот:
Как не показывать 0 в Эксель? В версиях 2007 и 2010 жмите на CTRL+1, в списке «Категория» выберите «Пользовательский», а в графе «Тип» — 0;-0;;@. Для более новых версий Excel жмите CTRL+1, а далее «Число» и «Все форматы». Здесь в разделе «Тип» введите 0;-0;;@ и жмите на «Ок». Ниже приведем основные способы, как не отображать нули в Excel в ячейках для разных версий программы — 2007, 2010 и более новых версий.
Как скрыть нули
Существует много способов, как не показывать 0 в Excel, если в них нет необходимости. Здесь алгоритм действий немного отличается в зависимости от версии программы.
Версия 2007 и 2010
При наличии под рукой версии 2007 или 2010 можно внести изменения следующими методами.
Числовой формат
Наиболее простой способ, как не показывать нулевые значения в Excel — скрыть данные с помощью форматирования. Сделайте следующие шаги:
- Выделите ячейки с цифрами «0», которые нужно не показывать.
- Кликните на CTRL+1 или зайдите в раздел «Главная», а далее «Ячейки» и «Формат».
- В разделе «Категория» формата ячеек кликните на «Пользовательский»/«Все форматы».
- В секции «Тип» укажите 0;-0;;@.
Скрытые параметры показываются только в fx или в секции, если вы редактируете данные, и не набираются.
Условное форматирование
Следующий метод, как в Экселе не показывать нулевые значения — воспользоваться опцией условного форматирования. Сделайте следующие шаги:
- Выделите секцию, в которой имеется «0».
- Перейдите в раздел «Главная», а далее «Стили».
- Жмите на стрелку возле кнопки «Условное форматирование».
- Кликните «Правила выделения …».
- Выберите «Равно».
- Слева в поле введите «0».
- Справа укажите «Пользовательский формат».
- В окне «Формат …» войдите в раздел «Шрифт».
- В поле «Цвет» выберите белый.
Указание в виде пробелов / тире
Один из способов, как в Excel не показывать 0 в ячейке — заменить эту цифру на пробелы или тире. Для решения задачи воспользуйтесь опцией «ЕСЛИ». К примеру, если в А2 и А3 находится цифра 10, а формула имеет вид =А2-А3, нужно использовать другой вариант:
- =ЕСЛИ(A2-A3=0;"";A2-A3). При таком варианте устанавливается пустая строка, если параметр равен «0».
- =ЕСЛИ(A2-A3=0;"-";A2-A3). Ставит дефис при 0-ом показателе.
Сокрытие данных в нулевом отчете Excel
Как вариант, можно не отображать нулевые значения в Excel в отчете сводной таблицы. Сделайте следующие шаги:
- Войдите в «Параметры», а в разделе «Параметры сводной таблицы» жмите на стрелку возле пункта с таким же названием и выделите нужный раздел.
- Кликните на пункт «Разметка и формат».
- В секции изменения способа отображения ошибок в поле «Формат» поставьте «Для ошибок отображать», а после введите в поле значения. Чтобы показывать ошибки в виде пустых ячеек удалите текст из поля.
- Еще один вариант — поставьте флажок «Для пустых ячеек отображать» и в пустом поле введите интересующий параметр. Если нужно, чтобы поле оставалось пустым, удалите весь текст.
Для более новых версий
Ситуация обстоит иным образом, если нужно не показывать нули в Excel более новой версии. В таком случае воспользуйтесь одним из предложенных ниже решений.
Как не показывать 0 в выделенных ячейках
Это действие позволяет не отображать «0» в Excel с помощью числовых форматов. Спрятанные параметры показываются только в панели формул и не распечатываются. Если параметр в одной из секций меняется на нулевой число, параметр отобразится в ячейке, а формат будет правильным.
- Выделите место таблицы, в котором имеется «0» в Excel.
- Жмите CTRL+1.
- Кликните «Число» и «Все форматы».
- В поле «Тип» укажите 0;-0;;@.
- Кликните на кнопку «ОК».
Скрытие параметров, которые возращены формулой
Следующий способ, как не отображать 0 в Excel — сделать следующие шаги:
- Войдите в раздел «Главная».
- Жмите на стрелку возле «Условное форматирование».
- Выберите «Правила выделения ячеек» больше «Равно».
- Слева введите «0».
- Справа укажите «Пользовательский формат».
- В разделе «Формат ячейки» введите «Шрифт».
- В категории «Цвет» введите белый и жмите «ОК».
Отражение в виде пробелов / тире
Как и в более старых версиях, в Экселе можно не показывать ноль, а ставить вместо него пробелы / тире. В таком случае используйте формулу =ЕСЛИ(A2-A3=0;"";A2-A3). В этом случае, если результат равен нулю, в таблице ничего не показывается. В иных ситуациях отображается А2-А3. Если же нужно подставить какой-то другой знак, нужно между кавычками вставить интересующий знак.
Скрытие 0-х параметров в отчете
Как вариант, можно не показывать 0 в Excel в отчете таблицы. Для этого в разделе «Анализ» в группе «Сводная таблица» жмите «Параметры» дважды, а потом войдите в «Разметка и формат». В пункте «Разметка и формат» сделайте следующие шаги:
- В блоке «Изменение отображения пустой ячейки» поставьте отметку «Для пустых ячеек отображать». Далее введите в поле значение, которое нужно показывать в таблице Excel или удалите текст, чтобы они были пустыми.
- Для секции «Изменение отображения ошибки» в разделе «Формат» поставьте отметку «Для ошибок отображать» и укажите значение, которое нужно показывать в Excel вместо ошибок.
Как показывать нулевые значения
Иногда возникает обратная ситуация, когда нужно показать 0 в Excel.
Для Эксель 2007 и 2010
Для Excel 2007 и 2010 сделайте следующие шаги:
- Сделайте «Дополнительно».
- В группе «Показать параметры для следующего листа». Чтобы показать «0», нужно установить пункт «Показывать нули в ячейках, которые содержат 0-ые значения».
Еще один вариант:
- Жмите CTRL+1.
- Кликните на раздел «Категория».
- Выберите «Общий».
- Для отображения даты / времени выберите нужный вариант форматирования.
Для более новых версий
Для Эксель более новых версий, чтобы показывать 0, сделайте следующее:
- Выделите секции таблицы со спрятанными нулями.
- Жмите на CTRL+1.
- Выберите «Число», а далее «Общий» и «ОК».
Это основные способы, как не показывать 0 в Excel, и как обратно вернуть правильные настройки. В комментариях расскажите, каким способом вы пользуетесь, и какие еще имеются варианты.
Читайте также: