Люди, які тільки починають працювати в Excel, часто зустрічаються з таким питанням.
Допустимо, у нас є така табличка:
Приблизно так виглядає видалення всіх символів після “,”.
Це можна зробити різними способами. Ми розглянемо дещо.
Тож почнемо!
За допомогою функції «Знайти та замінити»
Це, мабуть, найшвидший і найзручніший спосіб.
Допустимо, у нас та ж табличка і завдання:
Покрокова інструкція:
- Копіюємо і вставляємо стовпчик А в;
- Виділіть стовпчик і натисніть «Головна»;
- Далі – “Знайти і виділити” -> “Замінити …”;
- У першому параметрі вкажіть “*”;
- Другий параметр не змінюйте;
- Натисніть кнопку «Замінити всі».
Готово! Ось результат:
Як це працює?
Символ * означає невизначену кількість символів.
Оскільки ми використовуємо «,*», це означає, що програмі потрібно замінити кому і всі символи після неї на порожнє місце.
Це буде працювати тільки якщо в кожному осередку у вас одна кома, якщо ж у вас не одна, то перша та всі інші дані будуть замінені на порожнє місце.
За допомогою формул
Також ми можемо виконати наше завдання і за допомогою формул.
Допустимо, у нас є така табличка:
Формула набуває такого вигляду:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“,”;A2)-1)
Функція ЗНАЙТИ повертає порядковий номер коми.
Це простий приклад, давайте розглянемо дещо складніше.
Тепер у нас така табличка:
Формула, для цього прикладу, набуває такого вигляду:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;2))-1)
Отже, як і минулого разу — не вийде. Оскільки ЗНАЙТИ повертатиме порядковий номер першої коми, а нам треба знайти його для другої.
Ми використовуємо невелику хитрість, а якщо конкретніше, то замінюємо другу кому на знак оклику, а потім з ним вже проводимо операції.
І все б добре, тільки в цьому прикладі в кожному рядку у нас рівно 2 коми. А що робити якщо їхня невизначена кількість? Адже у великих даних ви не вивірятимете скільки коми в кожному рядку.
Ось приклад:
Отже, нам потрібно знайти порядковий номер останньої коми, а потім уже проводити з нею операції.
Для цього прикладу формула набуває такого вигляду:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;ДЛСТР(A2)-ДЛСТР(ПІДСТАВИТИ(A2;”,”;”,”))))-1)
Отже, функція ДЛСТР спочатку знаходить кількість символів у рядку із комами, а потім без них.
А потім віднімає з першого – друге. Таким чином ми отримуємо кількість ком в рядку.
А потім ми замінюємо останню на знак оклику.
Ось так можна замінювати все після певного символу за допомогою формул. Звісно, з невеликими хитрощами.
Плюс цього в тому, що дані будуть динамічні. Тобто якщо щось зміниться в початкових даних, все зміниться і даних після обробки.
За допомогою функції «Заповнити»
Функція «Заповнити» – це досить давній інструмент. Він може допомогти нам у цьому випадку.
Як він працює?
Дуже просто – ви просто робите будь-що і після використовуєте функцію. Вона намагається зрозуміти логіку ваших дій та продовжити її.
Давайте розглянемо приклад.
Припустимо, у нас є та сама табличка:
Покрокова інструкція:
- У перший осередок стовпчика В введіть те, що має вийти після обробки;
- У наступний осередок, те саме;
- А тепер виділіть стовпчик;
- І клацніть на “Головна” -> “Заповнити” -> “Миттєве заповнення”;
Готово! Ось результат:
Цю функцію можна використовувати не тільки для видалення тексту після символу. Вона працює там, де є логіка.
Однак іноді вона може помилятися. Тому завжди перевіряйте те, що вийшло після обробки.
За допомогою 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)
У нашій функції першим аргументом ми вказали діапазон для пошуку, а другим символ, останню позицію якого нам потрібно знайти.
За допомогою Visual Basic все простіше.
От і все! Якщо вам потрібно зробити щось подібне 1-2 рази, то краще використовувати функцію «Знайти і замінити…», а якщо ви робите це постійно, то використовуйте Visual Basic.
Сподіваюся, ця стаття виявилася корисною для вас!
