Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Нужно удалить только нужные мне предлоги/слова в конце фразы, Составил формулу но не до конца рабочаю
 
#1
19.04.2018 14:36:00
Всем привет!

Задача:
Нужно удалить только нужные мне предлоги/слова в конце фразы (в каждой ячейки)

Т.е.
Столбец А:
пластиковые окна через
купить пластиковые окна в
деревянные окна над
деревянные окна к

Столбец B:
над
в

Результат
пластиковые окна через
купить пластиковые окна
деревянные окна
деревянные окна к

Сам по всякому пробовал уже...
Нашел отдельную формулу которая удаляет одно слово в конце
Код
=ЛЕВСИМВ(СЖПРОБЕЛЫ(A1);ПОИСК("`";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"`";ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";""))))-1)
и скрестил ее с такой
Код
=ЕСЛИ(ПРАВСИМВ(A1;1)="в";ПСТР(A1;1;ДЛСТР(A1)-1);A1)

в итоге более менее рабочий вариант получился такой
Код
=ЕСЛИ(ИЛИ(ПРАВСИМВ(A2;1)="в";ПРАВСИМВ(A2;3)="над");ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);ПОИСК("`";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";"`";ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";""))))-1);A2)
но тут как видно нужно прописывать все предлоги руками!

А мне нужно что бы нужные предлоги/слова брались из колонки (их там будет 200-300 и постоянно меняться)

Сам нуб в экселе, мкорее всего это можно как то еще проще сделать
 
 
 
#2
19.04.2018 14:46:38
пример бы в файле приложили веселее бы дело пошло
Лень двигатель прогресса, доказано!!!
 
 
 
#3
19.04.2018 14:53:52
я бы наверно смотрел в сторону массива, попробовать формулу прописать ссылаясь на диапазон возможных значений и условия заключить в массив (фигурные скобки ставятся Cntr+Shift+Enter), скиньте пример файла попробовать.
 
 
 
#4
19.04.2018 23:02:46
Сергей, DopplerEffect, вот пример, частично работает но не перебирает все слова из столбца и проблема с формулой ПРАВСИМВ - с аргументом кол-во символов...Вообщем помогите ребята)
 
 
 
#5
19.04.2018 23:29:29
Макросом
Код
Sub Poisk()
Dim i As Long
Dim iLastRow As Long
Dim Predlog As String
Dim FoundPredlog As Range
Dim arr
 iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For i = 1 To iLastRow
   arr = Split(Cells(i, "A"), " ")
   Predlog = arr(UBound(arr))
    Set FoundPredlog = Columns("C").Find(Predlog, , xlValues, xlWhole)
    If Not FoundPredlog Is Nothing Then
      Cells(i, "B") = Replace(Cells(i, "A"), Predlog, "")
    Else
      Cells(i, "B") = Cells(i, "A")
    End If
  Next
End Sub
 
 
 
#6
19.04.2018 23:40:54
a_tiutiunkin, формула массива
Код
=ЕСЛИОШИБКА(ЛЕВБ(A1;ДЛСТР(A1)-ДЛСТР(ИНДЕКС($C$1:$C$2;ПОИСКПОЗ(99;ПОИСК("* "&$C$1:$C$2;A1))))-1);A1)

PS вот так покороче и без массива
Код
=ЕСЛИОШИБКА(ЛЕВБ(A1;ДЛСТР(A1)-ДЛСТР(ПРОСМОТР(2;1/ПОИСК("* "&$C$1:$C$2;A1);$C$1:$C$2))-1);A1)
Изменено: Казанский - 19.04.2018 23:47:25
 
 
 
#7
20.04.2018 09:45:36
Казанский, первая формула вообще не заменяет ничего почему то (попробовал на своем фале примере)

в прикрпленном фале из вашего примера - супер, почти то что нужно - только когда расширяю диапазон до $C$1:$C$3 - то все перестает работать
а у меня это диапазон (колонка С с предлогами) - неизвестен - может быть 10 а может 200, т.е. туда будет добавляться и убираться
 
 
 
#8
20.04.2018 09:49:52
обе формулы работают внимательно читать надо что пишут помогающие а именно относительно первой формулы
Цитата
Казанский написал:
формула массива
Лень двигатель прогресса, доказано!!!
 
 
 
#9
20.04.2018 09:53:12
Еще вариант. Формула массива. Вводить сочетанием клавиш Ctrl+Shift+Enter. Если введёте правильно, формула обернётся фигурными скобками { } и вернёт нужный результат.
=ЛЕВСИМВ(A1;ДЛСТР(A1)-СУММ(--ЕСЛИ(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";50));50))=$C$1:$C$4;ДЛСТР($C$1:$C$4)+1)))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#10
20.04.2018 12:23:52
Цитата
a_tiutiunkin написал:
у меня это диапазон (колонка С с предлогами) - неизвестен - может быть 10 а может 200
Код
=ЕСЛИОШИБКА(ЛЕВБ(A2;ДЛСТР(A2)-ДЛСТР(ПРОСМОТР(2;1/ПОИСК("* "&$C$1:$C$200;A2)/ДЛСТР($C$1:$C$200);$C$1:$C$200))-1);A2)
 
 
 
#11
21.04.2018 12:50:18
ребята всем спасибо!!! Работает!
особенно Казанский!
последняя формула самая удачная - независимо есть ли в колонке С предлоги или нет (пустые ячейки или нет) - отрабатывает как надо!  
 
 
 
Страницы: 1
Наверх