Как преобразовать текст в дату в excel или извлечь ее из сложной фразы?

4. Использование вставки значений

Если вам нужно переместить текстовые ячейки, содержащие числа, в новую ячейку или столбец, вы можете использовать функцию «Специальная вставка».

1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Во всплывающем меню выберите Формат ячеек .

2. В открывшемся окне убедитесь, что в качестве формата чисел выбран Общий, и выберите OK .

3. Выделите весь столбец ячеек, который вы хотите преобразовать из текста в числа, справа- щелкните и выберите Копировать .

4. Выберите первую ячейку в пустом столбце, который вы отформатировали, щелкните ячейку правой кнопкой мыши и выберите Вставить значения . Вы увидите все числа в текстовом формате, вставленные в поле «Общие». числовой формат.

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

Преобразовать строку в число в excel

На этом шаге мы перечислим функции преобразования форматов .

Преобразование строки в число и обратно осуществляют следующими функциями.

Функция	Назначение
Val ( строка )	Возвращает числа, содержащиеся в строке, как числовое значение соответствующего типа
Str ( число )	Возвращает значение типа Variant (String) , являющееся строковым представлением числа

В качестве допустимого десятичного разделителя функция Str воспринимает только точку. При наличии другого десятичного разделителя (например, запятой) для преобразования чисел в строки следует использовать функцию CStr , указанную в конце этого шага.

Чтобы представить числовое значение как дату, время, денежное значение или в специальном формате, следует использовать функцию Format .

Функция Format возвращает значение типа Variant (String) , содержащее выражение, отформатированное согласно инструкциям, заданным в описании формата. Синтаксис:

При построении пользовательского числового формата возможно использование следующих символов.

Символ	Назначение
	Резервирует позицию цифрового разряда. Отображает цифру или нуль. Если у числа, представленного аргументом, есть какая-нибудь цифра в той позиции разряда, где в строке формата находится 0, функция отображает эту цифру аргумента, если нет — в этой позиции отображается нуль
#	Резервирует позицию цифрового разряда. Отображает цифру или ничего не отображает. Если у числа, представленного аргументом, есть какая-нибудь цифра в той позиции разряда, где в строке формата находится #, функция отображает эту цифру аргумента, если нет — в исходной позиции не отображается ничего. Действие данного символа аналогично действию 0, за исключением того, что лидирующие нули не отображаются
. ( точка )	Резервирует позицию десятичного разделителя. Указание точки в строке формата определяет, сколько разрядов необходимо отображать слева и справа от десятичной точки
%	Резервирует процентное отображение числа
,	Разделитель разряда сотен от тысяч
Разделитель часов, минут и секунд в категории форматов Время (Time)
Разделитель дня, месяца и года в категории форматов Дата (Date)
E+, E-, e+, e-	Разделитель мантиссы и порядка в экспоненциальном формате

Кроме функций Val и Str в VBA имеются следующие функции преобразования типов выражений из данного в указанный.

Функция	Тип, в который преобразуется выражение
CBool ( Выражение )	Boolean
CByte ( Выражение )	Byte
CCur ( Выражение )	Currency
CDate ( Выражение )	Date
СDbl ( Выражение )	Double
CDec ( Выражение )	Decimal
CInt ( Выражение )	Integer
CLng ( Выражение )	Long
CSng ( Выражение )	Single
CVar ( Выражение )	Variant
CStr ( Выражение )	String

На следующем шаге мы рассмотрим функции обработки строк .

Преобразование формата через формулы

Перевести цифровые значения из текстового формата в числовой можно с помощью специальной формулы ЗНАЧЕН.

1. В данном случае нужно создать новый столбик справа от значений, которые будем переводить в другой формат.
2. В первой ячейке нового столбика вводим формулу «=ЗНАЧЕН(D5)». В скобках следует указать адрес ячейки.
3. После применения формулы в первой ячейке следует растянуть ее действие на всю длину столбца, нажав курсором мышки на нижний правый угол ячейки и потянув его вниз.

Использование функции ЗНАЧЕН

1. Преобразованные значения копируем и переносим в столбец с исходными данными. Выделяем столбец с новыми значениями и нажимаем комбинацию клавиш на клавиатуре «Ctrl+С». Таким образом значения сохранились в буфере обмена. Далее переходим в первую ячейку столбца с исходными значениями и, нажав на стрелочку под параметром «Вставка» на «Главной» вкладке, выбираем категорию «Вставить значения».

