Как запретить Excel автоматически преобразовывать числа в даты
В Excel есть несколько действительно умных функций, которые могут быть действительно полезны во многих случаях (а иногда и разочаровывать). Одна из таких областей — когда вы вводите числа в Excel. Иногда Excel автоматически заменяет эти числа датами.
Например, если вы введете 1/2 в Excel, Excel автоматически изменит это на 01-02-2020 (или 02-01-2020, если вы находитесь в США).
Точно так же, если вы введете 30-06-2020, Excel предполагает, что вы хотите ввести дату, и изменит ее на дату (как показано ниже).
Хотя в большинстве случаев это может быть то, что вам нужно, но что, если я этого не хочу. Что, если мне просто нужен точный текст 30-06-2020 или дробь 1/2.
Как запретить Excel менять числа на даты?
Вот о чем этот урок.
В этом руководстве по Excel я покажу вам два действительно простых способа остановить преобразование чисел в текст в Excel.
Но прежде чем я покажу вам методы, позвольте мне быстро объяснить, почему Excel это делает.
Почему Excel преобразует числа в текст?
Хотя это может расстраивать, когда Excel делает это, он пытается помочь.
В большинстве случаев, когда люди вводят числа, которые также могут представлять допустимые форматы дат в Excel, они автоматически преобразуются в даты.
И это просто не означает, что он меняет формат, это фактически изменяет базовое значение.
Например, если вы введете 3/6/2020 (или 6/3/2020, если вы находитесь в США и используете формат даты в США), Excel изменит значение ячейки на 44012, что является числовым значением даты.
Остановить Excel от автоматического преобразования чисел в даты
Единственный способ запретить Excel преобразовывать эти числа (или текстовую строку) в даты — это очистить, дав ему понять, что это не числа.
Посмотрим, как это сделать.
Измените формат на текст
Самый простой способ убедиться, что Excel понимает, что он не должен изменять число на текущий момент, — это указать формат ячейки как текст.
Поскольку даты хранятся в виде чисел, когда вы задаете формат текста ячейки, Excel поймет, что введенное число должно быть в текстовом формате, а не преобразовываться в дату.
Ниже описано, как остановить преобразование чисел в даты в Excel:
Вышеупомянутые шаги изменили бы формат ячейки на текст. Теперь, когда вы вводите любое число, например 30-06-2020 или 1/2, Excel не преобразует их в формат даты.
Вы также можете открыть диалоговое окно «Формат ячеек», выбрав ячейку, щелкнув ее правой кнопкой мыши, а затем выбрав параметр «Форматировать ячейку».
Этот метод лучше всего подходит, когда вам нужно изменить формат диапазона ячеек. Если вам нужно сделать это только для пары ячеек, лучше всего использовать метод апострофа, описанный ниже.
Добавьте апостроф перед числом
Если вам нужно ввести число только в несколько ячеек и вы не хотите, чтобы Excel менял его до настоящего времени, вы можете использовать этот простой прием.
Просто добавьте знак апострофа перед вводом числа (как показано ниже).
Когда вы добавляете апостроф в самом начале, Excel считает эту ячейку текстом. Хотя вы не увидите знака апострофа, вы можете визуально увидеть, что числа будут выровнены по левому краю (указывая, что это текст).
По умолчанию все числа выравниваются по правому краю, а все текстовые значения выравниваются по левому краю.
Еще одним преимуществом использования знака апострофа является то, что вы по-прежнему можете использовать формулы поиска (например, VLOOKUP или MATCH), используя значение в ячейке. Эти функции игнорируют апостроф.
Вы также можете использовать этот метод для преобразования существующих дат в текст. Например, если у вас есть 30-06-2020 в ячейке, вы можете просто добавить апостроф (‘) в начале, и это изменит дату на текст
Иногда вы можете увидеть зеленый треугольник в верхней левой части ячейки, который указывает на то, что числа были сохранены в виде текста в этих ячейках. Но поскольку в данном случае это именно то, что нам нужно, вы можете игнорировать эти зеленые треугольники или щелкнуть по ним, а затем выбрать опцию «Игнорировать ошибку», чтобы она исчезла.
Хотя эти методы хороши, что делать, если у вас есть столбец, полный дат, который вы хотите использовать как текст, а не как даты. Вот несколько простых методов, которые вы можете использовать для преобразования даты в текст в Excel.
Надеюсь, вы нашли это руководство по Excel полезным!
Отмена автоматической замены чисел датами
Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. Однако существует несколько способов обойти это.
Предформатация ячеек, в которые нужно ввести числа, в виде текста. В этом случае Excel не будет пытаться изменить то, что вы вводите в даты.
Если нужно ввести всего несколько чисел, можно сделать так, чтобы Excel не превнося их в даты, введите:
Пробел перед вводом числа. После нажатия ввод пробел останется в ячейке. (См. заметки)
Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47. Апостроф не отображается в ячейке после нажатия ввода.
Нуль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4-мар. Введите 0 0 1/2 или 0 3/4. Ноль не остается в ячейке после нажатия ввод, и ячейка становится типом дробного числа.
Выйдите из ячеек, в которые нужно ввести числа.
Нажмите клавиши CTRL+1 (1 в строке чисел над клавишами QWERTY), чтобы открыть формат ячеек.
Выберите тексти нажмите кнопку ОК.
Выберите ячейки, в которые необходимо ввести числа.
Щелкните Главная > Числовой формат > Текст.
Рекомендуется использовать апостроф вместо пробела для ввода данных, если вы планируете использовать функции подменю для данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.
Если число в ячейке выровнено по левому краю, обычно это означает, что оно не отформатировано как число.
При введении в ячейку числа с буквой «е», например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel меняет число на дату
При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. В ряде случаев можно быстро изменить текстовый формат на числовой сразу в нескольких ячейках. Произведем это преобразование с помощью Специальной вставки из Буфера обмена.
Если ячейка содержит число, но число сохранено как текст, то это может стать ошибкой в вычислениях. Например функция СУММ() игнорирует числа сохраненные как текст и может вернуть не то значение, которое от нее ожидают.
Как правило, в таких ячейках установлен текстовый формат. Если изменить формат ячейки на числовой (через Формат ячеек или Главная/ Число/ Числовой формат ), то изменений не последует.
Теперь присмотримся и увидим в левом верхнем углу каждой ячейки зеленый треугольник. При выделении ячейки сбоку от нее появится желтенький значок, нажав который можно преобразовать значения в числа.
После преобразования числа будут выравнены по правому краю ячейки (если, конечно, в формате ячейки установлено Выравнивание по горизонтали — по значению).
Чтобы преобразовать сразу несколько значений — выделите их. У верхней ячейки появится желтый значок — с помощью него преобразуйте сразу все значения.
Другой способ группового преобразования чисел, сохраненных как текст, можно использовать следующий подход:
После умножения на 1, числовые значения не изменятся, но будут преобразованы в числа (если по ошибке были выделены текстовые значения, операция умножения на них никак не повлияет).
Для EXCEL 2007: Если до умножения ячейки имели текстовый формат ячеек, то он будет сохранен (числовые значения все равно будут преобразованы, но будут выровнены по левому краю, как текст). Изменить формат ячеек на числовой можно стандартным способом через меню Формат ячеек (нажав CTRL+1).
Этот же прием поможет преобразовать ДАТЫ, сохраненные как текст в формат ДАТ (Если, конечно, EXCEL поймет ту форму, в которой записана ДАТА). Правда, после преобразования в ячейках будет содержаться число, соответствующее датам. Изменить формат ячеек на формат ДАТЫ можно стандартным способом через меню Формат ячеек (нажав CTRL+1).
СОВЕТ:
О преобразовании чисел из ТЕКСТового формата в ЧИСЛОвой можно также прочитать в одноименной статье Преобразование чисел из текстового формата в числовой (часть 1).
Бывают случаи, когда при работе в программе Excel, после занесения числа в ячейку, оно отображается в виде даты. Особенно такая ситуация раздражает, если нужно произвести ввод данных другого типа, а пользователь не знает как это сделать. Давайте разберемся, почему в Экселе вместо чисел отображается дата, а также определим, как исправить эту ситуацию.
Решение проблемы отображения числа как даты
Единственной причиной, почему данные в ячейке могут отображаться как дата, является то, что в ней установлен соответствующий формат. Таким образом, чтобы наладить отображение данных, как ему нужно, пользователь должен его поменять. Сделать это можно сразу несколькими способами.
Способ 1: контекстное меню
Большинство пользователей для решения данной задачи используют контекстное меню.
После этого данные в выделенных ячейках уже не будут отображаться как дата, а станут показываться в нужном для пользователя формате. То есть, будет достигнута поставленная цель.
Способ 2: изменение форматирования на ленте
Второй способ даже проще первого, хотя почему-то менее популярный среди пользователей.
- Выделяем ячейку или диапазон с форматом даты.
Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.
Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.
После этого, формат в выделенных ячейках будет изменен на тот, который вам нужен. Теперь числа в них не будут отображаться в виде даты, а примут заданную пользователем форму.
Как видим, проблема отображения даты в ячейках вместо числа не является особо сложным вопросом. Решить её довольно просто, достаточно всего нескольких кликов мышкой. Если пользователь знает алгоритм действий, то эта процедура становится элементарной. Выполнить её можно двумя способами, но оба они сводятся к изменению формата ячейки с даты на любой другой.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Почему САМОПРОИЗВОЛЬНО меняется формат на ДАТА, не в момент ввода, а когда в следующий раз открываешь документ.
Здравствуйте, Александр. А вы сохраняете документ после того, как снова меняете формат на нужный вам или просто его закрываете? Если все-таки сохраняете, то проверьте, не включены ли у вас случайно макросы в этом документе.
Спасибо. Страдаю от этого не первый год. Лицензия.
Спасибо! Очень помогли.
Здравствуйте. Вопрос заключается в том, что при выделении всей таблицы, где находятся даты вместо чисел, и форматируя ячейки по другим форматам, приводит к тому, что вместо дат появляются непонятные числа (не те, что в исходном документе). А изменяя таблицу повторно по форматам, изменяются уже эти неправильные числа в ячейках. Также в данные ячейки не получается записать исходник после всех манипуляций (преобразует снова в неправильное новое число).
Что делать, я уже не знаю??…Эксель 14 версии, офис 10
Спасибо! А то блин сам не разобрался бы.
Давно хотел Excel изучить всё времени нет
Здравствуйте! Почему в Excel вместо числа появляется текст проблему выложил на ютуб youtu.be/xh9_8icxj0c
Здравствуйте, Андрей. На ваш вопрос довольно сложно ответить, так как не известно, что это за документ. Но скорее всего у вас какой-то баг либо в макросе, либо в надстройке, которая переводит цифры в значение прописью. Посмотрите, какие макросы (или надстройки) у вас установлены и найдите тот. который отвечает за это. Если отключить этот макрос (или надстройку), то абсолютно все числа будут писаться цифрами, а не буквами. если вас такой вариант не устроит, то нужно связаться с разработчиком настройки или макроса, чтобы исправил баг.
Мне нужно проставить страницы в содержании(таблица), я ставлю 4-5, перехожу к другой ячейке, у меня в первой дата ставиться. Я работаю не в вордовском документе, а в OpenOffice. Таблицу я не делала, нам скинули отчет по практике, который мы должны заполнить, и таблица там уже была
Уже не надо, спасибо. Сделала по-другому, получилось
В Excel в столбце занесены данные в числовом формате, а при обработке этого файла в другой программе этот столбец меняет формат с числового на дату. Вопрос почему? Что не так с этим столбцом?
Здравствуйте, Сергей. Трудно ответить на ваш вопрос, не зная нюансов. Из вашего вопроса даже не понятно, пытаетесь вы повторно открыть файл в той же версии Эксель, в другой версии Эксель или вообще экспортируете в совсем другую программу. также было бы не плохо, если бы вы приложили полный скриншот того листа, где видна описываемая вами проблема. с выделением одной из ячеек проблемного столбца.
Попробую объяснить. Эксель 2007, есть таблица с данными на 1000 человек, в столбце дата стоит 28.11.1974, при просмотре ячейки через «формат ячейки» данные в ней «дата» при загрузке этого файла в программу которая проверяет правильность внесенных данных в Экселевский файл значение указанной ячейки исчезает, т.е. там пусто. Мне удалось установить, что если ячейку в данными 28.11.1974 войти в «формат ячейки» и по менять с «дата» поменять на «общий» или «числовой» появиться цифра 27361. Но в моем случае остается значение 28.11.1974. Если бы это был единичный случай проблем бы не было. Но так как файлы обрабатываются ежемесячно и в них 1000 человек, то приходиться в ручную проверять все 1000 дат на предмет такого казуса. При том что доходит до абсурда. Шаблон Экселевского файла один и тот же изменятся информация в других ячейках, а в ячейках столбца с датами уже другие даты ведут себя подобным образом.
У нас следующая проблема: из подпрограммы AutoCad выводим данные в Exel. Значений как правило много, больше 1000 и половина переводиться автоматически в даты.
В AutoCad загружается данный фал, но программа даты не воспринимает!
Однако на соседней машине, с такой же версией Exel, значения не преобразуются в даты.
Как отключить автоматическое преобразование?
такая же проблема с переводом из Autocad
я сделала как вы сказали в двух способах,но всё возвращается опять на дату
все сделал,но дата не исчезла
Добрый день. А вы после изменения формата ячейки попробуйте заново вбить нужное число.
почему excel воспринимает число как дату в html
если лист защищен ты ничего не можешь сделать, лучше б это описали
Добрый день. Этому вопросу у нас посвящена отдельная статья:
Снятие защиты с файла Excel
Приветствую Максим. Хелп, не могу пофиксить даты, ну никак. Пробовал разные форматы, как дат(в том числе пробовал иностранные форматы), так и числовые. Не получается у меня исходное число получить. Вот пример, есть ряд чисел, 88.28,11.25,0.48, указаны через запятую. Первое и третье числа переносятся как есть, второе число становится 25 ноября. При его формате я получаю разные числа, вплоть до 45962 (общий формат), но исходное число получить не могу.
Если ставить нормальную запятую то в формате ячейки числовой или текстовый, а так же общий — цифры остаются прежними.
А вот если Вы ставите точку то формат ячейки изначально должен быть текстовый! И только потом туда вбивать число.
Сергей, спасибо за ваш ответ. Немного детализирую проблему. Это импортированный документ, его необходимо упорядочить.Вся информация в нем до дефолту собрана в одном столбце. Я применяю функцию «Текст по столбцам» и далее указываю использовать запятые как разделитель, никаким другим образом я не получаю соответствие информации в столбцах и строках, то есть замена точек на запятые не подходит, от этого пострадает логика разбивки. Принудительное форматирование ячеек как текст так же не дает результатов. Единственный способ, которые сохраняет логику, но изначально понятен только мне, это замена на отличный от запятой символ, например знак доллара. Тогда, зная что доллар=запятая/точка, данные приобретают смысл и сходятся.
Если я правильно Вас понял Алексей, Вам не важно стоит запятая или точка в ячейке после применения функции «Текст по столбцам». Если честно с таким еще не сталкивался. Но заметил такую вещь. При применении функции «Текст по столбцам» в «Мастере текстов (разбор) — шаг 3 из 3» в «Образец разбора данных» можно выбирать столбец и к каждому столбцу можно задавать «Формат данных столбца» если поменять с «Общего» на «Текстовый» данные не поменяют свои значения. Попробуйте может это Вам поможет.
Как заменить дату на число?
Формат ячейки. Или у Вас другой случай? Тогда более подробнее.
изменяю формат с даты на числовой, но всё равно пишет, что это видоизменённая дата, как это изменить?
Не совсем понял. Можно подробнее сам процесс. И желательно с примером.
в ячейке с форматом общий(по умолчанию) ввожу 8-12, оно преобразуется в дату, я меняю формат на «числовой», заново ввожу 8-12, навожу на ячейку курсор, выпадает меня с надписью, что это видоизменённая дата, скрин прилагаю. Вот как сделать что бы число стало чистом а не видоизменённой датой?
Простите, но числа 8-12 в математике не существует! Если Вам нужно чтобы в ячейке было написано 8-12 сначала измените формат ячейки не «Текстовый» а затем введите 8-12.
Согласен, такого числа нет))), но есть числа написанные через тире. И если я Вас правильно понял, то надо сначала задавать формат ячейки «Текстовым», а затем вводить числа через тире или запятую? Если я сначала их ввожу в ячейку числа с тире или через запятую, точку, то программа понимает это как календарная дата и уже поменять формат на «Текстовой» не получится? Что собственно у меня и происходит. В любом случае спасибо за ответ!
Вместо даты во всём столбце стали отображаться пятизначные числа. Ни один из перечисленных в статье способов не помогает
Току нет. от статьи вопрос был не как поменять формат данных а как вставить изначально не как дату Я вставляю 5,03 он пишет 5 марта вот в этом проблема.
У Вас не запятая после 5, а точка. Можете указать конкретно проблема? Но при вставлении значения в ячейку с форматом «текстовый» значение не меняется.
например было число 7.55, эксель преобразовал в 01.07.1955, после изменения формата в ячейке 20271 что в принципе не верно
Сначала меняете формат ячейки на текстовый затем вставляете значение.
Нужно скопировать большой объем данных в эксель. Задаешь всем ячейкам формат — число. После копирования формат поменялся на дату. И часть ячеек с числами, часть с датами. Вместо 4.47 — стоит апр.47. Если поменять формат на числовой, будет 17258,00
А что не судьба задать формат ячейки — текстовый. Или это сверх сложная задача.
Здравствуйте! Попробовал все выше перечисленные варианты, но всё равно при в воде 5-30, почему то выходит дата 01.05.1930 и формат ячейки меняется. Помогите пожалуйста разобрать с этим вопросом.
Можете привести конкретный пример и последовательность действий? Вдруг удастся найти решение!)
Такая же песня. Уже час бьюсь. Меняю формат, нажимаю Ок, ввожу 3/30, пишет мар.30 и формат опять «дата».
Выделяю ячейку, правой клавишей вызываю контекстное меню, выбираю формат ячейки ставлю текстовый, после забиваю 3/30 или 3-30 и значение не меняется.
У меня получилось если заранее в ячейке поменять формат на текстовый, а затем вставить диапазон от 5 до 30
Нужно чтобы он раз и навсегда перестал так делать. Каждый раз изменять формат ячеек (особенно учитывая, что экзель забывает о том, какой формат мы ему задали, когда туда забивается число через CTR+V) неудобно.
Есть такие советы:
Приложение Microsoft Excel программируется так, чтобы облегчить ввод дат. Например, 12/2 меняется на 2-дек. Это очень плохо, когда вы вводите что-то, которое вы не хотите менять на дату. К сожалению, отключить эту функцию невозможно. Но есть и другие способы обойти.
Предформатируйте ячейки, в которых нужно вводить числа в виде текста. В этом случае Excel не будет пытаться изменить данные, вводимые в даты.
Если вы хотите ввести несколько чисел, вы можете запретить Excel изменить их на даты, введя:
Пробел перед вводом числа. После нажатия клавиши Ввод пространство останется в ячейке. (См. заметки)
Апостроф (‘) перед вводом числа, например, ‘ 11-53 или ‘ 1/47. После нажатия клавиши Ввод этот апостроф не отображается в ячейке.
Ноль и пробел перед вводом дробной части, например 1/2 или 3/4, чтобы они не переменялись на 2 января или 4 марта, например. Введите 0 1/2 или 0 3/4. После нажатия клавиши Ввод ноль не сохраняется в ячейке, а ячейка становится десятичным числом
Подойдут?
все замечательно, кроме одного: эти числа нельзя будет использовать в формулах. с таким же успехом можно просто поставить текстовый формат. или создать таблицу в ворде (
Приложение Microsoft Excel программируется так, чтобы облегчить ввод дат. Например, 12/2 меняется на 2-дек. Это очень плохо, когда вы вводите что-то, которое вы не хотите менять на дату. К сожалению, отключить эту функцию невозможно. Но есть и другие способы обойти.
Предформатируйте ячейки, в которых нужно вводить числа в виде текста. В этом случае Excel не будет пытаться изменить данные, вводимые в даты.
Если вы хотите ввести несколько чисел, вы можете запретить Excel изменить их на даты, введя:
Пробел перед вводом числа. После нажатия клавиши Ввод пространство останется в ячейке. (См. заметки)
Апостроф (‘) перед вводом числа, например, ‘ 11-53 или ‘ 1/47. После нажатия клавиши Ввод этот апостроф не отображается в ячейке.
Ноль и пробел перед вводом дробной части, например 1/2 или 3/4, чтобы они не переменялись на 2 января или 4 марта, например. Введите 0 1/2 или 0 3/4. После нажатия клавиши Ввод ноль не сохраняется в ячейке, а ячейка становится десятичным числом.
Чтобы открыть Формат ячеек, нажмите клавиши CTRL + 1 (1 в строке номеров выше ключа QWERTY).
Выделите тексти нажмите кнопку ОК.
Выберите ячейки, в которые необходимо ввести числа.
Щелкните Главная > Числовой формат > Текст.
Если вы планируете использовать функции поиска для данных, мы рекомендуем использовать апостроф вместо места для ввода данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.
Если число в ячейке выровнено по левому краю, обычно это означает, что оно не отформатировано как число.
При введении в ячейку числа с буквой «е», например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9
В зависимости от введенного числа в левом верхнем углу ячейки может появиться маленький зеленый треугольник, указывающий на то, что число хранится в текстовом формате, которое в Excel является ошибкой. Пропустите треугольник или щелкните его. Слева появится поле. Щелкните поле, а затем выберите пункт пропустить ошибку, после чего треугольник исчезнет.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).