При роботі з текстовими даними в Excel може виникнути необхідність видалити текст до або після певного символу або текстового рядка.
Наприклад, якщо у вас є імена та дані про позначення людей, ви можете видалити позначення після коми і зберегти ім’я (або навпаки, де ви зберігаєте позначення та видаляєте ім’я).
Іноді це можна зробити за допомогою простої формули або швидкого пошуку та заміни, а іноді для цього потрібні складніші формули або обхідні шляхи.
У цьому підручнику я покажу вам, як видалити текст до або після певного символу в Excel (використовуючи різні приклади).
Тож почнемо з кількох простих прикладів.
Видалити текст після символу за допомогою функції «Знайти та замінити»
Якщо ви хочете швидко видалити весь текст після певного текстового рядка (або перед текстовим рядком), ви можете зробити це, використовуючи Знайти та Замінити та символи підстановки.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете видалити позначення після символу коми і зберегти текст перед комою.
Нижче наведено кроки для цього.
- Скопіюйте та вставте дані зі стовпця А у стовпець В (це також для збереження вихідних даних)
- Вибравши клітинки у стовпці В, клацніть на вкладці «Домашня сторінка»
- У групі Редагування натисніть на опцію Знайти та вибрати
- У параметрах, що з’являються у спадному меню, натисніть на опцію Замінити. Відкриється діалогове вікно «Знайти та замінити»
- У полі "Знайти що" введіть ,* (тобто кома, за якою слідує зірочка)
- Залиште поле «Замінити на» порожнім
- Натисніть кнопку Замінити все
Наведені вище кроки дозволять знайти кому в наборі даних і видалити весь текст після коми (включаючи кому).
Оскільки це замінює текст із вибраних клітинок, було б добре скопіювати текст в інший стовпець, а потім виконати цю операцію пошуку та заміни, або створити резервну копію ваших даних, щоб вихідні дані були недоторканими
Як це працює?
* (знак зірочки) - це символ підстановки, який може представляти будь -яку кількість символів.
Коли я використовую його після коми (у полі «Знайти що»), а потім натискаю кнопку «Замінити все», він знаходить першу кому в комірці і вважає її відповідним.
Це пояснюється тим, що знак зірочки (*) вважається відповідним до всього текстового рядка, що йде за комою.
Тому, коли ви натискаєте кнопку «Замінити все», вона замінює кому та весь текст, що наводиться далі.
Примітка: Цей метод добре працює, тоді у вас є лише одна кома в кожній клітинці (як у нашому прикладі). Якщо у вас є кілька ком, цей метод завжди знайде першу кому, а потім видалить усе після неї. Тому ви не можете використовувати цей метод, якщо хочете замінити весь текст після другої коми і залишити першу такою, яка є.Якщо ви хочете видалити всі символи перед комою, змініть запис поля пошуку "find", поставивши перед комою знак зірочки (*, замість,*)
Видалити текст за допомогою формул
Якщо вам потрібен більший контроль над тим, як знаходити та замінювати текст до або після певного символу, краще скористатися вбудованими текстовими формулами в Excel.
Припустимо, у вас є набір даних нижче, де потрібно видалити весь текст після коми.
Нижче наведена формула для цього:
= ВЛІВО (A2, FIND (",", A2) -1)
У наведеній вище формулі використовується функція FIND для пошуку положення коми в комірці.
Цей номер позиції потім використовується функцією LEFT для вилучення всіх символів перед комою. Оскільки я не хочу, щоб кома була частиною результату, я вилучив 1 з отриманого значення формули Find.
Це був простий сценарій.
Візьмемо трохи складний.
Припустимо, у мене є такий набір даних, де я хочу видалити весь текст після другої коми.
Ось формула, яка дозволить це зробити:
= ВЛІВО (A2, ЗНАЙТИ ("!", ЗАМІНУТИ (A2, ",", "!", 2))-1)
Оскільки цей набір даних містить кілька ком, я не можу використовувати функцію FIND, щоб отримати позицію першої коми і витягнути все зліва від неї.
Мені потрібно якось з'ясувати положення другої коми, а потім витягнути все, що зліва від другої коми.
Для цього я використав функцію SUBSTITUTE, щоб змінити другу кому на знак оклику. Тепер це дає мені унікальний характер у клітині. Тепер я можу використовувати позицію знака оклику, щоб витягнути все зліва від другої коми.
Ця позиція знака оклику використовується у функції ВЛЕВО для вилучення всього перед другою комою.
Все йде нормально!
Але що робити, якщо в наборі даних є незмінна кількість ком.
Наприклад, у наведеному нижче наборі даних деякі клітинки мають дві коми, а деякі - три коми, і мені потрібно витягнути весь текст перед останньою комою.
У цьому випадку мені потрібно якимось чином з'ясувати позицію останнього входження коми, а потім витягнути все ліворуч від неї.
Нижче наведена формула, яка дозволить це зробити
= LEFT (A2, FIND ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
У наведеній вище формулі використовується функція LEN для визначення загальної довжини тексту в комірці, а також довжини тексту без коми.
Коли я віднімаю ці два значення, це дає мені загальну кількість ком у комірці.
Отже, це дасть мені 3 для комірки А2 і 2 для комірки А4.
Потім це значення використовується у формулі SUBSTITUTE для заміни останньої коми знаком оклику. І тоді ви можете скористатися лівою функцією, щоб витягнути все, що знаходиться ліворуч від знака оклику (там, де раніше була остання кома)
Як ви можете бачити в прикладах, використання комбінації текстових формул дозволяє впоратися з багатьма різними ситуаціями.
Крім того, оскільки результат пов'язаний з вихідними даними, при зміні вихідних даних результат буде автоматично оновлюватися.
Видалити текст за допомогою Flash Fill
Flash Fill - це інструмент, який був представлений в Excel 2013 і доступний у всіх версіях після цього.
Він працює шляхом визначення шаблонів, коли ви вводите дані вручну, а потім екстраполюєте, щоб надати вам дані для всього стовпця.
Отже, якщо ви хочете видалити текст до або після певного символу, вам просто потрібно показати флеш -феї, як би виглядав результат (ввівши його пару разів вручну), а заповнення флеш автоматично зрозуміє шаблон і дасть вам результати.
Дозвольте мені показати вам це на прикладі.
Нижче у мене є набір даних, де я хочу видалити весь текст після коми.
Нижче описано, як це зробити за допомогою Flash Fill:
- У клітинку В2, яка є сусіднім стовпцем наших даних, вручну введіть "Джеффрі Хеггінс" (це очікуваний результат)
- У клітинку В3 введіть "Тім Скотт" (це очікуваний результат для другої клітинки)
- Виберіть діапазон B2: B10
- Натисніть вкладку Домашня сторінка
- У групі «Редагування» натисніть спадне меню «Заповнити»
- Натисніть на Flash Fill
Вищезазначені кроки дадуть вам результат, як показано нижче:
Ви також можете скористатися комбінацією клавіш Flash Fill Control + E після виділення клітинок у стовпці результату (стовпець В у нашому прикладі)
Flash Fill - чудовий інструмент, і в більшості випадків він здатний розпізнати візерунок і дати правильний результат. але в деяких випадках він може бути не в змозі розпізнати візерунок і може дати вам неправильні результати.
Тому обов’язково перевірте результати Flash Fill
І так само, як ми видалили весь текст після певного символу за допомогою флеш -заливки, ви можете використовувати ті ж дії, щоб видалити текст перед певним символом. просто покажіть флеш -заповнення, як результат повинен виглядати як мій Інтернет вручну у сусідньому стовпці, а це зробить все інше.
Видалити текст за допомогою VBA (користувацька функція)
Вся концепція видалення тексту до або після певного символу залежить від визначення позиції цього символу.
Як ніби видно вище, знайти останнє входження цього символу добре означає використання суміші формул.
Якщо вам потрібно це робити досить часто, ви можете спростити цей процес, створивши спеціальну функцію за допомогою VBA (так звані користувальницькі функції).
Після створення цю функцію можна використовувати знову і знову. Він також набагато простіший і легший у використанні (оскільки більшість важких підйомів виконується кодом VBA на задній панелі).
Під кодом VBA, який можна використовувати для створення користувацької функції в Excel:
Функція LastPosition (rCell As Range, rChar As String) 'Ця функція дає останню позицію зазначеного символу' Цей код був розроблений Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) For i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Тоді LastPosition = i - 1 Вихід із функції End Якщо далі Функція i End
Вам потрібно розмістити код VBA у звичайному модулі в редакторі VB або в особистій книзі макросів. Після того, як він у вас є, ви можете використовувати його як будь -яку іншу звичайну функцію аркуша у книзі.
Ця функція бере 2 аргументи:
- Посилання на клітинку, для якої потрібно знайти останнє входження певного символу або текстового рядка
- Символ або текстовий рядок, позицію якого потрібно знайти
Припустимо, тепер у вас є набір даних нижче, і ви хочете видалити весь текст після останньої коми, а текст матиме лише перед останньою комою.
Нижче наведена формула, яка дозволить це зробити:
= ВЛІВО (A2, LastPosition (A2, ",")-1)
У наведеній вище формулі я вказав, щоб знайти позицію останньої коми. Якщо ви хочете знайти позицію якогось іншого символу або текстового рядка, ви повинні використовувати це як другий аргумент у функції.
Як бачите, це набагато коротше і простіше у використанні, порівняно з формулою з довгим текстом, яку ми використовували в попередньому розділі
Якщо ви розмістите код VBA у модулі у книзі, ви зможете використовувати цю спеціальну функцію лише у цій конкретній книзі. Якщо ви хочете використовувати це у всіх робочих книгах у вашій системі, вам потрібно скопіювати та вставити цей код у особисту книгу макросів.
Отже, це деякі з листів, які можна використовувати для швидкого видалення тексту до або після певного символу в Excel.
Якщо це проста разова річ, то ви можете зробити це за допомогою функції «Знайти та замінити». що якщо це трохи складніше, то вам потрібно використовувати комбінацію вбудованих формул Excel або навіть створити власну спеціальну формулу за допомогою VBA.
Сподіваюся, цей підручник був вам корисним.
