Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція заміни Excel в Excel для пошуку та заміни тексту.
ЗАМІНА Огляд функції
Функція SUBSTITUTE знаходить і замінює існуючий текст новим текстовим рядком. З урахуванням регістру
Щоб скористатися функцією заміни робочого аркуша Excel, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Функція SUBSTITUTE Синтаксис та входи:
| 1 | = SUBSTITUTE (текст, старий_текст, новий_текст, номер_примірника) |
текст - Оригінальний рядок тексту.
старий_текст - Текстовий рядок, який потрібно знайти та замінити.
new_test - Текст заміни.
instance_num - ОПЦІОНАЛЬНИЙ. Номер екземпляра для заміни. Якщо це поле порожнє, усі екземпляри замінюються.
Що таке ЗАМІНА?
Функція SUBSTITUTE в Excel використовується для заміни рядка символів у текстовому рядку іншим зазначеним текстовим рядком.
Давайте розглянемо наступний приклад, замінивши рядок “швидкий коричневий” на “повільний білий”.
| 1 | = ЗАМІНА (C2, C3, C4) |
SUBSTITUTE шукає в рядку в C2, знаходить "швидкий коричневий" і замінює його на "повільний білий".
Як користуватися SUBSTITUTE
Функція SUBSTITUTE приймає 3 необхідні аргументи та 1 необов’язковий:
- Текст: текст, який потрібно замінити символами
- Старий_текст: текст, який потрібно замінити
- Новий_текст: текст, яким потрібно замінити старий_текст
- Кількість екземплярів (необов’язково): Вказує на появу старого_тексту, який потрібно замінити на Новий_текст. За замовчуванням усі випадки Old_text будуть замінені, якщо екземпляр не вказано.
ВМІНЕННЯ ЗАМІНИ
Що, якби нам довелося замінити дві частини одного рядка? Давайте подивимося, як ми об'єднаємо дві функції SUBSTITUTE.
| 1 | = ЗАМІНА (ЗАМІНА (С2, С3, С4), С5, С6) |
Зверніть увагу, як результат SUBSTITUTE (C2, C3, C4) використовується як перший аргумент для другого SUBSTITUTE. Якщо розбити формулу на частини:
= SUBSTITUTE (C2, C3, C4) оцінює: повільний білий лисиця стрибає через ледачого собаку ».
Потім SUBSTITUTE («Повільна біла лисиця перестрибує через ледачого собаку», C5, C6), яка оцінюється як «Повільна біла лев стрибає через ледачого собаку ».
Різниця між SUBSTITUTE та REPLACE
Аналогічна функція SUBSTITUTE - REPLACE. У більшості випадків їх можна використовувати як взаємозамінні, однак, діє наступне емпіричне правило.
Функцію SUBSTITUTE слід використовувати, коли рядок тексту підлягає заміні відомий, або ціле слово або слова замінюються, як у нашому прикладі лисиці, використаному раніше.
| 1 | = ЗАМІНА (C2, C3, C4) |
Функцію REPLACE слід використовувати, коли розташування символів тексту для заміни в рядку відомі, або під час заміни частини слова чи рядка. Наприклад, видалення дефіса з рядка чисел.
Функція REPLACE також може бути вкладена як SUBSTITUTE, однак важливо зазначити, що відносне положення початкового символу може змінюватися залежно від довжини рядка, який потрібно замінити на першому кроці вкладеної формули.
Наприклад, заміна “швидкий коричневий” на “повільний білий” означає заміну 11 символів на 10 символів. Тому, якщо він вкладений, початковий символ для другої ЗАМІНИ буде на 1 символ від початкових позицій символів.
Поради щодо заміни
- SUBSTITUTE можна використовувати для підрядків:
- Пам’ятайте, що вам потрібно вказати номер_інстанції, якщо ви намагаєтесь замінити набір символів, які повторюються у рядку:
Зверніть увагу, що 3030 неправильний, оскільки і "20", і "20" у "2020" були замінені на "30".
Цікавить більше функцій тексту?
Докладніше про ЗАМІНУ або про те, як використовуються інші текстові функції в Excel, такі як ВЛІВО, СЕРЕДНЬО та ВПРАВО, дивіться в інших наших статтях.
SUBSTITUTE у Таблицях Google
Функція SUBSTITUTE працює точно так само в Google Таблицях, як і в Excel:
додаткові нотатки
Функція SUBSTITUTE чутлива до регістру !. Ви можете скористатися функціями LOWER або UPPER для перетворення рядків тексту у послідовний регістр перед використанням функції SUBSTITUTE. Потім, за бажанням, ви можете використовувати функцію ВЛАСНО, щоб написати велику першу літеру кожного слова.
Крім того, ви можете скористатися функціями ПОШУК і ЗАМІНА, щоб імітувати незамітний регістр ЗАМІНИ. Використовуйте функцію SEARCH, щоб знайти початкове положення текстового рядка. Функція SEARCH не враховує регістр. Потім скористайтеся функцією ЗАМІНИТИ, щоб замінити текст. Вам потрібно буде використати результат функції SEARCH як вхід start_num у REPLACE. Вам також потрібно буде визначити кількість символів для заміни (num_chars). Ви можете підрахувати їх вручну або скористатися функцією LEN для підрахунку кількості символів. Приклад:
Замінити - видалити дефіси
Поширене питання - як видалити дефіс з тексту. Хитрість полягає в тому, щоб дефіс замінити нічим.
Для цього прикладу припустимо, що клітинка А1 містить текст із дефісами. Ось як їх видалити
1. У порожній клітинці -помічнику введіть = SUBSTITUTE (
2. Натисніть на клітинку, у яку ви хочете замінити символи, або A1 (це автоматично заповнює формулу)
3. Введіть, (кома)
4. Введіть "-"
5. Введіть, (кома)
6. Введіть “” і натисніть Enter
Ваш кінцевий результат у клітині -помічнику має виглядати так:
= ЗАМІНА (A1, ”-“, ””)
Ще 3 замінні приклади:
Дивлячись на клітинку В3, ми хочемо замінити слово “хворий” на “великий”. Ми можемо використати такий вираз:
| 1 | = ЗАМІНА (В3, "хворий", "великий", 1) |
Якщо текст, який ми хочемо замінити, зустрічається більше одного разу, нам потрібно вказати, що мається на увазі. У другому прикладі ми маємо два вживання слова «старий». Отже, якщо ми хочемо змінити другий екземпляр на слово «сірий», то ми повинні використовувати:
| 1 | = ЗАМІНА (В4, "старий", "сірий", 2) |
Зауважте, що якщо входження не вказано, то всі екземпляри замінюються. Так:
| 1 | = ЗАМІНА (В4, "старий", "сірий") |
Позбувся б усіх прикладів слова "старий" і замінив би їх словом "сірий". Зауважте, що якщо старий текст не вдається знайти, то рядок залишається незмінним. Тож розглянемо останній приклад:
| 1 | = ЗАМІНА (B5, "чорний", "сірий", 1) |
Це означає, що ми намагаємось замінити слово "чорний" на слово "сірий". Однак, оскільки «чорний» не зустрічається, оригінальний рядок залишався левітрою, що поставляється в Сполучених Штатах без змін:
Щоб завантажити файл .XLSX із цієї статті, натисніть тут
ЗАМІНА Приклади у VBA
Ви також можете використовувати функцію SUBSTITUTE у VBA. Тип:application.worksheetfunction.substitute (текст, старий_текст, новий_текст, номер_примірника)
Для аргументів функції (текст тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Коли виконується наступний код
| 1 | Діапазон ("A1") = Робочий лист Функція. Замінити (Діапазон ("A1"), "використовуючи", "", 1) |
Вміст комірки А1 стане таким:
Отже, слово "використання" було замінено порожнім рядком. Останній параметр (1) вказує, який екземпляр тексту слід замінити, якщо текст присутній більше одного разу. Так, наприклад, якщо ми виконаємо наступне твердження
| 1 | Діапазон ("A1") = Робочий лист Функція. Замінити (Діапазон ("A1"), "the", "an", 1) |
Осередок А1 буде виглядати так
Зверніть увагу, що перше введення «the» було замінено на «an», тоді як на друге «the» це не вплинуло. Якби ми опустили останній параметр, обидва слова “the” були б замінені
Повернення до списку всіх функцій Excel
