Завантажте зразок робочої книги
Цей підручник буде продемонструвати вам, як знайти і витягти число з текстового рядка в Excel і Google Таблицях.
Знайти та витягнути номер із рядка
Іноді ваші дані можуть містити цифри та текст, і ви хочете витягти числові дані.
Якщо числова частина знаходиться праворуч або ліворуч від рядка, розділити число та текст відносно легко. Однак, якщо числа всередині рядка, тобто між двома текстовими рядками, вам доведеться використовувати набагато складнішу формулу (показану нижче).
TEXTJOIN - Вилучення номерів у Excel 2016+
В Excel 2016 була введена функція TEXTJOIN, яка може витягувати числа з будь -якого місця текстового рядка. Ось формула:
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, ROW (INDIRECT ("1:" & LEN (B3)))), 1)*1), "")) |
Давайте подивимося, як працює ця формула.
Функції ROW, INDIRECT та LEN повертають масив чисел, відповідних кожному символу у вашому буквено -цифровому рядку. У нашому випадку “Monday01Day” містить 11 символів, тому функція ROW поверне масив {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}.
| 1 | = TEXTJOIN ("", TRUE, IFERROR ((MID (B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, 1)*1), ""))) |
Далі функція MID витягує кожен символ із текстового рядка, створюючи масив, що містить ваш вихідний рядок:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({"M"; "o"; "n"; "d"; "a"; "y"; "0"; "1"; "D"; "a ";" y "}*1)," ")) |
Помноження кожного значення в масиві на 1 створить масив, що містить помилки, якщо символ масиву був текстом:
| 1 | = TEXTJOIN ("", TRUE, IFERROR (({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!; 0; 1; #VALUE!;#VALUE!;#VALUE!) !}), ""))) |
Далі, функція IFERROR видаляє значення помилок:
| 1 | = TEXTJOIN ("", TRUE, {""; ""; ""; ""; ""; ""; 0; 1; ""; ""; ""}) |
Залишаючи лише функцію TEXTJOIN, яка об’єднує інші числа разом.
Зауважте, що формула дасть вам усі числові символи разом із вашого рядка. Наприклад, якщо ваш буквено -цифровий рядок - Monday01Day01, у результаті ви отримаєте 0101.
Витяг номерів - до Excel 2016
До Excel 2016 ви можете використовувати набагато більш складний метод для вилучення чисел з тексту. Ви можете використовувати функцію FIND разом з функціями IFERROR та MIN для визначення як першої позиції числової частини, так і першої позиції текстової частини після числа. Потім ми просто витягуємо числову частину за допомогою функції MID.
| 1 | = MID (B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999)), MIN (IFERROR (SEARCH ({"a" , "b", "c", "d", "e", "f", "g", "h", "I", "j", "k", "l", "m", " n "," o "," p "," q "," r "," s "," t "," u "," v "," w "," x "," y "," z " }, B3, MIN (IFERROR (FIND ({0,1,2,3,4,5,6,7,8,9}, B3), 999999999))), 999999999))-MIN (IFERROR (FIND (FIND ( {0,1,2,3,4,5,6,7,8,9}, В3), 999999999))) |
Примітка: Це формула масиву, яку потрібно ввести, натиснувши CTRL + SHIFT + ENTER, а не просто ENTER.
Давайте покроково розглянемо, як працює ця формула.
Знайдіть номер кулака
Ми можемо використовувати функцію FIND для визначення початкової позиції числа.
| 1 | = MIN (IFERROR (НАЙДИ ({1,2,3,4,5,6,7,8,9,0}, B3), 999999999)) |
Для аргументу find_text функції FIND ми використовуємо константу масиву {0,1,2,3,4,5,6,7,8,9}, що змушує функцію FIND виконувати окремий пошук для кожного значення в константа масиву.
Аргумент Within_text функції FIND у нашому випадку - Monday01Day, в якому 1 можна знайти в позиції 8, а 0 у позиції 7, тому наш масив результатів буде таким: {8,#VALUE,#VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 7}.
Використовуючи функцію IFERROR, ми замінюємо помилки #VALUE на 999999999. Потім ми просто шукаємо мінімум у цьому масиві і отримуємо, отже, місце першого числа (7).
Зверніть увагу, що наведена вище формула є формулою масиву, для її запуску потрібно натиснути Ctrl+Shift+Enter.
Знайти перший текст після цифри
Подібно до визначення першого числа в рядку, ми використовуємо функцію FIND, щоб визначити, де текст починається знову після номера, добре використовуючи також аргумент start_num функції.
| 1 | = MIN (IFERROR (FIND ({"a", "b", "c", "d", "e", "f", "g", "h", "I", "j", "k "," l "," m "," n "," o "," p "," q "," r "," s "," t "," u "," v "," w ", "x", "y", "z"}, B3, C3), 999999999)) |
Ця формула працює дуже подібно до попередньої, яка використовувалася для визначення першого числа, тільки ми використовуємо букви в константах масиву, а тому цифри викликають помилки #VALUE. Зверніть увагу, що ми починаємо пошук лише після позиції, визначеної для першого числа (це буде аргумент start_num), а не з початку рядка.
Не забудьте натиснути Ctrl+Shift+Enter, щоб скористатися наведеною вище формулою.
Не залишається нічого, як об'єднати все це разом.
Витяг номер з двох текстів
Після того, як у нас є вихідне положення числової частини та початок текстової частини після цього, ми просто використовуємо функцію MID для вилучення потрібної частини числа.
| 1 | = MID (B3, C3, D3-C3) |
Інший метод
Не пояснюючи цього більш детально, ви також можете скористатися наведеною нижче складною формулою, щоб отримати число всередині рядка.
| 1 | = SUMPRODUCT (MID (0 & B3, LARGE (INDEX (ISNUMBER (-MID (B3, ROW (INDIRECT ("1:" & LEN (B3))), 1))**ROW (INDIRECT ("1:" & LEN (B3) )), 0), ROW (INDIRECT ("1:" & LEN (B3))))+1,1)*10^ROW (INDIRECT ("1:" & LEN (B3)))/10) |
Майте на увазі, що ця формула вище дасть вам 0, якщо в рядку не знайдено жодного числа, і що пропущені нулі будуть залишені.
Знайдіть та витягніть номер із рядка в текст у Таблицях Google
Наведені вище приклади працюють у Google Таблицях так само, як і в Excel.