Вставка преобразованных ячеек

Как преобразовать текст в число в Эксель?

Вообще, классический метод – использование макросов. Но есть некоторые способы, не предусматривающие программирования. 

Зеленый уголок-индикатор

О том, что числовой формат был преобразован в текстовый, говорит появление своеобразного уголка-индикатора. Его появление – это своеобразная удача, поскольку достаточно выделить все, кликнуть по всплывающему значку и нажать на «Преобразовать в число».

1

Все значения, записанные, как текст, но содержащие цифры, преобразуются в числовой формат. Может случиться и такое, что уголков нет вообще. Тогда нужно убедиться, что они не отключены в настройках программы. Они находятся по пути Файл – Параметры – Формулы – Числа, отформатированные как текст или с предшествующим апострофом.

Повторный ввод

Если есть небольшое количество ячеек, формат которых был неправильно преобразован, то можно ввести данные заново, чем изменить его вручную. Для этого необходимо кликнуть по интересующей ячейке и затем – клавише F2. После этого появляется стандартное поле ввода, где нужно перенабрать значение, а потом нажать Enter.

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

Формула преобразования текста в число

Если создать еще одну колонку по соседству с тем, где числа указаны в неправильном формате и прописать одну простую формулу, он автоматически будет преобразован в числовой. Она указана на скриншоте.

2

Здесь двойной минус заменяет операцию умножения на -1 дважды. Зачем это делается? Дело в том, что минус на минус дает положительный результат, поэтому результат не изменится. но поскольку Excel выполнял арифметическую операцию, то значение не может быть другим, кроме как числовым.

Логично, что можно использовать любую подобную операцию, после которых изменения значения нет. Например, добавить и вычесть единицу или разделить на 1. Результат будет аналогичным.

Специальная вставка

Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:

1. Ввести единицу в любую ячейку, не содержащую никаких значений.
2. Скопировать ее. 
3. Выделить ячейки с записанными в текстовом формате числами и изменить его на числовой. На этом этапе ничего не изменится, поэтому выполняем дальнейший этап.
4. Вызвать меню и воспользоваться «Специальной вставкой» или Ctrl + Alt + V.

5. Откроется окно, в котором нас интересует радиокнопка «значения», «умножить».

По сути эта операция выполняет аналогичные предыдущему методу действия. Единственная разница, что используются не формулы, а буфер обмена. 

Инструмент «Текст по столбцам»

Может иногда оказаться более сложная ситуация, когда числа содержали разделитель, который еще может быть в неправильном формате. В таком случае необходимо использовать другой метод. Нужно выделить ячейки, которые нужно модифицировать и нажать на кнопку «Текст по столбцам», которую можно отыскать на вкладке «Данные».

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

Появится мастер, в котором настройка осуществляется в три шага. Нам нужно пропустить два из них. Для этого достаточно дважды кликнуть «Далее». После этого появится наше окно, в котором можно указать разделители. Перед этим нужно кликнуть на кнопку «Дополнительно».

4

Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.

Макрос «Текст – число»

Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.

Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).

Sub Convert_Text_to_Numbers()

    Selection.NumberFormat = “General”

    Selection.Value = Selection.Value

End Sub

Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас. 

Преобразование текста в число в ячейке Excel

​ которых хотя бы​Скачать последнюю версию​и выберите пункт​Готово​ качестве аргумента начальная_позиция​ вычислений получим:​ соответствующие столбцы таблицы.​ байт) не отличаются.​ .Value = arr​ т.д.).​ состояния написано, что​О других сочетаниях​

Как преобразовать текст в число в Excel

​. Неправда. Есть много​ отобразится количество строк.​ и смотрим на​ блоке инструментов​Мастер функций​ одна ячейка заполнена.​ Excel​Специальная вставка​

​, и Excel преобразует​ было передано число,​Функция имеет следующую синтаксическую​Вид таблицы данных:​ Если на компьютере​ End With End​Добавить специальную вставку. Здесь​ выделено 3 строки​ клавиш, чтобы выделить​ других приемов, чтобы​

​Так же вычисляется количество​ показатель​«Стили»​.​

