Вычленение дат из ячейки с текстом

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычленение дат из ячейки с текстом (Формулы/Formulas)

Volkofx

Дата: Вторник, 18.08.2015, 17:57 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 71

Репутация: 1 ±

Замечаний: 20% ±

Excel 2013

Уважаемые господа! Посмотрел 11 страниц форума по интересующей меня информации и не нашел подходящего решения! К сожалению, само собой) Поэтому со спокойной душой прошу о помощи. Так вот, подскажите пожалуйста как решить небольшую задачу, имеется ячейка с расположенными в ней датами одна под другой, нужно вычленить последнюю дату, отображенную в ячейке. Сам хотел через правсимвол сделать, но столкнулся с проблемой, что в некоторых ячейках есть текст в конце ячейки, как это сделать? пример ниже. Спасибо!!

К сообщению приложен файл: __2003.xls (25.5 Kb)

Ответить

_Boroda_

Дата: Вторник, 18.08.2015, 18:14 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Вариант формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)

Код

=МАКС(ЕЧИСЛО(ПОИСК(ТЕКСТ(СТРОКА(A42000:A43000);"ДД.ММ.ГГГ");ТЕКСТ(A2;"ДД.ММ.ГГГ")))*СТРОКА(A42000:A43000))

К сообщению приложен файл: _2003_1.xls (27.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Volkofx

Дата: Вторник, 18.08.2015, 18:17 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 71

Репутация: 1 ±

Замечаний: 20% ±

Excel 2013

_Boroda_, как всегда оперативно, спасибо огромное.)

Ответить

Volkofx

Дата: Вторник, 18.08.2015, 18:19 | Сообщение № 4

Группа: Пользователи

Ранг: Участник

Сообщений: 71

Репутация: 1 ±

Замечаний: 20% ±

Excel 2013

Правда не понял сути вот этого "A42000:A43000", чтоб хоть что-то уяснить

Ответить

Udik

Дата: Вторник, 18.08.2015, 18:34 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1588

Репутация: 192 ±

Замечаний: 0% ±

Excel 2016 х 64

А время не надо отбирать? В примере отбирается: 12.08.2015 (10:00)
А у Бороды вроде нет: 12.08.2015

Я сам подумывал через поиск первой кирилл. литеры, но чет не смог.

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Ответить

Manyasha

Дата: Вторник, 18.08.2015, 18:42 | Сообщение № 6

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 898 ±

Замечаний: 0% ±

Excel 2010, 2016

Volkofx, это даты, которые Вы ищете в ячейках.

