Особи, які тільки розпочинають свою діяльність у Excel, часто стикаються з такою проблемою.
Припустимо, у нас є ось така таблиця:
Ось перероблений текст з збереженням змісту:
Ось як виглядає процес усунення всіх знаків після “”,”:
Є кілька варіантів для цього. Давайте розглянемо декілька з них.
Отже, розпочнемо!
Використовуючи опцію «Пошук і заміна»
Мабуть, це найбільш швидкий і комфортний варіант.
Припустимо, що нам потрібно мати таку ж таблицю та виконати таке ж завдання:
Інструкція поетапно:
- Скопіюємо та вставимо стовпець А в;
- Оберіть стовпець і натисніть на вкладку «Головна»;
- Потім – “Шукати та підсвітити” -> “Змінити …”;
- Вкажіть у першому параметрі символ “*”;
- Не змінюйте другий параметр;
- Увімкніть опцію «Замінити все».
Всё сделано! Ось результат:
Зображення з темою: «Як у Excel видалити текст до або після конкретного символу».
Яким чином це функціонує?
Символ * позначає невизначену кількість знаків.
Ось перероблений текст з збереженням змісту:
Використання символу «,*» свідчить про те, що програмі необхідно видалити кому та всі символи, що знаходяться після неї.
Це здійсниться лише за умови, що в кожному осередку міститься одна кома. Якщо ж таких ком більше ніж одна, то перша та всі наступні дані будуть видалені і замінені на пусте місце.
З використанням формул
Ми також здатні здійснити наше завдання, використовуючи формули.
Наприклад, у нас є ось така таблиця:
Формула має наступний вигляд:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“,”;A2)-1)
Функція ЗНАЙТИ визначає позицію коми в рядку.
Це базовий приклад, тепер розгляньмо щось більш ускладнене.
Тепер у нас є така таблиця:
Для даного прикладу формула виглядає так:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;2))-1)
Отже, як і раніше, у нас не вийде. Адже функція ЗНАЙТИ знаходить позицію першої коми, а нам необхідно виявити позицію другої.
Ми застосовуємо одну просту trick: конкретно, ми замінюємо другу кому на знак оклику і далі виконуємо з ним необхідні дії.
Все в порядку, але в даному прикладі в кожному рядку ми маємо точно 2 коми. А як бути, якщо їх кількість не визначена? У великих обсягах даних вам не вдасться перевірити, скільки саме ком у кожному рядку.
Ось зразок:
Таким чином, перш ніж виконати будь-які операції, варто визначити позицію останньої коми.
У даному прикладі формула має наступний вигляд:
=ЛЕВСИМВ(A2;ЗНАЙТИ(“!”;ПІДСТАВИТИ(A2;”,”;”!”;ДЛСТР(A2)-ДЛСТР(ПІДСТАВИТИ(A2;”,”;”,”))))-1)
Таким чином, функція ДЛСТР спочатку визначає загальну кількість символів у рядку, включаючи коми, а потім без них.
А далі від першого числа віднімаємо друге. Таким чином, ми визначаємо кількість ком в рядку.
Після цього ми змінюємо останній знак на знак оклику.
Отже, існує спосіб заміни всіх символів після конкретного знаку, використовуючи формули. Звичайно, для цього знадобляться деякі трюки.
Перевагою цього є те, що інформація залишатиметься динамічною. Це означає, що у разі змін у вихідних даних, зміни торкнуться і оброблених результатів.
Використовуючи опцію «Заповнити»
Функція «Заповнити» є старим, але корисним інструментом. Вона може виявитися дуже корисною в даній ситуації.
Яким чином він функціонує?
В усе достатньо легко – просто дійте у будь-який спосіб, а потім застосуйте функцію. Вона намагається зрозуміти, якою є логіка ваших дій, і продовжити її.
Ось переформульований варіант: “
Пропонуємо розглянути приклад.
Припустимо, що у нас є аналогічна таблиця:
Інструкція з покроковим виконанням:
- У верхньому осередку колонки В введіть результат, який має бути після обробки;
- У наступному пункті – те ж саме;
– “
- Тепер виокремте стовпчик;
- Оберіть “Головна” -> “Заповнити” -> “Миттєве заповнення”;
Виконано! Ось підсумок:
Цю можливість можна застосовувати не лише для видалення тексту після певного символу. Вона функціонує у моменти, коли присутня логіка.
Проте іноді вона може здійснювати помилки. Тому завжди уважно перевіряйте результати обробки.
З використанням Visual Basic
І, як зазвичай, проаналізуємо варіант, використовуючи Visual Basic.
Ми розробимо індивідуальну функцію і застосуємо її для обробки інформації.
Це надзвичайно комфортно, коли ви регулярно виконуєте одну й ту ж дію. Наприклад, в нашій ситуації ми видаляємо інформацію після знака.
Ось переформульований текст з збереженням сенсу:
Код на Visual Basic:
Function ПозиціяОстання(rКлітинка As Range, rСимвол As String) ‘Ця функція визначає останню позицію вказаного символу ‘Цей код написав Суміт Бансал (https://trumpexcel.com) Dim довжинаРядка As Integer довжинаРядка = Len(rКлітинка) For i = довжинаРядка To 1 Step -1 If Mid(rКлітинка, i – 1, 1) = rСимвол Then ПозиціяОстання = i – 1 Exit Function End If Next i End Function
Якщо будуть потрібні ще зміни або допомога, дайте знати!
Для того щоб код функціонував, його необхідно вставити в Visual Basic через меню “Вставити” -> “Модуль”.
Проаналізуємо приклад її застосування.
Уявімо, що у нас є ось така таблиця. Формула в цьому випадку виглядає так:
=ЛЕВСІМВ(A2;ВостаннєПозиція(A2;”,”)-1)
У нашій функції першим параметром ми визначили область для пошуку, а другим – символ, позицію якого ми хочемо знайти в останньому місці.
Використання Visual Basic робить все легшим.
І ось і все! Якщо вам потрібно виконати подібну задачу один або два рази, то доцільніше скористатися функцією «Знайти і замінити…», але якщо ви плануєте робити це регулярно, то краще застосувати Visual Basic.
Сподіваюся, ця стаття була для вас корисною!
