Файлы к уроку: Файлы доступны только спонсорам канала. Оформите спонсорскую подписку всего за 49 — 149 рублей, если хотите получить максимум от обучения.
В этом уроке:
- Как сделать множественную замену текста в Power Query
- Как сделать множественную замену текста в Power Query без учета регистра
- Как сделать множественную замену текста в Power Query без учета регистра с помощью функции List.Accumulate
- Как сделать множественную замену текста в Excel
- Функция List.Positions
- Функция List.Accumulate
- Функция Text.Lower
- Функция Text.Proper
- Функция Text.Replace
- Множественная текстовая замена в VBA
В наших данных есть такой столбец:
Компания |
Открытое акционерное общество Санаторий "Красная Талка" |
Открытое акционерное общество фирма "Геленджик-Отель" |
Закрытое акционерное общество «НЕВА ИНТЕРНЕЙШЕНЕЛ» |
Закрытое акционерное общество пансионат "Приветливый берег" |
Закрытое акционерное общество лечебно-оздоровительный комплекс "Солнечная" |
Закрытое акционерное общество "Санаторий "Ейск" |
Закрытое акционерное общество "Санаторий "Предгорье Кавказа" |
Нам нужно сделать такие замены:
Найти | Заменить |
открытое акционерное общество | ОАО |
закрытое акционерное общество | ЗАО |
индивидуальный предприниматель | ИП |
общество с ограниченной ответственностью | ООО |
" | « |
« | « |
» | « |
& | & |
акционерное общество | АО |
образовательный фонд | ОФ |
Такую таблицу замен нужно обязательно создать для решения этой задачи.
Так же для решения понадобится еще одна вспомогательная таблица:
Найти | Заменить |
Оао | ОАО |
Зао | ЗАО |
Ип | ИП |
Ооо | ООО |
Ао | АО |
Нао | НАО |
Оф | ОФ |
Алгоритм решения в Power Query:
- Выполнить массовую текстовую замена с помощь List.Accumulate в нижнем регистре
- Преобразовать столбец в Proper Case
- Выполнить массовую текстовую замену с помощью List.Accumulate еще раз со второй таблицей
Формула первого преобразования выглядит так:
each List.Accumulate(List.Positions(Замена_1[Найти]), Text.Lower([Компания]),
(state, current) => Text.Replace(state, Замена_1[Найти]{current}, Замена_1[Заменить]{current}))
Формула второго преобразования выглядит так:
each List.Accumulate(List.Positions(Замена_2[Найти]), [Пользовательская],
(state, current) => Text.Replace(state, Замена_2[Найти]{current}, Замена_2[Заменить]{current}))
VBA код для решения задачи выглядит так:
Sub replace_values()
For Each company In Range("Гостиницы[Компания2]")
For Each Find In Range("Замена_1[Найти]")
company.Value = replace(company, Find.Value, Find.Offset(0, 1).Value, Compare:=vbTextCompare)
Next Find
Next company
End Sub