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

Вход

Регистрация

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

 

= Мир MS Excel/Как формулой извлечь часть текста, по определенному критерию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как формулой извлечь часть текста, по определенному критерию (Формулы/Formulas)
Как формулой извлечь часть текста, по определенному критерию
Grell Дата: Четверг, 20.04.2017, 13:31 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
Здравствуйте, дорогие форумчане.
Хочу обратится к вам - с непростым вопросом.

У меня на листе в столбце - есть текст включающий слова tks05-05, tks1-4, tks05-2, tks05-8
Обязательное условие - слово tks в каждой - встречается лишь один раз.

Как формулой - найти в ячейке слово начинающееся с "tks" и вынести из него ту часть текста, которая находится между символами "-" и "," ?
То есть в случае со словами tks05-05, tks1-4, tks05-2, tks05-8 - это будет 05, 4, 2, 8.
К сообщению приложен файл: 3548.xls (29.5 Kb)
 
Ответить
СообщениеЗдравствуйте, дорогие форумчане.
Хочу обратится к вам - с непростым вопросом.

У меня на листе в столбце - есть текст включающий слова tks05-05, tks1-4, tks05-2, tks05-8
Обязательное условие - слово tks в каждой - встречается лишь один раз.

Как формулой - найти в ячейке слово начинающееся с "tks" и вынести из него ту часть текста, которая находится между символами "-" и "," ?
То есть в случае со словами tks05-05, tks1-4, tks05-2, tks05-8 - это будет 05, 4, 2, 8.
Автор - Grell
Дата добавления - 20.04.2017 в 13:31
китин Дата: Четверг, 20.04.2017, 13:44 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
так?
Код
=ЕСЛИОШИБКА(ПСТР(ПСТР(D8;ПОИСК("-";D8)+1;99);1;ПОИСК(",";ПСТР(D8;ПОИСК("-";D8)+1;99))-1);0)
К сообщению приложен файл: Grell.xls (31.0 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Четверг, 20.04.2017, 13:45
 
Ответить
Сообщениетак?
Код
=ЕСЛИОШИБКА(ПСТР(ПСТР(D8;ПОИСК("-";D8)+1;99);1;ПОИСК(",";ПСТР(D8;ПОИСК("-";D8)+1;99))-1);0)
Автор - китин
Дата добавления - 20.04.2017 в 13:44
Grell Дата: Четверг, 20.04.2017, 14:30 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
китин, нет - это немного не то.

Ваша формула - не задает критерий поиска - слово "tks".
Поиск начинается именно с этого слова.

А у вас - критерий поиска - это символ "-".

То есть - если поставить символ - где-нибудь в районе начала текстовой строки, то ваша формула - отхватит кусок текста - от символа "-" до ближайшего символа ",".
К сообщению приложен файл: 4233400.xls (30.5 Kb)


Сообщение отредактировал Grell - Четверг, 20.04.2017, 14:31
 
Ответить
Сообщениекитин, нет - это немного не то.

Ваша формула - не задает критерий поиска - слово "tks".
Поиск начинается именно с этого слова.

А у вас - критерий поиска - это символ "-".

То есть - если поставить символ - где-нибудь в районе начала текстовой строки, то ваша формула - отхватит кусок текста - от символа "-" до ближайшего символа ",".
Автор - Grell
Дата добавления - 20.04.2017 в 14:30
китин Дата: Четверг, 20.04.2017, 14:46 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Цитата Grell, 20.04.2017 в 14:30, в сообщении № 3 ()
критерий поиска - слово "tks".

после слова "tks" насколько я понял ВСЕГДА идет "-". я к этому и привязался.как в примере так и сделал. никде про
Цитата Grell, 20.04.2017 в 14:30, в сообщении № 3 ()
если поставить символ - где-нибудь в районе начала текстовой строки

не сказано :p

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Цитата Grell, 20.04.2017 в 14:30, в сообщении № 3 ()
критерий поиска - слово "tks".

после слова "tks" насколько я понял ВСЕГДА идет "-". я к этому и привязался.как в примере так и сделал. никде про
Цитата Grell, 20.04.2017 в 14:30, в сообщении № 3 ()
если поставить символ - где-нибудь в районе начала текстовой строки

не сказано :p
Автор - китин
Дата добавления - 20.04.2017 в 14:46
buchlotnik Дата: Четверг, 20.04.2017, 14:55 | Сообщение № 5
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
можно UDF-кой [vba]
Код
Function tks$(t$)
    With CreateObject("VBScript.RegExp")
        .Pattern = "(tks\d+-)(\d+)"
        tks = 0
        If .test(t) Then tks = .Replace(.Execute(t)(0), "$2")
    End With
End Function
[/vba]
К сообщению приложен файл: 3159376.xls (40.0 Kb)
 
Ответить
Сообщениеможно UDF-кой [vba]
Код
Function tks$(t$)
    With CreateObject("VBScript.RegExp")
        .Pattern = "(tks\d+-)(\d+)"
        tks = 0
        If .test(t) Then tks = .Replace(.Execute(t)(0), "$2")
    End With
End Function
[/vba]
Автор - buchlotnik
Дата добавления - 20.04.2017 в 14:55
Nic70y Дата: Четверг, 20.04.2017, 14:57 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 9122
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(D8;ПОИСК("tks";D8)+3;15);"-";ПОВТОР(" ";15));15;99);",";ПОВТОР(" ";99));99));0)

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(D8;ПОИСК("tks";D8)+3;15);"-";ПОВТОР(" ";15));15;99);",";ПОВТОР(" ";99));99));0)
Автор - Nic70y
Дата добавления - 20.04.2017 в 14:57
Grell Дата: Четверг, 20.04.2017, 14:57 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
китин, нет вы правильно поняли - после слова "tks" ВСЕГДА идет "-".

