Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Вычленение дат из ячейки с текстом - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычленение дат из ячейки с текстом (Формулы/Formulas)
Вычленение дат из ячейки с текстом
Volkofx Дата: Вторник, 18.08.2015, 17:57 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 40% ±

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

Автор - Volkofx
Дата добавления - 18.08.2015 в 17:57
_Boroda_ Дата: Вторник, 18.08.2015, 18:14 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15978
Репутация: 6209 ±
Замечаний: 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
 
Ответить
СообщениеВариант формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=МАКС(ЕЧИСЛО(ПОИСК(ТЕКСТ(СТРОКА(A42000:A43000);"ДД.ММ.ГГГ");ТЕКСТ(A2;"ДД.ММ.ГГГ")))*СТРОКА(A42000:A43000))

Автор - _Boroda_
Дата добавления - 18.08.2015 в 18:14
Volkofx Дата: Вторник, 18.08.2015, 18:17 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 40% ±

Excel 2013
_Boroda_, как всегда оперативно, спасибо огромное.)
 
Ответить
Сообщение_Boroda_, как всегда оперативно, спасибо огромное.)

Автор - Volkofx
Дата добавления - 18.08.2015 в 18:17
Volkofx Дата: Вторник, 18.08.2015, 18:19 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 40% ±

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

Автор - Volkofx
Дата добавления - 18.08.2015 в 18:19
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
 
Ответить
СообщениеА время не надо отбирать? В примере отбирается: 12.08.2015 (10:00)
А у Бороды вроде нет: 12.08.2015

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

Автор - Udik
Дата добавления - 18.08.2015 в 18:34
Manyasha Дата: Вторник, 18.08.2015, 18:42 | Сообщение № 6
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 894 ±
Замечаний: 0% ±

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

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


ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеVolkofx, это даты, которые Вы ищете в ячейках.

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

Автор - Manyasha
Дата добавления - 18.08.2015 в 18:42
Volkofx Дата: Среда, 19.08.2015, 14:57 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 1 ±
Замечаний: 40% ±

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


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

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


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

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

Автор - Volkofx
Дата добавления - 19.08.2015 в 14:57
Pelena Дата: Среда, 19.08.2015, 16:19 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 17026
Репутация: 3674 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Формула работает, если длина текста в ячейке меньше 255 символов.
Если даты всегда в начале, то можно просто взять первые 255 символов, остальное обрезать
Код
=МАКС(ЕЧИСЛО(ПОИСК(ТЕКСТ(СТРОКА(A42000:A43000);"ДД.ММ.ГГГ");ТЕКСТ(ЛЕВСИМВ(A2;255);"ДД.ММ.ГГГ")))*СТРОКА(A42000:A43000))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеФормула работает, если длина текста в ячейке меньше 255 символов.
Если даты всегда в начале, то можно просто взять первые 255 символов, остальное обрезать
Код
=МАКС(ЕЧИСЛО(ПОИСК(ТЕКСТ(СТРОКА(A42000:A43000);"ДД.ММ.ГГГ");ТЕКСТ(ЛЕВСИМВ(A2;255);"ДД.ММ.ГГГ")))*СТРОКА(A42000:A43000))

Автор - Pelena
Дата добавления - 19.08.2015 в 16:19
МВТ Дата: Среда, 19.08.2015, 20:38 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 136 ±
Замечаний: 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
 
Ответить
СообщениеМогу предложить 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:38
krosav4ig Дата: Четверг, 20.08.2015, 04:28 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2323
Репутация: 976 ±
Замечаний: 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
 
Ответить
Сообщениееще вариант, с учетом времени
формула
Код
=МАКС(ЕСЛИОШИБКА(--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&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]
+числовой формат и условное форматирование

Автор - krosav4ig
Дата добавления - 20.08.2015 в 04:28
VitaliyPegushin Дата: Среда, 07.10.2020, 14:43 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
krosav4ig, Подскажите пожалуйста как можно изменить данную Функцию для определения минимального значения
 
Ответить
Сообщениеkrosav4ig, Подскажите пожалуйста как можно изменить данную Функцию для определения минимального значения

Автор - VitaliyPegushin
Дата добавления - 07.10.2020 в 14:43
VitaliyPegushin Дата: Среда, 07.10.2020, 14:44 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - VitaliyPegushin
Дата добавления - 07.10.2020 в 14:44
VitaliyPegushin Дата: Среда, 07.10.2020, 14:46 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - VitaliyPegushin
Дата добавления - 07.10.2020 в 14:46
Michael_S Дата: Среда, 07.10.2020, 14:59 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 372 ±
Замечаний: 0% ±

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

Автор - Michael_S
Дата добавления - 07.10.2020 в 14:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычленение дат из ячейки с текстом (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!