Як видалити все до або після певного символу в Excel

Люди, які тільки починають працювати в Excel, часто зустрічаються з таким питанням.

Допустимо, у нас є така табличка:

Як видалити все до або після певного символу в Excel

Приблизно так виглядає видалення всіх символів після “,”.

Це можна зробити різними способами. Ми розглянемо дещо.

Тож почнемо!

За допомогою функції «Знайти та замінити»

Це, мабуть, найшвидший і найзручніший спосіб.

Допустимо, у нас та ж табличка і завдання:

Як видалити все до або після певного символу в Excel

Покрокова інструкція:

  • Копіюємо і вставляємо стовпчик А в;

Як видалити все до або після певного символу в Excel

  • Виділіть стовпчик і натисніть «Головна»;

Як видалити все до або після певного символу в Excel

  • Далі – “Знайти і виділити” -> “Замінити …”;

Як видалити все до або після певного символу в Excel

  • У першому параметрі вкажіть “*”;

Як видалити все до або після певного символу в Excel

  • Другий параметр не змінюйте;

Як видалити все до або після певного символу в Excel

  • Натисніть кнопку «Замінити всі».

Як видалити все до або після певного символу в Excel

Готово! Ось результат:

Як видалити все до або після певного символу в Excel

Як це працює?

Символ * означає невизначену кількість символів.

Оскільки ми використовуємо «,*», це означає, що програмі потрібно замінити кому і всі символи після неї на порожнє місце.

Це буде працювати тільки якщо в кожному осередку у вас одна кома, якщо ж у вас не одна, то перша та всі інші дані будуть замінені на порожнє місце.

За допомогою формул

Також ми можемо виконати наше завдання і за допомогою формул.

Допустимо, у нас є така табличка:

Як видалити все до або після певного символу в Excel

Формула набуває такого вигляду:

=ЛЕВСИМВ(A2;ЗНАЙТИ(“,”;A2)-1)

Як видалити все до або після певного символу в Excel

Функція ЗНАЙТИ повертає порядковий номер коми.

Це простий приклад, давайте розглянемо дещо складніше.

Тепер у нас така табличка:

Як видалити все до або після певного символу в Excel

Формула, для цього прикладу, набуває такого вигляду:

=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;2))-1)

Як видалити все до або після певного символу в Excel

Отже, як і минулого разу — не вийде. Оскільки ЗНАЙТИ повертатиме порядковий номер першої коми, а нам треба знайти його для другої.

Ми використовуємо невелику хитрість, а якщо конкретніше, то замінюємо другу кому на знак оклику, а потім з ним вже проводимо операції.

І все б добре, тільки в цьому прикладі в кожному рядку у нас рівно 2 коми. А що робити якщо їхня невизначена кількість? Адже у великих даних ви не вивірятимете скільки коми в кожному рядку.

Ось приклад:

Як видалити все до або після певного символу в Excel

Отже, нам потрібно знайти порядковий номер останньої коми, а потім уже проводити з нею операції.

Для цього прикладу формула набуває такого вигляду:

=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;ДЛСТР(A2)-ДЛСТР(ПІДСТАВИТИ(A2;”,”;”,”))))-1)

Як видалити все до або після певного символу в Excel

Отже, функція ДЛСТР спочатку знаходить кількість символів у рядку із комами, а потім без них.

А потім віднімає з першого – друге. Таким чином ми отримуємо кількість ком в рядку.

А потім ми замінюємо останню на знак оклику.

Ось так можна замінювати все після певного символу за допомогою формул. Звісно, з невеликими хитрощами.

Плюс цього в тому, що дані будуть динамічні. Тобто якщо щось зміниться в початкових даних, все зміниться і даних після обробки.

За допомогою функції «Заповнити»

Функція «Заповнити» – це досить давній інструмент. Він може допомогти нам у цьому випадку.

Як він працює?

Дуже просто – ви просто робите будь-що і після використовуєте функцію. Вона намагається зрозуміти логіку ваших дій та продовжити її.

Давайте розглянемо приклад.

Припустимо, у нас є та сама табличка:

Як видалити все до або після певного символу в Excel

Покрокова інструкція:

  • У перший осередок стовпчика В введіть те, що має вийти після обробки;

Як видалити все до або після певного символу в Excel

  • У наступний осередок, те саме;

Як видалити все до або після певного символу в Excel

  • А тепер виділіть стовпчик;

Як видалити все до або після певного символу в Excel

  • І клацніть на “Головна” -> “Заповнити” -> “Миттєве заповнення”;

Як видалити все до або після певного символу в Excel

Готово! Ось результат:

Як видалити все до або після певного символу в Excel

Цю функцію можна використовувати не тільки для видалення тексту після символу. Вона працює там, де є логіка.

Однак іноді вона може помилятися. Тому завжди перевіряйте те, що вийшло після обробки.

За допомогою Visual Basic

І, як завжди, розберемо варіант за допомогою Visual Basic.

Ми створимо власну функцію і будемо використовувати її для обробки даних.

Це дуже зручно, якщо ви робите щось дуже часто. Наприклад, як у нашому випадку видаляєте дані після символу.

Код Visual Basic:

Function LastPosition(rCell As Range, rChar As String) 'Ця функція виглядає останньою позицією відповідного character 'This code ha beveled by Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rC For i = rLen To 1 Step -1 If Mid(rCell, i – 1, 1) = rChar Then LastPosition = i – 1 Exit Function End If Next i E nd Function

Код, щоб він працював, потрібно вставити у Visual Basic -> “Insert” -> “Module”.

Розглянемо приклад її використання.

Допустимо, у нас є така табличка. Формула набуває такого вигляду:

=ЛЕВСІМВ(A2;LastPosition(A2;”,”)-1)

Як видалити все до або після певного символу в Excel

У нашій функції першим аргументом ми вказали діапазон для пошуку, а другим символ, останню позицію якого нам потрібно знайти.

За допомогою Visual Basic все простіше.

От і все! Якщо вам потрібно зробити щось подібне 1-2 рази, то краще використовувати функцію «Знайти і замінити…», а якщо ви робите це постійно, то використовуйте Visual Basic.

Сподіваюся, ця стаття виявилася корисною для вас!

nBook
Додати коментар