Но просто слово "tks" - встречается не всегда, и символ "-" встречается много где еще.
Вот смотрите, что получается по этой формуле.

Я подсветил проблемные результаты в ячейках - желтым цветом.
К сообщению приложен файл: 546456.xls (31.5 Kb)
 
Ответить
Сообщениекитин, нет вы правильно поняли - после слова "tks" ВСЕГДА идет "-".

Но просто слово "tks" - встречается не всегда, и символ "-" встречается много где еще.
Вот смотрите, что получается по этой формуле.

Я подсветил проблемные результаты в ячейках - желтым цветом.
Автор - Grell
Дата добавления - 20.04.2017 в 14:57
китин Дата: Четверг, 20.04.2017, 14:58 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
ну ловите монстра. оптимизировать некогда
Код
=ЕСЛИОШИБКА(ЛЕВБ(ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99);ПОИСК(",";ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99))-1);0)
К сообщению приложен файл: _Grell.xls (31.5 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Четверг, 20.04.2017, 15:00
 
Ответить
Сообщениену ловите монстра. оптимизировать некогда
Код
=ЕСЛИОШИБКА(ЛЕВБ(ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99);ПОИСК(",";ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99))-1);0)
Автор - китин
Дата добавления - 20.04.2017 в 14:58
Grell Дата: Четверг, 20.04.2017, 15:01 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
buchlotnik, макрос - не подходит.
Именно формула нужна.


Сообщение отредактировал Grell - Четверг, 20.04.2017, 15:02
 
Ответить
Сообщениеbuchlotnik, макрос - не подходит.
Именно формула нужна.
Автор - Grell
Дата добавления - 20.04.2017 в 15:01
Grell Дата: Четверг, 20.04.2017, 15:01 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
Nic70y, спасибо - подходит.
 
Ответить
СообщениеNic70y, спасибо - подходит.
Автор - Grell
Дата добавления - 20.04.2017 в 15:01
Grell Дата: Четверг, 20.04.2017, 15:01 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
китин, спасибо.
Теперь все работает.
 
Ответить
Сообщениекитин, спасибо.
Теперь все работает.
Автор - Grell
Дата добавления - 20.04.2017 в 15:01
Grell Дата: Четверг, 20.04.2017, 15:23 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
Использовал формулы:
[vba]
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(D8;ПОИСК("tks";D8)+3;15);"-";ПОВТОР(" ";15));15;99);",";ПОВТОР(" ";99));99));0)
[/vba]

[vba]
Код
=ЕСЛИОШИБКА(ЛЕВБ(ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99);ПОИСК(",";ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99))-1);0)
[/vba]

И в той и в другой формуле, несмотря на кажущуюся работоспособность, обнаружился изъян.

Когда я в области формулы - поставил вместо "tks" - ссылку на ячейку - со значением "tks" - то эта формула перестала выдавать нулевое значение - по целевой ячейке с текстом (D10).
Хотя если вместо ссылки на ячейку

Подскажите, в чем тут может быть дело ?
К сообщению приложен файл: 5207564.xls (35.5 Kb)
 
Ответить
СообщениеИспользовал формулы:
[vba]
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ПОДСТАВИТЬ(ПСТР(D8;ПОИСК("tks";D8)+3;15);"-";ПОВТОР(" ";15));15;99);",";ПОВТОР(" ";99));99));0)
[/vba]

[vba]
Код
=ЕСЛИОШИБКА(ЛЕВБ(ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99);ПОИСК(",";ПСТР(ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);"");ПОИСК("-";ПОДСТАВИТЬ(D8;ЛЕВБ(D8;ПОИСК("tks";D8)+2);""))+1;99))-1);0)
[/vba]

И в той и в другой формуле, несмотря на кажущуюся работоспособность, обнаружился изъян.

Когда я в области формулы - поставил вместо "tks" - ссылку на ячейку - со значением "tks" - то эта формула перестала выдавать нулевое значение - по целевой ячейке с текстом (D10).
Хотя если вместо ссылки на ячейку

Подскажите, в чем тут может быть дело ?
Автор - Grell
Дата добавления - 20.04.2017 в 15:23
Nic70y Дата: Четверг, 20.04.2017, 15:29 | Сообщение № 13
Группа: Друзья
Ранг: Экселист
Сообщений: 9122
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
потомушто надо E$4
а не E4

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 20.04.2017, 15:30
 
Ответить
Сообщениепотомушто надо E$4
а не E4
Автор - Nic70y
Дата добавления - 20.04.2017 в 15:29
китин Дата: Четверг, 20.04.2017, 15:31 | Сообщение № 14
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
а кто будет Е4 закреплять долларами? вы тянете формулу и Е4 меняется на Е5;Е6;Е7 и т.д.

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеа кто будет Е4 закреплять долларами? вы тянете формулу и Е4 меняется на Е5;Е6;Е7 и т.д.
Автор - китин
Дата добавления - 20.04.2017 в 15:31
Grell Дата: Четверг, 20.04.2017, 15:31 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 113
Репутация: 0 ±
Замечаний: 60% ±

Excel 2007
Nic70y, а да... точно.
Не обратил внимания.
Спасибо еще раз.
 
Ответить
СообщениеNic70y, а да... точно.
Не обратил внимания.
Спасибо еще раз.
Автор - Grell
Дата добавления - 20.04.2017 в 15:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как формулой извлечь часть текста, по определенному критерию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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