1. ​Но бывают и ситуации,​Существует довольно большое количество​>​ ячейки.​ превышающее количество символов​ запись:​Для заполнения столбца «Наименование»​ используется двухбайтовый язык,​ Sub​ также применяется простое​ (ячейки).​ ячейки, смотрите в​ посчитать выделенные строки,​ столбцов, но в​«Количество»​
2. ​жмем на кнопку​Выделяем ячейку, в которую​ когда вы выделяете​ способов определения количества​Значения​Нажмите клавиши CTRL+1 (или​ в исходной строке.​=ПСТР(текст;начальная_позиция;число_знаков)​ используем следующую формулу:​
3. ​ каждый символ при​Чтобы он «заработал», нужно​ арифметическое действие. Но​Как преобразовать формат​ статье «Сочетание клавиш​ ячейки Excel. Было​ этом случае щелкните​в строке состояния,​«Условное форматирование»​ будет производиться вывод​ заполненные ячейки в​ строк. При их​.​+1 на Mac).​Если в качестве аргумента​Описание аргументов:​=ПСТР(A2;1;НАЙТИ(» «;A2))​ использовании ПСТРБ будет​ сохранить. Но книга​ вспомогательный столбец создавать​ данных из других​
4. ​ в Excel».​ бы желание, а​ область выделения строки,​ как и при​. Выбираем пункт​ готового итога подсчета​ строках, а отображение​ использовании применяются различные​Если указанные выше действия​ Выберите нужный формат.​ начальная_позиция было передано​текст – обязательный для​Функция НАЙТИ возвращает номер​
5. ​ считаться за два.​ Excel должна быть​ не нужно. В​ программ в формат​Четвертый способ​ способы найдем. )))​ которая находится слева​ решении проблемы первым​«Правила выделения ячеек»​ элементов. Жмем на​ количества на панели​ инструменты. Поэтому нужно​

​ не дали результатов,​Примечание:​ значение 1, а​ заполнения аргумент, принимающий​

Макрос «Текст – число»

​ Двухбайтовыми языками являются​ сохранена в формате​ отдельной ячейке написать​ числа Excel, чтобы​

​.​Один из способов​

​ от строки.​ способом. Именно это​. Далее открывается пункт​ кнопку​ состояния так и​ смотреть конкретный случай,​ попробуйте использовать этот​

​ Если вы по-прежнему видите​ аргумент число_знаков определен​ ссылку на ячейку​ « » в​ корейский, японский и​ с поддержкой макросов.​

​ цифру 1. Скопировать​ формулы считали такие​Посчитать количество строк​узнать количество выделенных​Пример результата:​

​ число и будет​ различных правил. Для​«Вставить функцию»​ не появляется. Это​ чтобы выбрать более​ метод, который подходит,​ формулы, которые не​ числом, которое равно​ с текстом или​

​ просматриваемой строке, который​

​ китайский.​Теперь возвращаемся на страницу​

​ ячейку в буфер​ числа, смотрите в​ можно с помощью​ строк – это​Если вы выберете всю​

​ или больше общему​ текстовую строку, заключенную​ принимается в качестве​Пример 1. В столбце​

​ с цифрами. Выделяем​ обмена (с помощью​

​ статье «Преобразовать текст​функции «Автосумма» в Excel​ настроить порядковые номера​ строку или столбец,​ строк, которые удовлетворяют​ выбираем пункт​ слева от строки​ функция просто отключена.​Самый простой способ решить​ преобразовать несколько столбцов​ возможно, включен параметр​ числу знаков в​ в кавычки, из​

exceltable.com>

Типы ссылок на ячейки в формулах Excel

​ ячейку. Это диапазон​ ссылки ведут себя​Деление​ при каких обстоятельствах​$C$5​и т.п. Давайте​Иногда, достаточно просто​ по столбцу. Отпускаем​​ статье «Как проверить​​ форматировании​​ функция ДЛСТР.​​ правилах Условного форматирования​ или ~​Коды символов​ в таблице символов​в​ во вторую –​ D2:D9​

Относительные ссылки

​ по-разному: относительные изменяются,​=А7/А8​ или действиях пользователя?​​) — она превратится​​ уже, наконец, разберемся​​ увеличить ширину столбца​​ мышь, все формулы​ формулы в Excel».​, т.д.​Скопируйте приведенную ниже таблицу​ описано в этой​Вопросительный знак, звездочку или​Excel.​​ на нужный символ​​Excel таблица символов​​ «2». Выделяем первые​​Воспользуемся функцией автозаполнения. Кнопка​​ абсолютные остаются постоянными.​​^ (циркумфлекс)​Решение заключается в использовании​ в​​ что именно они​​ или установить в​​ скопированы.​​В Excel можно​Для того, чтобы​ и вставьте ее​ статье.​ тильду.​Здесь приведены коды​

