✅ Витяг числа з рядка в Excel (за допомогою формул або VBA)

Зміст

Витяг номерів із рядка в Excel (Формула для Excel 2016)
Витяг номерів із рядка в Excel (для Excel 2013/2010/2007)
Розділіть текст і цифри в Excel за допомогою VBA

Переглянути відео - Як витягти числа з текстового рядка в Excel (за допомогою формули та VBA)

В Excel немає вбудованої функції для вилучення чисел із рядка в комірці (або навпаки - видалити числову частину та витягнути текстову частину з буквено -цифрового рядка).

Однак це можна зробити за допомогою коктейлю функцій Excel або простого коду VBA.

Спочатку дозвольте мені показати вам, про що я говорю.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете витягти числа з рядка (як показано нижче):

Вибраний вами метод також буде залежати від версії Excel, яку ви використовуєте:

Для версій до Excel 2016 вам потрібно використовувати трохи довші формули
Для Excel 2016 можна використовувати нещодавно представлену функцію TEXTJOIN
Метод VBA можна використовувати у всіх версіях Excel

Натисніть тут, щоб завантажити файл прикладу

Витяг номерів із рядка в Excel (Формула для Excel 2016)

Ця формула буде працювати тільки в Excel 2016, оскільки вона використовує нещодавно представлену функцію TEXTJOIN.

Крім того, ця формула може витягувати числа, які знаходяться на початку, кінці або посередині текстового рядка.

Зверніть увагу, що формула TEXTJOIN, описана в цьому розділі, дасть вам усі числові символи разом. Наприклад, якщо текст «Ціна 10 квитків становить 200 доларів США», в результаті ви отримаєте 10200.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете витягти числа з рядків у кожній клітинці:

Нижче наведена формула, яка дасть вам числову частину з рядка в Excel.

= TEXTJOIN ("", TRUE, IFERROR ((MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1)*1), "")))

Це формула масиву, тому вам потрібно використовувати "Control + Shift + Enter"Замість використання Enter.

Якщо у текстовому рядку немає чисел, ця формула повертає порожнє місце (порожній рядок).

Як працює ця формула?

Дозвольте мені розірвати цю формулу і спробувати пояснити, як вона працює:

РЯД (НЕПРЯМИЙ (“1:” & ЛЕН (A2))) - ця частина формули дасть ряд чисел, починаючи з одиниці. Функція LEN у формулі повертає загальну кількість символів у рядку. У разі "Вартість 100 доларів США", вона поверне 19. Формули, таким чином, стануть РЯДОМ (НЕПРЯМИЙ ("1:19"). Після цього функція РОД поверне ряд цифр - {1; 2; 3 ; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}
(MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1) - Ця частина формули повертає масив #VALUE! помилки або числа на основі рядка. Усі текстові символи в рядку стають #VALUE! помилки і всі числові значення залишаються такими, які є. Це відбувається, коли ми помножили функцію MID на 1.
IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2))), 1)*1), ””) - Коли використовується функція IFERROR, вона видаляє всі #VALUE! помилки, і залишиться лише цифра. Вихід цієї частини виглядатиме так - {“”; ””; ””; ””; ””; ””; ””; ””; ””; ”” ”;” ”;” ”; ””; ””; ””; 1; 0; 0}
= TEXTJOIN (“”, TRUE, IFERROR ((MID (A2, ROW (INDIRECT (“1:” & LEN (A2)))), 1)*1), ””)) - Функція TEXTJOIN тепер просто поєднує рядкові символи що залишається (які є лише цифрами) та ігнорує порожній рядок.

Професійна порада: Якщо ви хочете перевірити виведення частини формули, виділіть комірку, натисніть F2, щоб увійти в режим редагування, виберіть частину формули, для якої потрібно отримати результат, і натисніть F9. Ви відразу побачите результат. І не забудьте натиснути Control + Z або натиснути клавішу Escape. НЕ натискайте клавішу Enter.

Завантажте файл прикладу

Ви також можете використовувати ту ж логіку для вилучення текстової частини з буквено -цифрового рядка. Нижче наведена формула, яка отримає текстову частину з рядка:

= TEXTJOIN ("", TRUE, IF (ISERROR (MID (A2, ROW (INDIRECT ("1:" & LEN (A2)))), 1)*1), MID (A2, ROW (INDIRECT ("1:" & LEN (A2))), 1), ""))

Незначна зміна цієї формули полягає в тому, що функція IF використовується для перевірки того, чи є масив, який ми отримуємо від функції MID, помилками чи ні. Якщо це помилка, він зберігає значення, інакше замінює його порожнім.

Потім TEXTJOIN використовується для об'єднання всіх текстових символів.

