Вытащить дату из ячейки excel
Обновлено: 07.07.2024
Если Вам часто приходится вводить даты и время в ячейки, то Вам должна понравиться идея писать их сокращенно, без точек-дробей-двоеточий - просто как число. Чтобы в заданном диапазоне ячеек листа, например, число 250699 автоматически превращалось в 25.06.1999, а 1125 в 11:25.
Для этого щелкните по ярлычку листа, куда будут вводиться даты и время и выберите команду Исходный текст (Source Code). В открывшееся окно редактора Visual Basic скопируйте и вставьте следующий код:
Диапазоны A2:A10 и B2:B10 замените на свои области листа, куда будут подобным образом вводиться даты и время, соответственно.
Ссылки по теме
Макрос вообще не нужен если формат ячейки поставить "Дата", а значения вводить: 25-06-1999 или 25/06/1999 или 25,06,1999. Если набрать 25/6 или 25-06 . то год подставится текущий. Так в том и фишка, чтобы лишних знаков препинания не вводить. Обалдеть, класс. Спасибо огромное автору.И что поразительно у меня это работает. Не ставьте заранее для ячейки ввода формат даты. Иначе все введенное Excel автоматически преобразует в дату, а точнее - в свой специфический код даты. И макрос работает дальше уже с ним, а не с введенными вами цифрами. у меня не работает даже в скачанном примере. Если не менять формат ячейки, и ввести, к примеру, 110887, то ничего не меняется, цифры в таком формате и остаются. Если формат ячейки поменять на формат даты, то выдает 06.08.2203 Защита от макросов выключена? Файл - Параметры - Центр Управления Безопасностью - Параметры Центра Управления - Макросы - Разрешить все макросы. И перезагрузиться. Подскажите, пожалуйста, почему не работает на всю книгу? Если создаю для одного листа - все в порядке. Статью по созданию макросов прочитала, но все равно не получается. Потому, что этот макрос написан для листа и вставлять его надо в модули листов, а не в модуль ЭтаКнига. здравствуйте. макрос отличный на много сокращает количество нажатий кнопок на клавиатуре и соответственно времени рутины. при его использовании столкнулся с тем что при внесении даты(например 310113) если ошибочно внес не 6 знаков(например 3113), а другое количество выдает ошибку необходимости отладки кода макроса по 12 строке и макрос перестает работать.подскажите, что можно сделать ? В случае занесения не 6 символов ошибку выдавать не должно - в 10-й строке идет проверка на наличие именно 6 символов, и если количество введенных цифр не равно 6 - макрос заканчивает работу и ничего не происходит. Добрый день!
На работе этот макрос используем на нескольких компьтерах ошибка «runtimeerror ‘13’ typemismatch» появляется при ошибочном внесении например «1»или «131312».
После этого макрос перестает работать. Макрос включается только после перезапускаExcel.
Я не могу разобраться почему так происходит.
Подскажите,что нужно сделать что бы:
1. -макрос снова начинал работать без перезапуска Excel
2. - не появлялась на экране ошибка «runtimeerror ‘13’ typemismatch» которая приводит в панику некоторых пользователей.
3. - и посмотрите пожалуйста макрос предложенный Вами чуть чуть мной переделанный. Какие в нем недостатки. В VBA профан за ранее извеняюсь за назойливость .
(файл в котором у меня ошибка прикрепить не могу не нахожу кнопки "прикрепить файл"
А ее тут и нет - это комментарии к приему, а не форум. Лучше создайте тему на форуме и прикрепите ваш файл с макросом - а мы его покритикуем
А зависание макроса происходит, скорее всего, из за того, что вы в коде отключаете обработку событий Application.EnableEvents=False, а после возникновения ошибки она у вас обратно не включается.
Сразу Спасибо за сайт. оч помогает.
а как переделать макрос для времени, если вводится формат 21 . 15 (ну или любой другой знак, всякое бывает), ну а нужен обычный 21:15
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
12 строка подсвечивается для отладки, когда макрос перестаёт работать.
Это происходит, как я заметил, в случае:
1. Сортировки таблицы, несколько столбцов которой заполнены при помощи данного макроса датой и временем.
2. После ошибочного ввода в рабочем диапазоне макроса даты с разделителями, удаления ошибчной записи и последующей попытки заполнить ячейку не применяя разделители, при помощи макроса.
Добрый день! не в коем случаи не хочу обидеть автора статьи, но у меня есть один трюк именно для того, чтобы лишний раз не нажимать shift и какой либо знак. он делается очень легко и для даты и для времени, без всякого там кода VB, сам я ламер в VB поэтому вечно ищу более легкие пути, так вот я делаю так:
для даты:
в формате ячейки -> все форматы 00\.00\.0000
пример:
12122012 -> 12.12.2012
для времени -> все форматы 00\:00
пример:
1212 -> 12:12
после слеша влево можно поставить любой знак, который предпочтителен для Вас.
Надеюсь Вам понравится такая идея.
если у кого будут вопросы или захотите сказать спасибо, я в facebook Parviz Ruziev
Добрый день, описанный вами прием всего лишь придаст форму но не изменит сути, Excel не будет воспринимать введенное значение как дату.
К сожалению, с отофрматированными вашим способом датами нельзя работать. Попробуйте вычесть из одной даты другую, чтобы посчитать количество дней - и получите ерунду. И ни одна функция из категории "Даты и время" с вашими "датами" работать не будет. Так что не советовал бы идти таким путем, при всем уважении.Посмею предложить свой вариант. )))
В формате ячейки (у меня это D2) выставляем формат типа:
00"."00"."0000
На проверку вводимых данных задаем условия проверки:
тип данных – другой; и на соответствие формуле в соответствующей ячейке (у меня это R2).
Формула такого вида:
=ЕСЛИ(ИЛИ(И(ДЛСТР(D2)=7;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;2;2);"00";))<=12);И(ДЛСТР(D2)=8;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))<=12;ИЛИ(ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=1;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=3;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=5;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=7;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=8;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=10;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=12;);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=31);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=4;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=6;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=9;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=11);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=30);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=2;ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=29);ИСТИНА;ЛОЖЬ)));ИСТИНА;ЛОЖЬ)
Не пойму почему так отображает, в выше приведенной формуле смайлики заменить на ")" без кавычек естественно.
Соответственно дата вводится без каких-либо посторонних знаков, только цифры. Формула проверяет что бы введенный диапазон был между 01.01.1970 и 31.12.2020, при вводе большей или меньшей даты – выдает ошибку, при попытке ввести 13 и т.д. месяц – выдает ошибку, при попытке ввести более 31 дня (в январе, марте, мае, июле, августе, октябре, декабре) – выдает ошибку, при попытке ввести более 30 дней (в апреле, июне, сентябре, ноябре) – выдает ошибку, при попытке ввести более 29 дней в феврале – выдает ошибку. Осталось добить проверку високосных лет. )))))
Ну и перевести это все безобразие в дату тоже не проблема, у меня в ячейке I2 идет подсчет даты от введенной в ячейку D2 плюс 2 месяца вот такой формулой:
=ЕСЛИ(ЕПУСТО(D2);""; ЕСЛИ(ДЛСТР(D2)=7; ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);(СЦЕПИТЬ("0";ЛЕВСИМВ(D2))));2); ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));2)))
Сразу отвечу на вопрос зачем так все усложнять – просто задача стояла сделать без макросов.
а можно чтоб при вводе числа например 5 появлялась дата не текущего а конкретного месяца или хотябы прошлого месяца текущего года, я например делаю отчёт за прошлый месяц и чтоб не вводить значения года и месяца а только день А как сделать так чтобы дата и время было в одной ячейке Принес с работы файл созданный в офисе 2007 который работает хорошо (именно с этим макросом), но дома на офисе 2013 не хочет пишет Could not load an object because it is not available on this machine.(Не удалось загрузить объект, потому что он не доступен на этой машине.) В чем проблема? А как сделать чтобы выводился формат часы:минуты:секунды? Или просто минуты:секунды
P.S. и год как убрать? Дмитрий, посмотрите вот эту статью - многое станет понятнее по датам и времени.
Подскажите пожалуйста, если мне нужно даты во многих столбцах (B; N;O; S) начиная с 3 строки указывать, как это прописать в макросе?
Прошу прощения за вопрос, в VBA новичок. К сожалению, нет времени чтобы изучить возникшую проблему, необходимо срочно сделать форму отчета кассира КМ-6 (форма типовая, скачана из альбома унифицированных форм в Консультанте). Проблема в том, что в этой форме графы "дата составления" и "время работы" представляют собой объединение нескольких ячеек и изменение диапазона указывает на неправильное событие, если я правильно поняла. Какой должен быть макрос, чтобы эта проблема была устранена?
К сожалению, не удается вставить скрин-шот для наглядности.
Заранее благодарна.
Добрый день. Скажите пожалуйста, как можно установить автоматический ввод даты и времени в нескольких столбцах сразу (с помощью макроса, функция Range("F3:F50200")
For Each cell In Target 'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("F3:F50200" ) Is Nothing Then 'если изменененная ячейка попадает в диапазон A2:A100
With cell.Offset(0, 1) 'вводим в соседнюю справа ячейку дату
.Value = Now
.EntireColumn.AutoFit 'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
Next cell
End Sub
Excel не распознает ввод даты на английском. Подскажите, что делаю не так ? )
Если ввожу 10янв14, преобразует в дату 10.01.2014 , формат ячейки становится Custom (все ОК)
Если ввожу 10Jan14, остается в формате General
Пробовал заранее ставить формат ячейки "Date", все равно остается текст 10Jan14
Добрый день, спасибо за сайт и за эту статью. Воспользовалась вашем макросом и вставила его в свой файл "Табель уч.раб. вр." все получилось. Этим файлом еще пользуются руководители подразделений и хотелось бы для них вставить "подсказку" (скриншот как у вас в начале статьи) как всплывающую подсказку, но не знаю как вставлять анимированные картинки, пожалуйста, помогите сделать.
Добрый день, у некоторых пользователей при вводе данных в разные ячейки макрос выдает ошибку Compile erorr: Synteax erorr
Выскакивать окно Visual Basic при любом редактировании значений.
Дико неудобно, при введении каждого значения приходится закрывать окно VB, дважды нажимая на кнопки.
Один пользователь даже пробовал менять дистрибутивы винды и офиса, переустанавливать – везде одно и то же.
Судя по ошибке у стоит библиотека или скрипт, который позволяет выполнять эту процедуру.
Приветствую! Вопрос в следующем, подскажите, какой должен быть код, чтобы можно было редактировать в дальнейшем дату в введенной ячейке? Т.е я ввел 150216, он мне автоматом перевел в 15.02.2016, супер, но, если ошибся на день, пытаешься отредактировать на 14.02.2016, дата меняется на 20.04.1949 и в дальнейшем выскакивает ошибка "Run-time Error 13. Type mismatch". После этого код больше не работает и необходимо закрывать и открывать заново документ. Это первый момент.
Спасибо. Код золотой и очень полезен в работе, но хочется чтобы не было таких ошибок т.к будет активно использоваться в работе. Буду очень благодарен.
Николай! Добрый вечер! Подскажите начинающему плиз. Мне нужно тоже самое, что и в Вашем примере, только формат не часы:минуты:секунды, а просто минуты (единицы минут, несколько):секунды. Всего 3 цифры. Например, 3:40. Никак не получается сделать. Моя конечная цель - автоматизация оценивания учеников по 5 бальной системе по результатам забегов на физкультуре. Может, как-то изменить макрос?
Ребята или Николай, кто-нибудь в общем)) Помогите
Замечательный код, но вот вопрос:
Если я хочу внедрить этот код для подобного способа ввода даты в колонку где у меня уже был изначально какой-то формат ячеек к сожалению, и на какой бы другой формат я их не менял, результата нет положительного, тогда как я могу вернуть "никакой формат ячеек" или может мне что то в коде вашем поменять можно?
ЗЫ более того, я ввожу эти 6 цифр не в саму ячейку, у меня для этого есть код, с помощью которого вылазит диалоговое окно с просьбой ввести данные, этот код ссылается на нужную ячейку где я собственно и хотел бы увидеть дату. Но опять же эти 6 цифр я ввожу в диалоговое окно, для которого написал код.
Если не понятно что я имею в виду говоря про код и диалоговое окно, то вот немного подробнее:
Я настроил свою таблицу для работы максимально удобно, чтобы не возить мышкой и кликать триста раз по ячейкам, я уже в исходном коде листа прописал несколько полезных штук, таким образом, что мне надо только в начале ввести в определенную ячейку данные, после чего запускается код и цепь разных действий в виде диалоговых окон типа:
Диалоговое окно "Введите дату" - это как раз первый запрос - и мне она нужна не текущая, а та что я сам впишу, ибо это дэдлайн проекта. Текущая дата вводится автоматом в другом месте, это я уже благодаря кстати Николаю реализовал пару-тройку лет назад, за что большое спасибо и не только за это)) В общем мне пока приходится вводить дату полностью или писать типа "9 мая" чтобы быстрее было, но мне гораздо удобнее было бы все вводить на нампаде, раз 6 цифр ввел нажал Enter, вылазит следующее диалоговое окно "введите бюджет" ну и так далее, нампад - энтер, нампад-энтер — быстро и удобно, и не надо бегать по строке по разным ячейкам глазами выискивать, тем более риск ввести не туда, а колонок много и перепутать легко.
Это я для полной картины просто написал, чтобы понимали)) а то мало ли, может если способ ввода реализуется с помощью вот таких диалоговых окон, то этот ваш код для ввода даты не годится.
Заранее благодарю всех и Николая в особенности.
КСТАТИ! Раз у меня есть текущая дата, то может вместо ввода 6 цифр, можно было бы написать код таким образом чтобы при вводе например цифры 3 на столько увеличивалась текущая дата и вводилась бы как раз в ячейку дедлайна в виде даты? если так можно реализовать код, то это была бы просто бомба))
Например у меня есть текущая дата 6 мая в ячейке A22, вылазит диалоговое окно с просьбой ввести число на которое будет увеличена дата ячейки A22 и введена в ячейку K22 с учетом прибавления того числа что я ввел в диалоговое окно, например я ввел цифру 3 и в ячейке K22 появилась дата 9 мая, то есть код поссчитал 6+3=9 и ввел нужные данные в ячейку K22 в формате даты "9 мая" или "09.05.2017" - без разницы
Как считаете можно такое провернуть??
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071
Извлекать дату только из ячеек даты и времени с помощью функции INT или TRUNC
Обычно в Excel datetime распознается как серийный номер, целая часть - это порядковый номер даты, а дробная часть - это порядковый номер времени. Функции INT и TRUNC могут помочь извлечь только целую часть из ячеек datetime.
1. Введите или скопируйте любую из приведенных ниже формул в пустую ячейку, в которую вы хотите поместить результат:
Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и временная часть значения будет отброшена, как показано на следующем снимке экрана:
2. Затем вы должны изменить формат ячеек формулы на Время формат, как показано ниже:
Извлекать дату только из ячеек даты и времени с функцией ДАТА
В Excel функция ДАТА также может помочь вам напрямую извлечь только дату из ячеек datetime.
=DATE(YEAR(datetime),MONTH(datetime),DAY(datetime))- datetime : Ячейка содержит дату и время, из которых вы хотите извлечь только дату.
- YEAR() , MONTH() , DAY() : Эти три аргумента используются для извлечения отдельного номера года, месяца и дня из ячейки даты.
- DATE : Эта функция используется для объединения чисел года, месяца и дня из отдельных ячеек в действительную дату.
Используйте приведенную ниже формулу в пустую ячейку:
Затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите заполнить формулой, и все даты были извлечены из ячеек datetime, см. Снимок экрана:
Используемые относительные функции:
- INT :
- Возвращает целую часть значения.
- TRUNC :
- Эта функция используется для возврата усеченного числа на основе количества цифр.
- ДАТА:
- Он возвращает действительную дату на основе столбцов года, месяца и дня.
Статьи с относительной датой:
- Расчет дней, часов и минут между двумя датами в Excel
- Предположим, у вас есть два столбца ячеек даты и времени, и теперь вы хотите вычислить разницу в днях, часах и минутах между этими двумя ячейками даты и времени, как показано на следующем снимке экрана.
- Сравните две даты по месяцам и годам только в Excel
- Если у вас есть два списка дат, теперь вам нужно сравнивать даты только по месяцам и годам и игнорировать значение дня, если они имеют одинаковый месяц и год, результат должен отображаться как True, в противном случае должен быть False, как показано ниже показан снимок экрана. Как сравнивать даты только с месяцем и годом, но без учета дня в Excel?
- Vlookup между двумя датами и возврат соответствующего значения в Excel
- Предположим, у вас есть отчет о расписании некоторых проектов, теперь вы хотите получить конкретный проект на заданное время, которое находится между двумя заданными датами расписания.
- Список всех дат между двумя датами в Excel
- В некоторых случаях, если у вас есть конкретная дата начала и дата окончания, вам может потребоваться перечислить все даты между этими двумя заданными датами в Excel.
- Разделить дату и время из ячейки на две отдельные ячейки в Excel
- Например, у вас есть список данных, смешанных с датой и временем, и вы хотите разделить каждую из них на две ячейки, одна - это дата, а другая - время, как показано ниже.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
В этой статье говорится об извлечении части даты из ячейки в Excel. Например, вы хотите извлечь 2016 год, месяц 6 и день 1 отдельно от заданной даты 6/1/2016, как показано ниже.
Извлечь часть даты из ячейки с помощью Kutools for Excel
Извлечь часть даты из ячейки с формулой
Чтобы извлечь год, месяц, день или день недели, номер недели из заданной даты, вы можете использовать следующие формулы:
| Год извлечения | = ГОД (A2) |
| Месяц извлечения | = МЕСЯЦ (A2) |
| День извлечения | = ДЕНЬ (A2) |
| Извлечь будний день | = WEEKDAY (A2) или = WEEKDAY (A2,1) |
| Извлечь номер недели | = НЕДЕЛ. (A2) |
Выберите одну из приведенных выше формул, введите ее в пустую ячейку и нажмите клавишу Enter, чтобы получить извлеченную часть, перетащите дескриптор автозаполнения на ячейки, необходимые для применения формулы. Смотрите скриншот:
Извлечь часть даты из ячейки с помощью Kutools for Excel
Работы С Нами Kutools for ExcelАвтора Применить форматирование даты может помочь вам быстро преобразовать данные даты в год, месяц, день или другое форматирование даты по вашему желанию.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите даты и нажмите Kutools > Формат > Применить форматирование даты. Смотрите скриншот:
2. в Применить форматирование даты В диалоговом окне выберите форматирование даты на левой панели, в то же время вы можете предварительно просмотреть преобразованный результат на правой панели. Смотрите скриншот:
3. Нажмите Ok or Применить, выбранные даты были преобразованы в выбранный вами формат даты.
ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.
В этом руководстве мы обсудим синтаксис и особенности функции Excel ПСТР (в английской версии – MID), а затем вы узнаете несколько вариантов творческого её использования для выполнения сложных задач.
Cинтаксис.
Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.
Функция Excel ПСТР имеет следующие аргументы:
ПСТР(текст; начальная_позиция; количество_знаков)
Где текст - это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество их для извлечения.
Все 3 аргумента обязательны.
Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:
Результат может выглядеть примерно так:
5 вещей, которые вы должны знать о функции Excel ПСТР
Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок.
При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.
Как извлечь имя и фамилию.
Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.
Получаем имя.
Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены интервалом, вы можете извлечь имя, используя следующую формулу:
ПОИСК используется для сканирования исходного значения на предмет пробела (" ") и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.
Получаем фамилию.
Чтобы извлечь фамилию из A2, используйте эту формулу:
Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:
Как выделить подстроку между двумя разделителями.
Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?
Технически задача сводится к определению позиций двух пробелов в исходном тексте, и вы можете сделать это следующим образом:
- Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (" "), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (" "; A2) +1
- Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го: ПОИСК (" "; A2, ПОИСК (" "; A2) +1)
Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы:
ПОИСК(" "; A2; ПОИСК(" "; A2) +1) - ПОИСК(" "; A2)
Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:
На следующем скриншоте показан результат:
Аналогичным образом вы можете извлечь текст между любыми другими разделителями:
ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) - ПОИСК( разделитель ; строка ) -1)
Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение:
На следующем рисунке эта формула используется для извлечения из адреса названия города, и она отлично справляется со своей задачей:
Как получить N-е слово из текста.
Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:
- ДЛСТР - чтобы получить общую длину.
- ПОВТОР - повторение определенного знака заданное количество раз.
- ПОДСТАВИТЬ - заменить один символ другим.
- ПСТР - извлечь подстроку.
- СЖПРОБЕЛЫ - удалить лишние интервалы между словами.
Общая формула выглядит следующим образом:
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; " "; ПОВТОР (" "; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))
- Строка - это исходный текст, из которого вы хотите извлечь желаемое слово.
- N – порядковый номер слова, которое нужно получить.
Например, чтобы вытащить второе слово из A2, используйте это выражение:
Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:
Как работает эта формула?
По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:
- ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;" ";ПОВТОР(" ";ДЛСТР($A$2)))
Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.
- Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
- Количество букв и цифр для извлечения (второй аргумент) - самая простая часть - вы просто берете общую первоначальную длину: ДЛСТР(A2).
- Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))
Следующий рисунок демонстрирует улучшенный вариант (2) в действии:
Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))
Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.
Извлекаем слово, содержащее определенный символ.
В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , " ", ПОВТОР(" ", 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , " ", ПОВТОР(" ", 99))) - 50), 99))
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)); МАКС(1;НАЙТИ("$";ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)))-50);99))
Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.
Теперь разберём пошагово:
Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.
НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.
МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() - 50 будет отрицательным числом, а МАКС(1, ПОИСК() - 50) заменяет его на 1.
С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.
Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.
Как заставить ПСТР возвращать число?
Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.
Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.
Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте:
На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:
Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:
Вот как можно использовать функцию ПСТР в Excel.
Как извлечь текст из ячейки с помощью Ultimate Suite
Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.
Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :
Теперь вы выбираете исходные ячейки, и какие бы сложные строки они ни содержали, извлечение из них подстроки сводится к этим двум простым действиям:
- Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
- Щелкните Вставить результаты(InsertResults). Готово!
Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.
Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность - он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.
В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:
Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? ;)
Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст».
Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Как быстро посчитать количество слов в Excel - В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… Как быстро извлечь число из текста в Excel - В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… Как быстро сосчитать количество символов в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем… Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на… Как преобразовать текст в число в Excel — 10 способов. - В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…Читайте также: