Excel works

Excel works!

Мы уже разбирали как быстро найти и заменить данные в Excel по группе ячеек — в столбце или массиве данных. Но как это сделать в одной ячейке и желательно формулой? К примеру? у вас есть список машин, и каждый день вам присылают файл с новым списком машин. Нужно проверить есть ли такие номера в вашем основном списке. Но как обычно в номера машин добавляют пробелы или слеши, а самый худший вариант букву О вместо нуля Если вы регулярно выполняете одну и ту же операцию, то вручную нажимать Ctrl + H каждый раз неудобно, плюс придется запоминать, какие именно символы нужно заменять. Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel — это отличный выход из ситуации.

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

Есть номер А 777 АА 01 из него надо формулой сделать А777АА01 формулой.

Функция ПОДСТАВИТЬ в Excel

Как работает формула показано на первой картинке. Состав разберем ниже:

Текст — адрес ячейки или сам текст, в котором надо сделать замену символов, в примере А 777 АА 01
Стар_текст — что нам нужно заменить? Пробел.
Нов_текст — на что нужно заменить? Меняем на ничего, т.е. на пусто в формуле, это будет «»
Номер_вхождения — заполнять необязательно, но при этом реквизит очень полезный, в официальной справке говорится о «вхождении символа», но объясню простым языком. Здесь вы можете указать номер символа в строке (пробела в нашем случае). Т.е. если нужно заменить только пробел между 777 и АА (это второй по счету пробел в номере), формула будет выглядеть как

И в итоге вы получите А 777АА 01

Но, как правило, я использую вот такие конструкции, чтобы убрать все ненужные символы, которые уже понаставили при заполнении.

В данном случае мы убираем пробел, точку и слэш в номере «А 777 АА/01.»

Функция ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Excel

Функция ЗАМЕНИТЬ делает почти все тоже самое, что и подставить, но делает это с определенного символа. Аргументы такие:

Пример. Необходимо первую букву номера машины заменить на другую. Из А777АА01 сделать Б777АА01

Сначала описание аргументов:

Стар_текст — как и в ПОДСТАВИТЬ, текст, в котором нужно заменить т.е. А777АА01
Начальная_позиция — с какого символа производить замену, с первого.
Число_знаков сколько знаков меняем — один
Нов_текст — и на что меняем? На букву Б

Очень похоже на ПСТР , честно говоря 🙂

ЗАМЕНИТЬБ — это маленькая загадка Excel, даже в иностранных интернетах мало информации о ней. Если вкратце, то просто ЗАМЕНИТЬ меняет любой символ (даже если он размером 2 байта), ЗАМЕНИТЬБ меняет только однобайтные символы (аргумент число_знаков меняется на). Практического применения я для себя не нашел 🙂 Если кто-нибудь подскажет, буду благодарен!

UPD. Как мне подсказали, к двухбайтовым символам относятся большинство языков с ироглифами!

ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Что еще?

Обе функции учитывают регистр! Т.е. если вы указали большую букву А, маленькая буква заменяться не будет!

Функция ПОДСТАВИТЬ доступна только в версиях 2007 и выше, будьте аккуратны, если конечный пользователь будет использовать более ранние версии!

Как в Power BI или Power Pivot заменить одну часть текста другой? DAX функции REPLACE и SUBSTITUTE

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI или PowerPivot заменить одну часть текста другой, например, исправить какие-либо ошибки в исходном тексте. В языке DAX за это отвечают две функции REPLACE и SUBSTITUTE.

Читайте так же:

Защита листа от редактирования в excel

Давайте разберем эти функции, их параметры и свойства. А также, составим примеры формул и посмотрим их работу на практике.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция REPLACE в Power BI и Power Pivot

REPLACE () — заменяет один текст другим, с учетом стартовой позиции и количества символов.

Текст 1 – старый текст, который нужно изменить
Текст 2 – новый текст, которым нужно заменить старый
Позиция – номер позиции символа, с которого нужно заменять старый текст
Количество – количество символов, которые нужно заменить

Пример формулы на основе DAX функции REPLACE.

В Power BI Desktop имеется исходная таблица «Спорт Товары», содержащая перечисление спортивных курток бренда NIKE:

Но, в этой таблице наименование бренда записано с ошибкой — NICE. Задача — в исходной таблице создать столбец с перечислением спортивной одежды без ошибок в наименовании бренда. Попробуем решить эту задачу при помощи DAX функции REPLACE, которая сможет заменить слово NICE на NIKE.

Запишем формулу, где в первом параметре REPLACE вставим ссылку на исходный столбец с текстовой информацией, во втором параметре укажем позицию, с которой нужно начать замену символов, равную 7. В третьем параметре пропишем количество символов для замены — 4 и в четвертом параметре само новое слово «NIKE»:

Но, в Power BI, к сожалению, результат не тот, который мы ожидали:

А именно, в первой строке наименование бренда написано слитно с первым словом и вообще, имя бренда опять написано неправильно. Вся эта ошибка возникла из-за того, что мы в формуле указали 7 позицию символа, с которого нужно начать замену. Но, 7 позиция верна только для 2 и 3 строки, а для первой нужна 8 позиция, так как первое слово в первой строке длиннее на 1 символ, чем во 2 и 3 строке.

Чтобы исправить эту ошибку, дополнительно воспользуемся еще одной функцией языка DAX — SEARCH (подробно ознакомиться с этой функцией Вы можете в данной статье), которая способна найти искомое слово с ошибкой и вернуть его стартовую позицию. Исправим нашу формулу:

Проверим работу этой формулы в Power BI:

Как мы видим, теперь ошибка в наименовании бренда исправлена.

DAX функция SUBSTITUTE в Power BI и Power Pivot

SUBSTITUTE () — заменяет один текст другим с учетом регистра.

Текст 1 – исходный текст (столбец с текстовыми данными)
Текст 2 – часть текста, которую нужно изменить
Текст 3 – новая часть текста
Номер вхождения — (необязательный параметр) вхождение (повторы) того слова, которое нужно заменить. Если параметр неуказан, то по умолчанию заменяются все вхождения (повторы) искомого слова

Рассмотрим формулу на основе DAX функции SUBSTITUTE, продолжая пример, который мы рассматривали выше, но сейчас, немного усложним исходные данные.

В Power BI имеется такая же исходная таблица по спортивным товарам, но сейчас там расположены разные бренды, и ошибка в наименовании бренда NIKE допущена только в одной строке:

Читайте так же:

Как включить юнити плеер в яндекс браузере

Если мы попытаемся изменить ошибку при помощи функции REPLACE, которую рассматривали выше, то из-за того, что в ней нужно указывать точный номер позиции первого символа для замены, ошибку здесь изменить невозможно:

И тут, нам на помощь приходит еще одна текстовая функция языка DAX — SUBSTITUTE, в которой уже нет необходимости указывать какие-либо стартовые позиции. В ней просто нужно указать именно то конкретное слово, которое нужно изменить. Напишем формулу с участием функции SUBSTITUTE:

В первом параметре этой функции мы указали исходный столбец с текстовыми данными, во втором — искомое текстовое выражение и в третьем — текст для замены.

В итоге, в Power BI Desktop мы можем наблюдать созданный вычисляемый столбец с информацией без каких-либо ошибок:

На этом, с разбором DAX функций REPLACE и SUBSTITUTE, заменяющих одну часть текста другой в Power BI и Power Pivot, все.

Пожалуйста, оцените статью:

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

Как в Power BI рассчитать таблицу ТОП-3 не через вычисляемую таблицу, а через меру?

Математические функции в DAX: SQRT, POWER, ABS, SIGN, EXP, FACT, LN, LOG, LOG10, PI, RAND, RANDBETWEEN, RADIANS в Power BI и Power Pivot

CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX

Способ 3: Функция ПОДСТАВИТЬ

По аналогии с предыдущим методом работает функция ПОДСТАВИТЬ , для которой задается самая ячейка, искомое значение и ее замена. Требуется активировать пустую клетку, куда будет подставлено уже исправленное значение, а затем вписать в нее следующее: ПОДСТАВИТЬ(A1;» «;»») , где A1 — необходимая для обработки ячейка. Создавайте заново или копируйте эту формулу, подставляя другие ячейки, если требуется обработать сразу несколько значений.

Форматы приложения

Кроме данных, которые могут меняться местами, инструментарий программного продукта, предложенного фирмой Microsoft, позволяет определить в тексте места, которые имеют нестандартное форматирование. Естественно, посредством набора команд можно одно форматирование сделать совершенно другим. К примеру, данные с курсивом следует дополнительно отметить жирным шрифтом. Для этого в категории «Главная»необходимо определить подразделы «Редактирование» и «Найти и выделить», способные произвести необходимую замену.

Задача

С помощью ленты редактора запросов

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

Установите флажок Сравнивать содержимое ячейки целиком, чтобы заменить все содержимое ячеек. В противном случае при использовании команды Замена значений будут заменены все значения, соответствующие условию Значение для поиска.

Нажмите кнопку ОК.

Вам также могут быть интересны следующие статьи

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Читайте так же:

Запрет на изменение ячеек excel

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое!
Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке?
Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель:
1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные.
2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456.
Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

если руками:
Ctrl+H
и меняй » » на «», «-» на «», ну и в свободном столбце к получившемуся значению прибавь плюсик таким образом (допустим, если «37529123456» в ячейке А1, а В1 свободна — там и пишем:
=»+»&А1
Вот и получишь на выходе «+37529123456», остается только скопировать и вставить значения в столбец А.
Если там уже был плюс, то, опять же, поиском пробегаешься «++» меняешь на «+» и все готово

Если макросом — можешь записать рекордером, потом допилить получившийся макрос под себя (чтобы не вопил, если ниче не нашел и тому подобное). Почитай про метод find в vba

Здравствуйте,
Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

У меня такая проблема, только мне нужно одно слово другим цветом выделить, но выделяет всю ячейку.

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо!
262-о на О-262 и.тд.
263-о
264-о
265-о
266-о
267-о
268-о
269-о
270-о
271-о
271-о-а
272-о
273-о
274-о
275-о
276-о
277-о
278-о
279-о
280-о
281-о
282-о
283-о
284-о
285-о
286-о
287-о
288-о
289-о
290-о
291-о
291-о-а
292/1-о
292-о
293-о
294-о
295-о
296-о
297-о
297/1-о
298-о
298-а-о
299-о
300-о
301-о
302-о
303-о
304-о
305-о
306-о
307-о
308-о
309-о
310-о
311-о
312-о
313-о
314-о
315-о
316-о
317-о
318-о
319-о
320-о
321-о
322-о
323-о
324-о
326-о
325-о
327-о
329-о
330-о
331-о
332-о
333-о
334-о
335-о
336-о
337-о
338-о
339-о
340-о
342-о
343-о
344-о
345-о
347-о
348-о
349-о

Заменить несколько значений в excel

Как все мы знаем, мы можем использовать функцию «Найти и заменить», чтобы найти несколько одинаковых ячеек и заменить их нужным вам значением. Но иногда вам нужно применить замену «многие ко многим» одновременно. Например, у меня есть ряд данных, и теперь я хочу заменить все яблоки на красные яблоки, апельсины на зеленые апельсины, бананы на желтые бананы и так далее, как показано на следующих снимках экрана. Есть ли у вас какие-нибудь хорошие идеи для решения этой задачи? Excel?

Читайте так же:

Защита книги excel от редактирования

Найти и заменить сразу несколько значений кодом VBA

Если вы устали снова и снова находить и заменять значения, следующий код VBA может помочь вам заменить сразу несколько значений необходимыми текстами.

1. Создайте условия, которые вы хотите использовать, которые содержат исходные значения и новые значения. Смотрите скриншот:

2. Затем удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

Код VBA: найти и заменить сразу несколько значений

4, Затем нажмите F5 Чтобы запустить этот код, во всплывающем окне подсказки укажите диапазон данных, в котором вы хотите заменить значения новыми значениями.

5. Нажмите OK, и отображается другое окно подсказки, напоминающее вам о выборе критериев, созданных на шаге 1. См. снимок экрана:

6. Затем нажмите OK, все конкретные значения были заменены новыми значениями по мере необходимости.

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

124 Comments
Login

Best
Popular
Newest
Oldest

You are guest

or post as a guest, but your post won’t be published automatically.

How would the code look like if you would check by line?

First line: Original value «Apple» (A1) and replacing value «Green Apple» (B1) and in the same line is
the original range to be replaced by B2 e.g., apple, orange, banana, nuts (D1, E1, F1. )

Second line: Original value «Orange» (A2) and replacing value «Green Orange» (B2) and
in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2. )

Читайте так же:

Где вводить ключ в стиме в браузере

Thank you so much!

Second line: Original value «Orange» (A2) and replacing value «Green Orange» (B2) and in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2. )

Thank you so much!

Hello, sameer,
May be the following code can solve your problem, please try it:

Sub MultiFindNReplace()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = «KutoolsforExcel»
Set InputRng = Application.Selection
Set InputRng = Application.InputBox(«Original Range «, xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox(«Replace Range :», xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlWhole, MatchCase:=True
Next
Application.ScreenUpdating = True
End Sub

Hope it can help you!

skyyang — Thanks. This version does not work in my case since it appears to ‘match entire cell contents’ but I need to replace only part of the string. I realize now that the example I typed out does not communicate this detail; can you see the JPG I attached instead? Original Range:
Product1 = Call Option: risk 1
Product2 = Index Call Option: risk 2

It’s the extra text after the colon that breaks your version. And OP’s code is able to ignore the extra text, but can’t differentiate between «Call Option Risk:» and «Index Call Option Risk:». Again, I think the picture explains this best, if you could please take another look.

How skip cell after first match?

I’ve sorted translations from longest phrase to shortest.

He means that if a part of the original repeats later there is an issue because the function replace again, sorted or not, if the original word is not unique (even a part of it) this function does not work

Hello, mar_m,
Please apply the below code, try if it can solve your problem, Thank you!

El mejor. Mil gracias por compartir estos conocimientos, fue increíble la ayuda que me proporciono este programa en VBasic (nunca lo había usado hasta ahora), lo pensaba realizar en php o similar pero leyendo encontre esta valiosa información.

I have to replace list of bank with proper name based on key words. For example, if somebody enters Halifax, it should be Halifax Bank of Scotland (HBOS) or if somebody enters LloydsTSB, it should be Lloyds.

Original value Replacing Value
LloydsTSB : Lloyds
Santander : Santander (Abbey)
Argos Card Services : Argos
Halifax : Halifax Bank of Scotland (HBOS)

The down formula works in many cases. But if there is two Halifax bank on my list, I will get Halifax Bank of Scotland (HBOS) (HBOS)
. So, first time it finds Halifax, replace with correct one. Again it finds Halifax and replace. How can I correct this problem ?

голоса

Рейтинг статьи