Обережно: Хоча ця формула чудово працює, вона використовує мінливу функцію (функція INDIRECT). Це означає, що у випадку використання цього величезного набору даних може знадобитися деякий час, щоб отримати результати. Найкраще створити резервну копію перед використанням цієї формули в Excel.

Витяг номерів із рядка в Excel (для Excel 2013/2010/2007)

Якщо у вас Excel 2013. 2010. або 2007, ви не можете використовувати формулу TEXTJOIN, тому вам доведеться використовувати складну формулу, щоб це зробити.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете витягнути всі числа в рядку в кожній клітинці.

Нижче наведена формула дозволить це зробити:

= IF (SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7", " 8 "," 9 "}" 1)) * ROW (INDIRECT ("$ 1: $" & LEN (A2))), 0), ROW (INDIRECT ("$ 1: $" & LEN (A2))))+1,1) * 10^ROW (INDIRECT ("$ 1: $" & LEN (A2)))/10), "")

Якщо у текстовому рядку немає номера, ця формула повертає порожнє значення (порожній рядок).

Хоча це формула масиву, ви не потрібно використовувати "Control-Shift-Enter" для цього. Для цієї формули працює просте введення.

Завдяки цій формулі заслуговує дивовижний форум пана Екселя.

Знову ж таки, ця формула вилучить усі числа в рядку незалежно від позиції. Наприклад, якщо текст "Ціна 10 квитків становить 200 доларів США", в результаті ви отримаєте 10200.

Розділіть текст і цифри в Excel за допомогою VBA

Якщо вам часто доводиться відокремлювати текст і числа (або витягати числа з тексту), ви також можете скористатися методом VBA.

Все, що вам потрібно зробити, це використати простий код VBA для створення користувацької функції, визначеної користувачем (UDF) в Excel, а потім, замість використання довгих і складних формул, використовувати цю формулу VBA.

Дозвольте мені показати вам, як створити дві формули у VBA - одну для вилучення чисел та іншу для вилучення тексту з рядка.

Витягти номери з рядка в Excel (за допомогою VBA)

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

Нижче наведено код VBA, який ми будемо використовувати для створення цієї користувацької функції:

Функція GetNumeric (CellRef як рядок) Dim StringLength як ціле число StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Тоді Result = Результат & Mid (CellRef, i, 1) Далі i GetNumeric = Кінцева функція результату

Нижче наведено кроки для створення цієї функції, а потім використання її на робочому аркуші:

Перейдіть на вкладку Розробник.
Натисніть на Visual Basic (Ви також можете скористатися комбінацією клавіш ALT + F11)
У вікні редактора VB, що відкриється, клацніть правою кнопкою миші будь-який об’єкт книги.
Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до книги.
У вікні коду модуля скопіюйте та вставте вищевказаний код VBA.
Закрийте редактор VB.

Тепер ви зможете використовувати функцію GetText на робочому аркуші. Оскільки ми виконали всю важку роботу в самому коді, все, що вам потрібно зробити, це використати формулу = GetNumeric (A2).

Це миттєво дасть вам лише числову частину рядка.

Зауважте, що оскільки у книзі зараз є код VBA, її потрібно зберегти за допомогою розширення .xls або .xlsm.

Завантажте файл прикладу

Якщо вам доведеться часто використовувати цю формулу, ви також можете зберегти її у своїй особистій книзі макросів. Це дозволить вам використовувати цю спеціальну формулу в будь -якій книзі Excel, з якою ви працюєте.

Витяг тексту з рядка в Excel (за допомогою VBA)

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

Нижче наведено код VBA, який ми будемо використовувати для створення цієї користувацької функції:

Функція GetText (CellRef як рядок) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Тоді Result = Result & Mid (CellRef, i, 1 ) Далі i GetText = Функція завершення результату

Нижче наведено кроки для створення цієї функції, а потім використання її на робочому аркуші:

Перейдіть на вкладку Розробник.
Натисніть на Visual Basic (Ви також можете скористатися комбінацією клавіш ALT + F11)
У вікні редактора VB, що відкриється, клацніть правою кнопкою миші будь-який об’єкт книги.
Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до книги.
- Якщо у вас вже є модуль, двічі клацніть на ньому (не потрібно вставляти новий, якщо він у вас уже є).
У вікні коду модуля скопіюйте та вставте вищевказаний код VBA.
Закрийте редактор VB.

Тепер ви зможете використовувати функцію GetNumeric на робочому аркуші. Оскільки ми виконали всю важку роботу в самому коді, все, що вам потрібно зробити, це використати формулу = GetText (A2).

Це миттєво дасть вам лише числову частину рядка.