Има много начини да конвертирате дати, форматирани като текст, в действителни дати в Excel и този урок има за цел да ги проучи всички.
Когато импортирате данни в Excel, те могат да дойдат в много различни форми, които Excel не разпознава. Понякога, когато датите се импортират от външен източник, те ще бъдат форматирани като текст.
Също така е важно да знаете, че Excel прилага формати за дата въз основа на настройките на вашия системен регион. Така че, когато импортирате данни в Excel, които са дошли от друга държава, Excel може да не ги разпознае и да ги съхранява като текстови записи.
Ако имате този проблем, има много начини, които можете да използвате за преобразуване на текст в дата в Excel и ние ще ги разгледаме в този урок.
Методи за преобразуване на текст в дата
Ако вашият работен лист съдържа дати, форматирани като текст, вместо действителни дати, не е възможно да ги използвате в никакви изчисления. Така че трябва да ги преобразувате обратно към действителните дати.
Ако виждате датите си подравнени вляво, това означава, че са форматирани като текст, защото текстовете са подравнени вляво по подразбиране. И номерът и датите винаги са подравнени вдясно.
Има няколко различни начина, по които можете да конвертирате текст в дата в Excel, те са:
- Опция за проверка на грешка
- Функция Excel текст към колони
- Намерете и заменете
- Специален инструмент за поставяне
- Формула и функции на Excel
Преобразуване на текст към дата с помощта на опцията за проверка на грешки
Excel има вградена функция за проверка на грешки, която открива някои очевидни грешки във вашите данни. Ако открие някаква грешка, ще ви покаже малък зелен триъгълник (индикатор за грешка) в горния ляв ъгъл на клетката, който съдържа грешката. Ако изберете тази клетка, ще се появи предупредителен знак с жълт удивителен знак. Когато преместите курсора си върху този знак, Excel ще ви информира за възможния проблем с тази клетка.
Например, когато въведете годината в двуцифрения формат във вашата дата, Excel приема тази дата като текст и я съхранява като текст. И ако изберете тази клетка, ще се появи удивителен знак с предупреждение: „Тази клетка съдържа низ от дата, представен само с 2 цифри от годината“.
Ако клетките ви показват този индикатор за грешка, щракнете върху удивителния знак и той ще покаже няколко опции за преобразуване на дати, форматирани като текст, в действителни дати. Excel ще ви покаже опции да го преобразувате в 19XX или 20XX (19XX за 1915 г., 20XX за 2015 г.). Изберете подходящата опция.
След това текстът ще бъде преобразуван в правилен формат за дата.
Как да активирате опцията за проверка на грешки в Excel
Обикновено опцията Проверка на грешки е включена в Excel по подразбиране. Ако функцията за проверка на грешки не работи за вас, трябва да активирате проверката за грешки в Excel.
За да направите това, щракнете върху раздела „Файл“ и изберете „Опции“ в левия панел.
В прозореца с опции на Excel щракнете върху „Формули“ в левия панел и в десния панел активирайте „Активиране на проверка на грешки във фонов режим“ в секцията Проверка на грешки. И поставете отметка в „Клетки, съдържащи години, представени като 2 цифри“ в секцията Правила за проверка на грешки.
Преобразуване на текст към дата с помощта на функцията текст на Excel в колона
Текст в колона е страхотна функция в Excel, която ви позволява да разделяте данни на множество колони, а също така е мощен инструмент за преобразуване на текстови стойности в стойности за дата. Този метод разпознава няколко различни формата на данни и ги преобразува в правилен формат за дата.
Преобразуване на прости текстови низове в дати
Да кажем, че вашите дати са форматирани в текстови низове по следния начин:
Можете да използвате съветника за текст в колони, за да ги преформатирате бързо обратно към дати.
Първо изберете диапазона от текстови записи, които искате да преобразувате в дати. След това отидете на раздела „Данни“ в лентата и щракнете върху опцията „Текст в колони“ в групата Инструменти за данни.
Ще се появи съветникът за текст в колони. На стъпка 1 от съветника за текст в колона изберете опцията „Разграничени“ под Оригинален тип данни и щракнете върху „Напред“.
В стъпка 2 премахнете отметката от всички квадратчета „Разделители“ и щракнете върху Напред.
В последната стъпка на съветника изберете „Дата“ под Формат на данни за колона и изберете вашия формат на датата от падащия списък до „Дати“ и щракнете върху бутона „Край“. В нашия случай ние преобразуваме текстовите дати, представени като „01 02 1995“ (ден месец година), така че избираме „DMY“ от падащия списък „Дата:“.
Сега Excel преобразува вашите текстови дати в действителни дати и ги показва подравнени вдясно в клетките.
Забележка: Преди да започнете да използвате функцията Текст към колона, уверете се, че всичките ви текстови низове са в идентичен формат, в противен случай текстовете няма да бъдат преобразувани. Например, ако някои от вашите текстови дати са форматирани като формат месец/ден/година (MDY), докато други са ден/месец/година (DMY), и когато изберете „DMY“ в стъпка 3, ще получите неправилни резултати. Както е показано на снимката по-долу.
Преобразуване на сложен текстов низ в дата
Функцията Текст към колони е полезна, когато искате да конвертирате сложни текстови низове в дати. Позволява ви да използвате разделителите, за да идентифицирате къде вашите данни трябва да бъдат разделени и показани в 2 или повече колони. И комбинирайте разделените части на датите в цяла дата, като използвате функцията DATE.
Например, ако вашите дати се показват в текстови низове от няколко части, като това:
сряда, 01 февруари 2020 г
01 февруари 2020 г., 16.10 ч
Можете да използвате съветника Текст в колона, за да разделите информацията за деня, датата и часа, която е разделена със запетая, и да ги покажете в множество колони.
Първо изберете всички текстови низове, които искате да преобразувате в дати. Щракнете върху бутона „Текст в колони“ в раздела „Данни“. На стъпка 1 от съветника за текст в колона изберете опцията „Разграничени“ под Оригинален тип данни и щракнете върху „Напред“.
В стъпка 2 от съветника изберете разделителите, които съдържат вашите текстови низове, и щракнете върху „Напред“. Нашите примерни текстови низове, разделени със запетая и интервал – „Понеделник, 01 февруари 2015 г., 13:00“. Трябва да изберем „Запетая“ и „Интервали“ като разделители, за да разделим текстовите низове на множество колони.
В последната стъпка изберете формата „Общ“ за всички колони в секцията Преглед на данните. Посочете къде трябва да се вмъкнат колоните в полето „Дестинация“, ако не го направите, това ще презапише оригиналните данни. Ако искате да игнорирате част от оригиналните данни, щракнете върху нея в секцията Преглед на данни и изберете опцията „Не импортирайте колона (пропускане)“. След това щракнете върху „Край“.
Сега частите от датите (дни от седмицата, месец, година, час) са разделени в колони B, C, D, E, F и G.
След това комбинирайте частите от датата заедно с помощта на формула ДАТА, за да получите цялата дата.
Синтаксисът на функцията ДАТА в Excel:
=ДАТА(година, месец, ден)В нашия пример частите за месец, ден и година са съответно в колони C, D и E.
Функцията ДАТА разпознава само числа, а не текст. Тъй като нашите месечни стойности в колона C са текстови низове, трябва да ги преобразуваме в числа. За да направите това, трябва да използвате функцията MONTH, за да промените името на месеца на номера на месеца.
За да преобразувате името на месеца в числото на месеца, използвайте тази функция MONTH във функцията ДАТА:
=МЕСЕЦ(1&C1)Функцията MONTH добавя 1 към клетка C2, която съдържа името на месеца, за да преобразува името на месеца в съответния номер на месеца.
Това е функцията DATE, която трябва да използваме, за да комбинираме части от дата от различни колони:
=ДАТА(E1, МЕСЕЦ(1&C1),D1)
Сега използвайте манипулатора за запълване в долния ъгъл на клетката с формула и приложете формулата към колоната.
Преобразуване на текст към дата с помощта на метода за намиране и замяна
Този метод използва разделители за промяна на формата на текста към дати. Ако ден, месец и година във вашите дати са разделени с някакъв разделител, различен от тире (-) или наклонена черта (/), Excel няма да ги разпознае като дати и ще продължи и ще ги съхрани като текст.
За да отстраните този проблем, използвайте функцията за намиране и замяна. Чрез промяна на нестандартните разделители на периода (.) с наклонени черти (/) или тире (-), Excel автоматично ще промени стойностите на дати.
Първо изберете всички текстови дати, които искате да преобразувате в дати. В раздела „Начало“ щракнете върху бутона „Намиране и избор“ в най-десния ъгъл на лентата и изберете „Замяна“. Като алтернатива натиснете Ctrl+H за да отворите диалоговия прозорец Търсене и замяна.
В диалоговия прозорец „Намиране и замяна“ въведете разделителя, който текстът ви съдържа (в нашия случай точка (.) в полето „Намери какво“ и наклонена черта (/) или тире (-) в полето „Замяна с“). Щракнете върху бутона „Замяна на всички“, за да замените разделителите, и щракнете върху „Затвори“, за да затворите прозореца.
Сега Excel разпознава, че вашите текстови низове вече са дати и автоматично ги форматира като дати. Вашите дати ще бъдат подравнени вдясно, както е показано по-долу.
Преобразувайте текст в дата с помощта на специален инструмент за поставяне
Друг бърз и лесен начин за преобразуване на текстови низове в дати е чрез добавяне на 0 към текстовия низ с помощта на специална опция за поставяне. Добавянето на нула към стойност преобразува текста в сериен номер на датата, който можете да форматирате като дата.
Първо изберете празна клетка и я копирайте (изберете я и натиснете Ctrl + C да копирам).
След това изберете клетките, съдържащи текстовите дати, които искате да конвертирате, щракнете с десния бутон и изберете опцията „Специално поставяне“.
В диалоговия прозорец Специално поставяне изберете „Всички“ под секцията „Поставяне“, изберете „Добавяне“ в секцията „Операция“ и щракнете върху „OK“.
Можете също да извършвате други аритметични операции, изваждане/умножаване/разделяне на стойността в целевата клетка с поставената стойност (като умножаване на клетки с 1 или разделяне на 1 или изваждане на нула).
Когато изберете „Добавяне“ в операцията, той добавя „нула“ към всички избрани текстови дати, тъй като добавянето на „0“ не променя стойностите, ще получите серийния номер за всяка дата. Сега всичко, което трябва да направите, е да промените формата на клетките.
Изберете серийните номера и в раздела „Начало“ щракнете върху падащия списък „Формат на номера“ в групата Номер. Изберете опцията „Кратка дата“ от падащото меню.
Както можете да видите сега, числата са форматирани като дати и подравнени вдясно.
Преобразуване на текст в дата с помощта на формули
Има две функции, използвани основно за преобразуване на текст към дата: DATEVALUE и VALUE.
Използване на функция DATEVALUE на Excel
Функцията DATEVALUE на Excel е един от най-лесните начини за преобразуване на дата, представена като текст, в сериен номер на дата.
Синтаксисът на функцията DATEVALUE:
=DATEVALUE(текст_дата)Аргумент: дата_текст определя текстовия низ, който искате да скриете, или препращате към клетката, съдържаща текстови дати.
формулата:
=ДАТАСТОЙНОСТ(A1)Следната снимка илюстрира как функцията DATEVALUE обработва няколко различни формата за дата, които се съхраняват като текст.
Получихте серийните номера на датата, сега трябва да приложите формата на датата към тези числа. За да направите това, изберете клетките със серийни номера, след това отидете в раздела „Начало“ и изберете „Кратка дата“ от падащия списък „Формат на номера“.
Сега имате вашите форматирани дати в колона C.
Дори ако в текстовата ви дата (A8) няма част от годината, DATEVALUE ще използва текущата година от часовника на вашия компютър.
Функцията DATEVALUE ще преобразува само текстовите стойности, които изглеждат като дата. Той не може да преобразува текст, който прилича на число към дата, нито може да промени числова стойност към дата, за това ще ви трябва функцията VALUE на Excel.
Използване на функция VALUE на Excel
Функцията VALUE на Excel е в състояние да преобразува всеки текстов низ, който наподобява дата или число, в числова стойност, така че е наистина полезна, когато става въпрос за преобразуване на всяко число, а не само на дати.
Функцията VALUE:
=СТОЙНОСТ(текст)текст– текстовият низ, който искаме да преобразуваме или препращаме към клетката, съдържаща текстовия низ.
Примерната формула за преобразуване на текстовата дата:
=СТОЙНОСТ(A1)Формулата VALUE по-долу може да промени всички текстови низове, които изглеждат като дата, в число, както е показано по-долу.
Функцията VALUE обаче не поддържа всички типове стойности за дати. Например, ако датите използват десетични знаци (A11), той ще върне #VALUE! грешка.
След като имате серийния номер за вашата дата, ще трябва да форматирате клетката със серийния номер на датата, за да изглежда като дата, както направихме за функцията DATEVALUE. За да направите това, изберете серийните номера и изберете опцията „Дата“ от падащото меню „Формат на номера“ в раздела „Начало“.
Това е всичко, това са 5 различни начина, по които можете да конвертирате дати, форматирани като текст, в дати в Excel.