Смешанные ссылки

​ и в строке​. Эти символы не​ две ячейки –​ находится на вкладке​Все ссылки на ячейки​Степень​ функции​абсолютную​ означают, как работают​ ячейку «автоподбор» ширины.​Проставьте внизу таблицы​ написать сложную большую​​ таблица произвела необходимый​​ в ячейку A1​​Примечание:​Пример: по условию​​ часто используемых символов.​​ «Код знака» виден​ просто написаны как​​ «цепляем» левой кнопкой​​ «Главная» в группе​​ программа считает относительными,​​=6^2​​ДВССЫЛ (INDIRECT)​​и не будет​ и где могут​ Как быстро изменить​​ «ИТОГО» — автосумму.​​ формулу с многими​​ нам расчет, мы​​ на листе Excel.​​Мы стараемся как​​ поиска «ан06~?» будет​​В Excel можно​ код этого символа.​ буквы, а выполняют​ мыши маркер автозаполнения​ инструментов «Редактирование».​​ если пользователем не​

Абсолютные ссылки

​= (знак равенства)​, которая формирует ссылку​ меняться никак при​​ пригодиться в ваших​​ размер столбца, ячейки,​ Смотрите, как это​​ условиями (вложенными функциями).​​ должны написать формулу,​ Перетащите формулу из​ можно оперативнее обеспечивать​ найдено «ан06?»​ установить в ячейке​ Ставим в строке​

​ определенную функцию.​ – тянем вниз.​После нажатия на значок​ задано другое условие.​Равно​ на ячейку из​ любом копировании, т.е.​ файлах.​ строки, т.д., читайте​ сделать, в статье​ Как правильно написать​ по которой будет​ ячейки B2 B4,​​ вас актуальными справочными​​Предположим, что стоит задача​​ ссылку в виде​​ «из» — «Юникод»,​​Что в​​По такому же принципу​​ «Сумма» (или комбинации​​ С помощью относительных​​Меньше​​ текстовой строки. ​

​ долларами фиксируются намертво​Это обычные ссылки в​ в статье «Как​

​ «Закладка листа Excel​ такую формулу, смотрите​ произведен этот расчет.​​ чтобы увидеть длину​​ материалами на вашем​ подсчета ячеек, содержащих​​ символа на конкретную​​ если не срабатывает,​Excel означает символ​ можно заполнить, например,​ клавиш ALT+«=») слаживаются​ ссылок можно размножить​>​​Если ввести в ячейку​​ и строка и​ виде буква столбца-номер​ изменить ширину столбца,​ «Формулы»». Вот и​ в статье «Как​ Для начала определим,​ текста во всех​​ языке. Эта страница​​ все склонения слова​ строку в другой​​ то — «Кирилица​​. Например, если начинаем​ даты. Если промежутки​​ выделенные числа и​​ одну и ту​​Больше​​ формулу:​​ столбец:​​ строки (​ высоту строки в​​ получилась таблица ,​​ составлять формулы в​ в какой ячейке​ ячейках в столбце​ переведена автоматически, поэтому​ молоток (например, молотку,​​ таблице на другом​​ (дес.). Обязательно проверяйте,​ вводить в ячейку​ между ними одинаковые​ отображается результат в​

Действительно абсолютные ссылки

​ же формулу на​Меньше или равно​​=ДВССЫЛ(«C5″)​​Самый простой и быстрый​А1​ Excel» тут.​

​ которая считает, маленькая​ Excel для начинающих».​

​ должен стоять результат​

​ A.​

​ ее текст может​ молотком, молотка и​ листе. Например, есть​​ что написано и​​ данные со знака​ – день, месяц,​ пустой ячейке.​ несколько строк или​>=​=INDIRECT(«C5»)​ способ превратить относительную​,​В формулы можно​​ программка.​​Можно в таблице​ расчета. Затем выделим​Текстовые строки​ содержать неточности и​ пр.). Для решения​ таблица с общими​ ставьте нужное в​​ «равно» (=), то​​ год. Введем в​

​Сделаем еще один столбец,​

​ столбцов.​

planetaexcel.ru>

Преобразование чисел из текстового формата в числовой

​ в ячейку G3​ (a — b)​ тысяч vl =​ To 1 Step​ «тринадцать долларов»)) Edinicy(14)​

Выделите столбец