СТРОКА(A42000)=42000 или 27.12.14, а 43000 - это 22.09.2017.
В формуле перебираются все даты из этого диапазона и кусок ЕЧИСЛО(ПОИСК(...) возвращает есть ли такая дата в ячейке или нет.
Полученный результат (0 или 1) умножается на искомую дату.

ЯД: 410013299366744 WM: R193491431804

Ответить

Volkofx

Дата: Среда, 19.08.2015, 14:57 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 71

Репутация: 1 ±

Замечаний: 20% ±

Excel 2013

Цитата Manyasha, 18.08.2015 в 18:42, в сообщении № 6

СТРОКА(A42000)=42000 или 27.12.14, а 43000 - это 22.09.2017.
В формуле перебираются все даты из этого диапазона и кусок ЕЧИСЛО(ПОИСК(...) возвращает есть ли такая дата в ячейке или нет.
Полученный результат (0 или 1) умножается на искомую дату.

Спасибо, это понял и даже разобрался)

Но в целом возникла другая проблема вычленения дат, если ниже под датами много текста, то пишет 1900 год и тп. Если текст вручную убрать, то показывает нужную дату. если есть какие-то идеи, подскажите (Эта просьба всем участникам форума

)

К сообщению приложен файл: _-2-.xls (32.5 Kb)

Ответить

Pelena

Дата: Среда, 19.08.2015, 16:19 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19182

Репутация: 4420 ±

Замечаний: ±

Excel 365 & Mac Excel

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

Код

=МАКС(ЕЧИСЛО(ПОИСК(ТЕКСТ(СТРОКА(A42000:A43000);"ДД.ММ.ГГГ");ТЕКСТ(ЛЕВСИМВ(A2;255);"ДД.ММ.ГГГ")))*СТРОКА(A42000:A43000))

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

МВТ

Дата: Среда, 19.08.2015, 20:38 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 476

Репутация: 137 ±

Замечаний: 0% ±

Excel 2007

Могу предложить UDF [vba]

Код

Function LastDateInText(Text As String) As Date
Dim aDates As Object, aD As Object
Set aDates = CreateObject("VBScript.RegExp")
With aDates
       .Pattern = "\d\d.\d\d.\d\d\d\d"
       .Global = True
       .MultiLine = True
       Set aD = .Execute(Text)
End With
With aD
       For i = 0 To .Count - 1
           LastDateInText = Application.WorksheetFunction.Max(LastDateInText, CDate(.Item(i)))
       Next i
End With
End Function

[/vba]
это если надо вычислить наибольшую дату из стоящих вперемешку, а если просто последнюю, то вместо [vba]

Код

With aD
       For i = 0 To .Count - 1
           LastDateInText = Application.WorksheetFunction.Max(LastDateInText, CDate(.Item(i)))
       Next i

[/vba]
проще [vba]

Код

LastDateInText = aD.Item(aD.Count-1)

[/vba]

Сообщение отредактировал МВТ - Среда, 19.08.2015, 20:44

Ответить

krosav4ig

Дата: Четверг, 20.08.2015, 04:28 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

еще вариант, с учетом времени
формула

Код

=МАКС(ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&A2;"(";);")";);"
";ПОВТОР(" ";999));1+(СТРОКА(A$1:ИНДЕКС(A:A;ДЛСТР(A2&0)-ДЛСТР(ПОДСТАВИТЬ(A2;"
";))))-1)*999;999));))

UDF
[vba]

Код

Function LastDateInText(Text As String) As Date
     Dim s$
     With CreateObject("VBScript.RegExp")
         .Pattern = ".*(\d\d)\.(\d\d)\.(\d{4})( \(([\d:]{5}))?[^\d]?|[\s\S]+"
         .Global = True: .MultiLine = True
         Set f = .Execute(Text)
         s = "--{" & .Replace(Text, """$2-$1-$3 $5""" & ",") & "0}"
     End With
     With Application
          LastDateInText = .Max(.IfError(Evaluate(.Trim(s)), 0))
     End With
End Function

[/vba]
+числовой формат и условное форматирование

К сообщению приложен файл: _2003.xls (34.5 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Четверг, 20.08.2015, 04:37

Ответить

VitaliyPegushin

Дата: Среда, 07.10.2020, 14:43 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 42

Репутация: 0 ±

Замечаний: 20% ±

Excel 2016

krosav4ig, Подскажите пожалуйста как можно изменить данную Функцию для определения минимального значения

Ответить

VitaliyPegushin

Дата: Среда, 07.10.2020, 14:44 | Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 42

Репутация: 0 ±

Замечаний: 20% ±

Excel 2016

krosav4ig, и еще один момент, не считываются даты находящиеся в формате.01.01.20г. - где год указан двумя символами.

Ответить

VitaliyPegushin

Дата: Среда, 07.10.2020, 14:46 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 42

Репутация: 0 ±

Замечаний: 20% ±

Excel 2016

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

Ответить

Michael_S

Дата: Среда, 07.10.2020, 14:59 | Сообщение № 14

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

VitaliyPegushin, МВТ последний раз заходил на форум: Суббота, 21.01.2017, 11:53 а сегодня среда 07.10.2020...
И вообще, вся тема от 2015 г. Создайте лучше свою, ссылкой на эту тему.

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычленение дат из ячейки с текстом (Формулы/Formulas)

Страница 1 из 1
1