Як витягти число з текстового рядка в окремий осередок Excel

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

Але ми можемо використовувати комбінацію функцій або Visual Basic для виконання цього завдання.

Що я?

Припустимо, у нас є звичайний осередок з текстом, в якому є ще й число (приклад на малюнку нижче):

Як витягти число з текстового рядка в окремий осередок Excel

І нам необхідно це число записати в окремому осередку.

Тож почнемо!

Як отримати число з текстового рядка в Excel 2016

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

Ми будемо використовувати функцію ОБ'ЄДНАТИ. Що вона робить? Вона отримує всі чисельні значення з рядка. Наприклад, у нас є рядок: “Вітя купив морозива за 100 рублів, а Вася за 20 рублів”. Результатом виконання функції буде число “10020”.

Формула:

=ОБ'ЄДНАТИ(“”;ІСТИНА;ЄСЛИПОМИЛКА((ПСТР(A2;РЯДОК(ДВССИЛ(“1:”&ДЛСТР(A2)));1)*1);””))

Як витягти число з текстового рядка в окремий осередок Excel

Оскільки ми працюємо з масивом, після написання формули в комірку натисніть CTRL+SHIFT+ENTER.

У випадку, коли в рядку немає жодної цифри, результат виконання функції буде порожній рядок.

Що робить функція?

Давайте розберемо функцію частинами:

  • РЯДОК(ДВССИЛ(«1:»&ДЛСТР(A2))) — обчислення почнеться з функції ДЛСТР, вона поверне нам довжину рядка. Функція рядок поверне масив чисел починаючи з 1 і закінчуючи тим числом, яке дорівнює довжині рядка.
  • (ПСТР(A2,РЯДОК(ДВССИЛ(«1:»&ДЛСТР(A2)))),1)*1) — у цій частині функції «відкидаються» всі текстові значення, вони всі «падають» у помилку #!ЗНАЧЕННЯ. А чисельні значення лишаються.
  • ЕСЛИПОМИЛКА((ПСТР(A2,РЯДОК(ДВССИЛ(«1:»&ДЛСТР(A2))),1)*1),»») — у цій частині функції всі помилки, отримані раніше видаляються і залишаються лише чисельні значення.
  • =ОБ'ЄДНАТИ(«»;ІСТИНА;ЕСЛИПОМИЛКА((ПСТР(A2;РЯДОК(ДВССИЛ(«1:»&ДЛСТР(A2)));1)*1);»))) — і, нарешті, всі значення отримані раніше просто об'єднуються функцією ОБ'ЄДНАТИ.

Важлива інформація: якщо щось не виходить, ви можете перевірити виведення окремих частин функції за допомогою гарячої клавіші F2.

За аналогією ми можемо отримати тільки текстове значення рядка (видаляючи всі числа). Формула функції буде такою:

=ОБ'ЄДНАТИ(“”;ІСТИНА;ЯКЛИ(ЕПОМИЛКА(ПСТР(A2;РЯДОК(ДВССИЛ(“1:”&ДЛСТР(A2)));1)*1);ПСТР(A2;РЯДОК(ДВССИЛ(“1:”&ДЛСТР) (A2)));1);””))))

Різниця між цими формулами в тому, що функція ЯКЩО перевіряє помилка чи отримане від ПСТР значення чи ні.

Також, як і в попередньому варіанті, функція ОБ'ЄДНАТИ використовується для поєднання значень.

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

Як витягти число з текстового рядка в Excel раніше версій

Отже, у попередніх версіях Excel функції ОБ'ЄДНАТИ ще немає. Тому доведеться використати дещо інше.

Допустимо, у нас те саме завдання:

Як витягти число з текстового рядка в окремий осередок Excel

У такому разі формула буде такою:

=ЯКЩО(СУМ(ДЛСТР(A2)-ДЛСТР(ПІДСТАВИТИ(A2; {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,” 8″,”9″}, “”)))>0; СУММПРОИЗВ(ПСТР(0&A2; НАЙБІЛЬШИЙ(ІНДЕКС(ЄЧИСЛО(–ПСТР(A2;СТРОКА(ДВССИЛ)(“$1:$”&ДЛСТР(A2)))); 1))* РЯДОК(ДВССИЛ(“$1:$”&ДЛСТР(A2)));0); (“$1:$”&ДЛСТР(A2)))/10);””)

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

Функція досить складна і «важка» — тому, під час роботи з великими обсягами даних, виконання може зайняти якийсь час.

Як розділити рядок на текст та число за допомогою Visual Basic

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

Зрештою, за допомогою Visual Basic, ми отримаємо одну маленьку функцію, яка буде робити все, що вам потрібно і не доведеться писати величезні формули.

Отже, зараз я продемонструю вам, як створити дві різні функції. Одна витягуватиме з рядка лише числа, а друга лише текст.

Вилучаємо лише число з текстового рядка, використовуючи Visual Basic

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

Код Visual Basic:

Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) GetNumeric = Result End Function

Покрокова інструкція:

  • Клацніть на “Розробник”;

Як витягти число з текстового рядка в окремий осередок Excel

  • Далі – “Visual Basic” (або гарячі клавіші ALT + F11);

Як витягти число з текстового рядка в окремий осередок Excel

  • У вікні Visual Basic правою кнопкою мишки на ваш аркуш;

Як витягти число з текстового рядка в окремий осередок Excel

  • Далі “Insert” -> “Module”;

Як витягти число з текстового рядка в окремий осередок Excel

  • У вікно, що відкрилося, помістіть наш код;

Як витягти число з текстового рядка в окремий осередок Excel

  • Закрийте Visual Basic;

Отже, ми створили функцію для отримання всіх чисел з текстового рядка та назвали її GetNumeric. Тепер спробуємо використовувати її.

Як витягти число з текстового рядка в окремий осередок Excel

У результаті виконання ми отримали число з рядка тексту.

Не забувайте, що наразі нам необхідно зберегти файл Excel з відповідним розширенням, тому що в ньому використовується Visual Basic макрос.

Також можна зберегти цю функцію до книги макросів Excel. Таким чином, її використання буде ще швидше та зручніше.

Вилучаємо тільки текст із рядка використовуючи Visual Basic

А тепер давайте створимо функцію, яка отримуватиме лише текст із рядка (без чисел).

Код Visual Basic:

Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1 ) Next i GetText = Result End Function

Покрокова інструкція:

  • Клацніть на “Розробник”;

Як витягти число з текстового рядка в окремий осередок Excel

  • Відкрийте “Visual Basic”;

Як витягти число з текстового рядка в окремий осередок Excel

  • У вікні Visual Basic натисніть правою кнопкою мишки на ваш аркуш;

Як витягти число з текстового рядка в окремий осередок Excel

  • Далі “Insert” -> “Module”;

Як витягти число з текстового рядка в окремий осередок Excel

  • І помістіть наш код у вікно, що відкрилося;

Як витягти число з текстового рядка в окремий осередок Excel

  • Закрийте Visual Basic.

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

Давайте перевіримо як вона працює:

Як витягти число з текстового рядка в окремий осередок Excel

Не забувайте, що наразі нам необхідно зберегти файл Excel з відповідним розширенням, тому що в ньому використовується Visual Basic макрос.

Також можна зберегти цю функцію до книги макросів Excel. Таким чином, її використання буде ще швидше та зручніше.

nBook
Додати коментар