​ End Function​ = Right(n, 1)​ strЕдиницы As String,​При вводе номер, который​ ячейку формулу​«Надстройки»​щелкните стрелку рядом​Числа, хранящиеся как текст,​1. Курсы валют​ что курсы с​найти ‘,’ заменить​ в ситуации, когда​ и нажмем мастер​ * 100 If​ Mid(SumInt, shag, 1)​

Нажмите эту кнопку

​ -1 shag =​ = «четырнадцать «:​Можно написать алгори: Десятки​ strСотые As String​ начинается с нуля​=Сумма_прописью(A2)​​.​

​ с кнопкой​​ могут приводить к​ ​ подставляются только при​

Нажмите кнопку «Готово»

​ ЦБ РФ загружаются​ на ‘.’​ нужно отобразить числа​ функций нажав на​ c = 0​ txt = txt​​ shag + 1​

​ EdinicyPoslednie(14) = IIf(Valuta​ программы по-другому и​

Задайте формат

​ = «тридцать «:​

​ Dim Поз As​ — например, кода​

​, то в​

​Далее, в параметре настроек​Вставить​ непредвиденным результатам. Выделите​ 100% совпадении дат.​​ в текстовом формате​ ​а вообще, когда​​ в удобочитаемом виде​ ​ кнопку fx или​​ Then c =​ ​ & Sotni(vl) Case​

Использование формулы

​ Select Case x​ = 0, «четырнадцать​ еще сделать так,​

Вставьте новый столбец

​ n = Right(n,​ Integer strЧисло =​ продукта — Excel​ таком случае, любое​«Управление»​и выберите пункт​ ячейки и нажмите​2. Из-за разных​

Примените функцию ЗНАЧЕН

​ и по умолчанию​ число выгдядит как​​ либо объединить их​

​ комбинацию клавиш SHIFT+F3.​ CStr(c) + «0»​ 5 ‘ -​ Case 12 ‘​ евро», IIf(Valuta =​ чтобы она дописывала​ 1) Case «4»:​​ Format(Int(Число), «000000000000») ‘Миллиарды’​ ​ приведет к удалению​

Наведите указатель мыши

​ число, внесенное в​устанавливаем значение​Специальная вставка​ кнопку​ разделителей ЦБ РФ​ не могут использоваться​ число — а​ с текстом или​

Щелкните и перетащите вниз

​ В разделе категория​ d = «»​ десятки тысяч vl​ — сотни миллиардов​ 1, «четырнадцать рублей»,​ валюту суммы прописью.​ Десятки = «сорок​ Поз = 1​ ноль по умолчанию.​ ячейку A2 будет​«Надстройки Excel»​>​, чтобы выбрать​ & НБУ возникают​ в формулах.​​ не является числом​

​ символами.​ выберем текстовые и​​ If Valuta =​ ​ = Mid(SumInt, shag,​​ vl = Mid(SumInt,​ ​ «четырнадцать долларов»)) Edinicy(15)​​ Для этого создайте​ ​ «: n =​

Использование специальной вставки и умножения

​ strМиллиарды = Сотни(Mid(strЧисло,​ Если это не​ отображаться тут денежной​. Жмем на кнопку​Значения​ вариант преобразования. Если​ проблемы с дальнейшем​

1. ​Для одного значения​ помогает способ:​ALISA​ найдем необходимую функцию.​​ 1 Then d​

   ​ 1) If vl​ shag, 1) txt​

2. ​ = «пятнадцать «:​ Module2 и введите​
3. ​ Right(n, 1) Case​ Поз, 1)) strМиллиарды​ является вас не​
4. ​ суммой прописью.​​«Перейти…»​

   ​.​​ эта кнопка недоступна,​ ​ использованием курса UAH/USD​​ эту проблему решил​ ​Поместить 1 в​

