Підручник Excel – витягніть текст або число за певною позицією
У багатьох випадках вам може знадобитися лише витягти корисний вміст із речення або текстового рядка в клітинці, наприклад, витягти провінцію з адреси, витягти адресу електронної пошти з речення, витягти номер рахунка кур’єра з розмови, і так далі. Цей посібник звужує вилучення до певної позиції в комірці та збирає різні методи, які допомагають витягувати текст або числа з комірки за певною позицією в Excel.
Зміст: [ Приховати ]
Витягувати текст за позицією
У цьому розділі зібрано загальні положення, де текст можна витягти з комірки, і надаються відповідні методи для їх поетапної обробки. Ви можете переглянути докладнішу інформацію.
1. Витягніть кількість символів зліва або справа
Щоб отримати кількість символів з лівої або правої сторони рядка, ви можете спробувати один із наведених нижче методів.
1.1 Витягніть перший або останній N символів за допомогою формул
Припустимо, що у вас є список текстових рядків у стовпці B, як показано на знімку екрана нижче, для вилучення перших 2 символів і останніх 2 символів з кожного рядка можна застосувати наступні формули.
Витягніть перші N символів з текстового рядка
Функція LEFT може допомогти легко витягти перші N символів із текстового рядка в Excel.
Загальна формула
=LEFT(text_string,[num_chars])
Аргументи
Тепер ви можете застосувати цю формулу, щоб витягти перші 2 символи з комірок у стовпці B.
1. Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати перший результат. Виберіть комірку результату та перетягніть її маркер автозаповнення вниз, щоб застосувати формулу до інших клітинок.
=LEFT(B5,2)
Тепер перші 2 символи в кожній клітинці діапазону B5:B10 витягнуто.
Витягніть останні N символів з текстового рядка
Тут ми застосовуємо функцію RIGHT для вилучення останніх N символів з текстового рядка в Excel.
Загальна формула
=RIGHT(text_string,[num_chars])
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Потім виділіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати інші результати.
=RIGHT(B5,2)
1.2 Витягніть перший або останній N символів за допомогою дивовижного інструменту
Хоча формули, наведені вище, прості, для вилучення перших або останніх n символів з довгого списку текстового рядка вам все одно потрібно перетягувати маркер автозаповнення зверху вниз, що може зайняти трохи часу. Тут рекомендує Kutools для ExcelАвтора Витяг тексту утиліта, яка допомагає масово витягти перші або останні N символів зі списку текстових рядків.
1. Заздалегідь виберіть список текстових рядків, з якого потрібно витягти текст, і клацніть Кутулс > текст > Витяг тексту.
2. У спливаючому Витяг тексту діалогове вікно, вам потрібно налаштувати наступним чином.
примітки: щоб зробити результат динамічним при зміні текстового рядка, ви можете перевірити Вставте як формулу коробка
3. У наступному спливаючому вікні Витяг тексту діалоговому вікні виберіть клітинку для виведення витягнутих символів, а потім клацніть OK.
Потім вказані перші або останні N символів витягуються з вибраних клітинок.
Натисніть, щоб дізнатися більше про цю функцію.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
2. Витягніть текст до або після певного символу/слова
Щоб витягти текст до або після певного символу чи слова, різні сценарії в цьому розділі задовольнять ваші потреби.
2.1 Витяг тексту до або після першого роздільника (символу)
Як показано на знімку екрана нижче, щоб витягти текст до або після першого роздільника з кожної клітинки в діапазоні B4:B10, ви можете застосувати один із наведених нижче методів.
2.1.1 Витягніть текст перед першим роздільником за допомогою формули
Застосування формули на основі функцій LEFT і FIND може допомогти витягти текст перед першим роздільником з комірки. Щоб зробити це, виконайте наведені нижче дії.
Загальна формула
=LEFT(text_string,FIND("delimiter",text_string,1)-1)
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть в неї формулу, наведену нижче, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати перший результат. Виберіть першу клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати тексти перед першим роздільником інших комірок.
=LEFT(B5,FIND("-",B5,1)-1)
2.1.2 Витяг тексту після першого роздільника за допомогою формули
Наведена нижче формула допомагає витягти текст після першого роздільника з комірки в Excel.
Загальна формула
=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))
Аргументи
=MID(B5,FIND("-",B5)+1,LEN(B5))
2.1.3 Витягніть текст до або після першого роздільника за допомогою дивовижного інструменту
Тут настійно рекомендуємо Витяг тексту корисність Kutools для Excel. За допомогою цієї функції ви можете легко витягувати текст до або після першого роздільника з діапазону комірок.
1. Виділіть діапазон комірок, у які потрібно витягти текст, а потім клацніть Кутулс > текст > Витяг тексту.
2 В Витяг тексту діалогове вікно, вам потрібно налаштувати наступним чином.
Примітка: Щоб зробити результат динамічним при зміні текстового рядка, ви можете встановити прапорець Вставити як формулу.
3. Потім інший Витяг тексту з’явиться діалогове вікно, виберіть клітинку для виведення результатів і клацніть ОК.
Тоді тексти до або після першого роздільника витягуються з виділених комірок відразу.
Щоб дізнатися більше про цю функцію, відвідайте: Швидко витягніть певний текст із клітинок у Excel.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
2.2 Витяг тексту до або після останнього роздільника (символу)
У наведених вище кроках ми дізналися про методи вилучення тексту до або після першого роздільника з клітинки. Як показано на знімку екрана нижче, цей розділ покаже вам дві формули для вилучення тексту до або після останнього роздільника з комірки. Щоб зробити це, виконайте наведені нижче дії.
2.2.1 Витяг тексту перед останнім роздільником за допомогою формули
Щоб витягти текст перед останнім роздільником з комірки, можна використовувати функції ПОШУК, ЛЕНН і ЗАМІНА всередині функції ЛІВО.
Загальна формула
=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)
Аргументи
Виділіть комірку, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату та перетягніть її маркер автозаповнення вниз, щоб витягти тексти з інших текстових рядків у тому самому стовпці.
=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
2.2.2 Витяг тексту після останнього роздільника за допомогою формули
Після вилучення тексту перед останнім роздільником з комірки, ви можете застосувати формулу нижче, щоб витягти текст після останнього роздільника, як вам потрібно.
Загальна формула
=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))
Аргументи
Виділіть комірку, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату та перетягніть її маркер автозаповнення вниз, щоб витягти тексти з інших текстових рядків у тому самому стовпці.
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))
2.3 Витяг тексту після n-го символу
Подивіться на приклад нижче, є список текстових рядків у діапазоні B4:B10, щоб витягти текст після третього символу з кожної клітинки, ви можете застосувати формулу на основі функції MID і функції LEN.
Загальна формула
=MID(text_string,nth_char+1,LEN(text_string))
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть в неї формулу, наведену нижче, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати інші результати.
=MID(B5,3+1,LEN(B5))
2.4 Витягніть n-е слово з текстового рядка
Припустимо, що у вас є список текстових рядків, як показано на скріншоті нижче, і ви хочете витягти лише n-е слово з текстового рядка, у цьому розділі передбачено три способи, щоб це зробити.
2.4.1 Витягніть n-е слово за допомогою формули
Ви можете комбінувати функції TRIM, MID, SUBSTITUTE, REPT і LEN, щоб витягти n-е слово з текстового рядка в комірці.
Загальна формула
=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))
Аргументи
У цьому випадку діапазон B5:B10 містить текстові рядки, D5:D10 містить числа, що представляють n-е слово, давайте застосуємо цю формулу для вилучення n-го слова з текстового рядка.
Виділіть порожню клітинку, скопіюйте або введіть в неї формулу, наведену нижче, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати перший результат. Виберіть цю комірку результату та перетягніть її маркер автозаповнення вниз, щоб отримати n-е слово інших комірок.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))
Примітка: Ви можете безпосередньо ввести n-е число у формулу, як показано нижче.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))
2.4.2 Витягніть n-е слово за допомогою функції, визначеної користувачем
Окрім наведеної вище формули, ви також можете застосувати визначену користувачем функцію для вилучення n-го слова з комірки в Excel.
1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль а потім скопіюйте VBA нижче у вікно коду.
Код VBA: витягніть n-е слово з текстового рядка в комірці
Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
FindWord = ""
Else
FindWord = arr(Position - 1)
End If
End Function
3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.
4. Поверніться до аркуша, що містить текстові рядки, з яких потрібно витягти n-е слово. Виділіть порожню клітинку, скопіюйте або введіть в неї формулу, наведену нижче, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати n-е слово.
=FindWord(B5,D5)
Or
=FindWord(B5,2)
Примітка: у формулі D5 — це клітинка, яка містить число, що представляє n-те слово. Крім того, ви можете безпосередньо замінити посилання на комірку числом.
5. Виберіть комірку результату та перетягніть її маркер автозаповнення вниз, щоб витягти n-е слово з текстових рядків інших комірок.
2.4.3 Витягніть n-е слово за допомогою дивовижного інструменту
Якщо ви не хочете вручну застосовувати формулу або визначену користувачем функцію, наведену вище, тут рекомендується Kutools для Excel Витягніть n-те слово в клітинку корисність. За допомогою цієї функції ви можете легко витягти n-е слово з текстового рядка в клітинці лише кількома клацаннями.
1. Виберіть комірку для розміщення результату та клацніть Кутулс > Помічник формули > текст > Витяг n-е слово в клітинці. Дивіться знімок екрана:
2 В Помічник формул діалогове вікно, вам потрібно налаштувати наступним чином.
3. Потім n-е (друге) слово витягується з текстового рядка в комірці B5, і ви бачите, що одночасно створюється формула. Виберіть цю комірку результату та перетягніть її маркер автозаповнення вниз, щоб отримати n-е слово з інших текстових рядків.
Натисніть, щоб дізнатися більше про цю функцію.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
2.5 Витяг тексту до або після n-го входження роздільника
Припустимо, у вас є список текстових рядків, як показано на скріншоті нижче. Щоб витягти текст до або після другого входження пробілу, у цьому розділі наведено дві формули, які допоможуть вам це зробити.
2.5.1 Витяг тексту перед n-м входженням роздільника
Ви можете використовувати функцію LEFT разом із функціями SUBSTITUTE та FIND для вилучення тексту перед n-м входженням роздільника з комірки в Excel.
Загальна формула
=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)
Аргументи
Виділіть клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати інші результати у списку.
=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)
Примітка: У формулі B5 — це клітинка, що містить текстовий рядок, з якого потрібно витягти текст; “ ” тут позначає пробіл, а число 2 позначає друге входження пробілу. Ви можете змінити їх відповідно до ваших потреб.
2.5.2 Витяг тексту після n-го входження роздільника
Щоб витягти текст після n-го входження роздільника, можна застосувати функцію ВПРАВО з функціями ЗАМІНИ, ЛЕНН і ЗНАЙТИ.
Загальна формула
=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)
Аргументи
Тепер ви можете застосувати цю формулу для вилучення тексту після другого входження пробілу з кожної клітинки в діапазоні B5:B10, як показано нижче.
Виділіть комірку, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю клітинку результату та перетягніть її маркер автозаповнення вниз, щоб отримати інші результати.
=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)
2.6 Витяг тексту до або після розриву рядка
Припустимо, що у вас є список замовлень у стовпці B, і ви хочете витягти лише частину дати та частину продукту з кожної клітинки. Ви можете використовувати наведені нижче формули Excel, щоб зробити це.
2.6.1 Витяг тексту перед розривом першого рядка за допомогою формули
Як ви можете бачити на знімку екрана вище, частина дати розташовується перед першим розривом рядка в клітинці. У цьому розділі буде продемонстровано функцію ЛІВО разом із функцією SEARCH, щоб допомогти вам витягти текст перед розривом першого рядка в клітинці.
Загальна формула
=LEFT(cell, SEARCH(CHAR(10), cell)-1)
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть в неї формулу, наведену нижче, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=LEFT(B5, SEARCH(CHAR(10), B5)-1)
Тоді ви зможете побачити текст перед тим, як витягується перший розрив рядка в кожній клітинці в діапазоні B5:B8, як показано на знімку екрана нижче.
Примітка: У формулі CHAR(10) представляє розрив рядка в Windows.
2.6.2 Витяг тексту після останнього розриву рядка за допомогою формули
На попередньому кроці ми говорили про те, як витягти текст перед розривом першого рядка в клітинці. І ця частина допоможе вам витягти текст після останнього розриву рядка в клітинці за допомогою іншої формули.
Загальна формула
=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))
Аргументи
Виділіть порожню клітинку, введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати формулу до інших клітинок.
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Тоді товар №. частина кожної клітинки в списку витягується, як показано на знімку екрана вище.
Примітка: У формулі CHAR(10) представляє розрив рядка в Windows.
2.7 Витяг тексту до або після слова
У попередніх частинах ми дізналися, як витягувати текст до або після символу чи роздільника. Що потрібно зробити, щоб витягнути текст перед або після цілого слова? У цьому розділі будуть представлені три методи, які допоможуть вам виконати це завдання.
2.7.1 Витяг тексту перед певним словом за допомогою формули
Наведена нижче формула допомагає витягти текст перед певним словом у клітинці в Excel.
Загальна формула
=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)
Аргументи
Виділіть порожню клітинку, введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
У цьому прикладі ми збираємось витягти всі тексти перед словом «Excel», тому ми безпосередньо вводимо слово у формулу та беремо його в подвійні лапки. Або ви можете посилатися на клітинку, яка містить слово «Excel».
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
Примітки:
2.7.2 Витяг тексту після певного слова за допомогою формули
Щоб витягти текст після певного слова, ви можете застосувати наступну формулу.
Загальна формула
=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))
Аргументи
Виділіть комірку, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))
Потім ви зможете побачити всі тексти після того, як слово «Excel» у кожній клітинці буде витягнуто, як показано на знімку екрана нижче.
Примітки:
2.7.3 Витягніть текст перед або після певного слова за допомогою дивовижного інструменту
Якщо ви вважаєте, що використання формули може спричинити багато незручностей, настійно рекомендуємо тут Витяг тексту корисність Kutools для Excel. Ця функція допомагає автоматизувати завдання вилучення в Excel за кілька кліків.
1. клацання Кутулс > текст > Витяг тексту щоб увімкнути цю функцію.
2 В Витяг тексту діалогове вікно, виконайте такі налаштування.
Примітки: Якщо ви хочете створити динамічні результати, поставте прапорець Вставте як формулу коробка. Тоді результати автоматично оновляться, коли дані в діапазоні зміняться.
3. Потім an Витяг тексту з’явиться діалогове вікно, вам потрібно вибрати комірку для виведення результату, а потім натиснути кнопку OK кнопки.
Тоді тексти до або після певного слова в кожній клітинці у вибраному діапазоні відразу витягуються.
Примітка: Ця функція чутлива до регістру.
Натисніть, щоб дізнатися більше про цю функцію.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
3. Виділення між символами/словами
Якщо ви хочете виділити текст між певними символами або словами, спробуйте наступні методи.
3.1 Витяг тексту між двома символами
Щоб витягти текст між двома символами, які можуть бути однаковими або різними. У цьому розділі наведено кілька методів, і ви можете вибрати один з них відповідно до ваших потреб.
3.1.1 Витяг тексту між двома однаковими символами за допомогою формули
Як показано на знімку екрана нижче, у стовпці B є список текстових рядків, і ви хочете витягти числову частину між символами «/» з кожної клітинки в діапазоні, наступна формула може зробити вам послугу.
Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб отримати результати інших комірок у списку.
=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")
Потім текст між двома однаковими символами «/» витягується з кожної клітинки діапазону. Дивіться знімок екрана:
Примітка:
3.1.2 Витяг тексту між двома різними символами за допомогою формули
Після вивчення того, як витягувати текст між двома однаковими символами в клітинці, ми продемонструємо формулу для вилучення тексту між двома різними символами. Як показано на знімку екрана нижче, щоб отримати лише адресу електронної пошти між «<» і «>» з кожної клітинки в стовпці B, ви можете зробити наступне.
Загальна формула
=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
Ви можете побачити, що витягується лише текст між вказаними символами, як показано на знімку екрана вище.
3.1.3 Витягніть текст між двома символами за допомогою дивовижного інструменту
Тут настійно рекомендуємо Витяг рядків між зазначеним текстом особливість Kutools для Excel щоб допомогти вам легко витягувати текст між двома однаковими або різними символами в клітинці в Excel.
1. Виберіть порожню комірку для виведення результату, а потім натисніть Кутулс > Помічник формули > Помічник формули.
2 В Помічник формул діалогове вікно, виконайте такі налаштування.
3. Потім витягується лише текст між «<» і «>» у клітинці B5. Тим часом формула була створена, ви можете вибрати цю комірку результату, а потім перетягнути її маркер автозаповнення вниз, щоб витягти тексти з інших комірок у тому самому списку.
Натисніть, щоб дізнатися більше про цю функцію.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
3.1.4 Витягувати текст між двома символами (включаючи символи) за правилом
Якщо ви хочете зберегти два символи після вилучення, спробуйте застосувати правило у функції «Витягти текст» Kutools для Excel.
1. клацання Кутулс > текст > Витяг тексту.
2 В Витяг тексту діалогове вікно, виконайте такі налаштування.
3. Інший Витяг тексту з’явиться діалогове вікно, виберіть клітинку для виведення результату, а потім натисніть кнопку OK кнопки.
Потім текст між вказаними символами (включаючи символи) витягується з кожної клітинки у вибраному діапазоні масово.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
3.2 Витягніть текст між двома словами
Окрім вилучення тексту між двома символами, вам також може знадобитися витягувати текст між двома словами. Наприклад, витягніть усі текстові рядки між двома словами «KTE» і «feature» з кожної клітинки в стовпці B, як показано на знімку екрана нижче. Ви можете спробувати один із наступних методів, щоб це зробити.
3.2.1 Витяг тексту між двома словами за допомогою формули
Ви можете використовувати формулу, засновану на функції MID і функції SEARCH, щоб витягти всі текстові рядки між двома словами в клітинці.
Загальна формула
=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
примітки: У формулі цифра 3 представляє довжину символу слова «KTE»; цифра 4 позначає довжину символу слова "KTE" плюс 1.
Ви можете побачити, що всі текстові рядки між вказаними двома словами витягуються з кожної клітинки в стовпці B.
3.2.2 Витягніть текст між двома словами за допомогою дивовижного інструменту
Багатьом користувачам Excel формули можуть бути важко запам’ятовувати та обробляти. Тут, з Витяг рядків між зазначеним текстом особливість Kutools для Excel, ви можете легко витягувати текст між двома словами лише кількома клацаннями.
1. Виберіть клітинку для виведення результату, а потім клацніть Кутулс > Помічник формули > Помічник формули.
2 В Помічник формули діалогове вікно, вам потрібно налаштувати наступним чином.
3. Потім усі текстові рядки між двома словами «KTE» та «feature» у клітинці B5 витягуються. Тим часом формула була створена, ви можете вибрати цю комірку результату, а потім перетягнути її маркер автозаповнення вниз, щоб витягти тексти з інших комірок у тому самому списку.
Якщо ви хочете отримати безкоштовну пробну версію (30 днів) цієї програми, натисніть, щоб завантажити, а потім перейдіть до застосування операції, як описано вище.
Вилучення чисел за позицією
Для списку буквено-цифрових рядків може бути три випадки:
- Номер на початку тексту;
- Номер в кінці тексту;
- Номер може бути в будь-якому місці тексту.
У цьому розділі ми надамо різні методи, які можна використовувати для отримання чисел у кожному випадку, згаданому вище.
1 Витягніть число з лівої частини рядка
У цій частині буде представлена формула, яка допоможе вам витягти лише числа, які з’являються перед текстом у клітинці.
Загальна формула
=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)
Аргументи
Примітка:
Виберіть порожню клітинку, введіть формулу нижче та натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у or Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб отримати кількість інших комірок.
=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)
Примітки:
2 Витягніть число справа від рядка
Як показано на скріншоті нижче, щоб витягти лише числа, які з’являються після тексту в клітинці, спробуйте скористатися такою формулою.
Загальна формула
=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))
Аргументи
Примітка:
Виберіть порожню клітинку, введіть формулу нижче та натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у or Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб отримати кількість інших комірок.
=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))
Примітки:
3. Витягніть усі числа з будь-якої позиції текстового рядка
Наведені вище методи допомагають витягувати число лише зліва або справа від текстового рядка. Якщо ви хочете витягти всі числа з будь-якого місця в текстовому рядку, ми пропонуємо вам три способи, щоб це зробити.
3.1 Витягніть усі числа з будь-якого місця рядка за допомогою формули
Ви можете застосувати таку формулу, щоб витягти всі числа з будь-якого місця текстового рядка в Excel.
1. Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати всі числа з клітинки B5.
=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)
2. Виберіть комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб отримати всі номери інших комірок.
3.2 Витягніть усі числа з будь-якого місця рядка за допомогою VBA
Формула вище занадто довга та складна для багатьох користувачів Excel. Насправді, ви можете запустити сценарій VBA для автоматизації завдання в Excel. Ви можете зробити наступним чином.
1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. На відкритті Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль. Потім скопіюйте наведений нижче VBA у вікно коду модуля.
Код VBA: витягніть усі числа з будь-якого місця текстового рядка
Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
Dim xRg As Range
Dim xDRg As Range
Dim xRRg As Range
Dim nCellLength As Integer
Dim xNumber As Integer
Dim strNumber As String
Dim xTitleId As String
Dim xI As Integer
xTitleId = "KutoolsforExcel"
Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
If TypeName(xDRg) = "Nothing" Then Exit Sub
Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
If TypeName(xRRg) = "Nothing" Then Exit Sub
xI = 0
strNumber = ""
For Each xRg In xDRg
xI = xI + 1
nCellLength = Len(xRg)
For xNumber = 1 To nCellLength
If IsNumeric(Mid(xRg, xNumber, 1)) Then
strNumber = strNumber & Mid(xRg, xNumber, 1)
End If
Next xNumber
xRRg.Item(xI) = strNumber
strNumber = ""
Next xRg
End Sub
3 Натисніть кнопку F5 клавіша для запуску коду. На відкритті Kutools для Excel діалоговому вікні виберіть діапазон комірок, у який потрібно витягти всі числа з кожної клітинки, а потім клацніть OK кнопки.
4. Потім інший Kutools для Excel з'явиться діалогове вікно. У цьому діалоговому вікні виберіть цільову клітинку та клацніть ОК.
Потім усі числа витягуються з кожної клітинки у вибраному діапазоні.
4. Витягніть числа після певного тексту
Як показано на знімку екрана нижче, щоб витягти будь-які числа після конкретного тексту «Ні», у цьому розділі надано два способи, які допоможуть вам це зробити.
4.1 Витягувати числа після певного тексту за допомогою формули
Ви можете застосувати таку формулу для вилучення чисел після певного тексту в комірці в Excel.
Загальна формула:
=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))
Аргументи
Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))
Примітки:
4.2 Витягувати числа після певного тексту за допомогою визначеної користувачем функції
Наступна визначена користувачем функція також може допомогти витягнути числа після певного тексту в клітинці. Будь ласка, зробіть наступне.
1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль а потім скопіюйте наведений нижче код VBA у вікно коду модуля.
Код VBA: витягує числа після певного тексту в клітинку
Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
xValue = Rng.Text
xStart = InStr(1, xValue, Char, vbTextCompare)
If IsEmpty(xStart) Then
GetNumberAfterTheChar = ""
Exit Function
End If
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xStart = xStart - 1 + Len(Char)
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xValue = Mid(xValue, xStart + 1)
xRntString = ""
For xI = 1 To Len(xValue)
xC = Mid(xValue, xI, 1)
Select Case Asc(xC)
Case 48 To 57
xRntString = xRntString & xC
Case Else
Exit For
End Select
Next
GetNumberAfterTheChar = xRntString
End Function
3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.
4. Виберіть комірку, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виберіть цю комірку результату, а потім перетягніть її маркер автозаповнення вниз, щоб застосувати цю формулу до інших клітинок.
=GetNumberAfterTheChar(B5,"No. ")
Примітки:
Статті по темі:
Підручник з Excel: розділити клітинки тексту, числа та дати (розділити на кілька стовпців)
Цей посібник поділено на три частини: розділені клітинки тексту, розділені клітинки з числами та розділені клітинки дати. Кожна частина містить різні приклади, які допоможуть вам зрозуміти, як впоратися з завданням поділу, коли зіткнулися з тією ж проблемою.
Натисніть, щоб дізнатись більше ...
Excel Додайте текст і номер у вказане положення комірки
У Excel додавання текстів або чисел до клітинок є дуже поширеною роботою. Наприклад, додавання пробілу між іменами, додавання префікса чи суфікса до клітинок, додавання тире до соціальних номерів. Тут, у цьому підручнику, перелічено майже всі сценарії додавання в Excel та надано відповідні методи для вас.
Натисніть, щоб дізнатись більше ...
Excel видаляє символи, слова, числа з текстових рядків
Припустимо, у вас є довгий список текстових рядків, які містять символи, цифри або інші специфічні символи. У певних випадках вам може знадобитися видалити деякі символи на основі позиції, наприклад, справа, зліва чи посередині з текстових рядків, або видалити деякі небажані символи, числа зі списку рядків. Знаходження рішень по одному призведе до головного болю. У цьому посібнику зібрано всі види методів видалення символів, слів або чисел у Excel.
Натисніть, щоб дізнатись більше ...
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!