5. ​: Уважаемые знатоки!!!! Знаю​​В окне аргументов зададим​

   ​ = «коп.» Else​​ = 1 And​ ​ = txt &​ EdinicyPoslednie(15) = IIf(Valuta​ в него следующий​ «5»: Десятки =​

6. ​ = strМиллиарды &​

   ​ устраивают, можно создать​Как видим, несмотря на​

Отключение зеленых треугольников

​.​Если указанные выше действия​ выполните указанные ниже​ при условии, что​​ (см. закладку «Продажи»​

​ любую пустую ячейку​​ вопрос банальный, но​ ​ следующие значения:​​ d = «цен.»​ ​ Mid(SumInt, shag +​​ Sotni(vl) Case 11​ ​ = 0, «пятнадцать​

support.office.com>

Как преобразовать 8-значное число в дату в Excel

Это очень распространенная ситуация, когда дата вводится как 8-значное число, например 10032021. Вам необходимо преобразовать ее в такой вид, который может распознать Excel (то есть, 20.03.2021). В этом случае простое изменение формата ячейки на «Дата» не пройдёт – в результате вы получите ##########.

Чтобы преобразовать это в дату, вам нужно будет использовать функцию ДАТА() в сочетании с функциями ПРАВСИМВ(), ЛЕВСИМВ() и ПСТР(). К сожалению, невозможно составить универсальную формулу, которая будет работать во всех сценариях, потому что исходное значение может быть введено самыми разными способами. Например:

Число	Формат	Дата
20032021	ддммгггг	20-мар-2021
20210320	ггггммдд
20212003	ггггддмм

Я постараюсь объяснить общий подход к преобразованию таких чисел в даты и приведу несколько примеров формул.

Для начала запомните порядок аргументов функции ДАТА():

=ДАТА(год; месяц; день)

Итак, что вам нужно сделать, так это извлечь год, месяц и день из исходного числа и передать их в качестве соответствующих аргументов функции ДАТА().

Например, давайте посмотрим, как преобразовать число 20032021 (хранящееся в ячейке A2) в 20.03.2021.

• Извлекаем год. Это последние 4 цифры, поэтому мы используем функцию ПРАВСИМВ, чтобы выбрать последние 4 символа: ПРАВСИМВ(A2; 4).
• Месяц. Это третья и четвёртая цифры, поэтому мы используем функцию ПСТР, чтобы получить их: ПСТР(A2;3;2). Здесь цифра 3 (второй аргумент) – это начальная позиция, а 2 – это количество символов, которые нужно извлечь.
• День. Это первые две цифры, поэтому у нас есть функция ЛЕВСИМВ, которая возвращает их: ЛЕВСИМВ(A2;2).

Наконец, вставьте указанные выше элементы в функцию ДАТА(), и вы получите формулу для преобразования числа в дату в Excel:

Следующий скриншот демонстрирует это и еще пару выражений в действии:

Обратите внимание на строку 5 на скриншоте выше:

 Исходное число (212003) содержит только два символа, обозначающих год (21). Итак, чтобы получить 2021 год, мы объединяем 20 и 21, используя следующую формулу:

Если вы не сделаете этого, функция ДАТА() возвратит 1921 год, что немного странно, как будто Microsoft все еще живет в 20-м веке 🙂

В том случае, если число представляет собой последовательность ДДММГГГ или ГГГММДД, то для конвертации в дату можно использовать функцию ТЕКСТ().

К примеру, число 20210320 можно преобразовать при помощи формулы

Сначала в нужных местах мы вставляем разделители — наклонную черту. Получаем текст 2021/03/20. А затем производим с ним математическую операцию: дважды умножаем на минус 1. Видя операцию умножения, Excel пытается превратить содержимое ячейки в число. И ему это успешно удается: получаем 44275. Осталось применить подходящий формат даты.

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

Итак, мы с вами рассмотрели как в Excel можно различными способами преобразовать текст в дату. Надеюсь, вам удалось найти метод по своему вкусу.

 Я благодарю вас за чтение и надеюсь вновь увидеть вас на нашем сайте.

Также рекомендуем:

Является ли текст данных ячейки или числа?

Есть несколько способов узнать, отформатирован ли номер или набор чисел в столбце или строке как текст в Excel.

Самый простой способ – выбрать ячейку, выбрать главное меню и в группе «Число» на ленте указать формат чисел, отображаемый в раскрывающемся списке.

Если в раскрывающемся списке отображается «текст», значит, ячейка отформатирована как текстовый. Если вы хотите выполнить численные расчеты на камеру с помощью Формулы Excel, вам нужно сначала преобразовать его.

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

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

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

Извлечение числа из текста

Эта возможность будет полезной, если из строки нужно извлечь число. Для этого ее нужно применить в комбинации с функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР. 

Так, чтобы достать последние 3 знака из ячейки A2, а результат вернуть, как число, нужно применить такую формулу.

=ЗНАЧЕН(ПРАВСИМВ(A2;3))

Если не использовать функцию ПРАВСИМВ внутри функции ЗНАЧЕН, то результат будет в символьном формате, что мешает выполнять любые вычислительные операции с получившимися цифрами.

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