Розділіть текст і числа в комірці в Excel

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

Як розділити текст і цифри в комірці в Excel?

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

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

Загальні формули

Загальна формула для визначення позиції першого числа

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

Загальна формула для отримання тексту

=LEFT(A1,position-1)

Загальна формула для отримання чисел

=RIGHT(A1,LEN(A1)-position+1)

Аргументи наведених формул

Як користуватися цими формулами?

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

По-перше, поверніть початкове положення цифр у текстовому рядку

1. Виберіть порожню комірку для виведення позиції. У цьому випадку я вибираю С3.

2. Введіть формулу нижче і натисніть на Що натомість? Створіть віртуальну версію себе у ключ. Виділіть комірку результату, перетягніть маркер заповнення вниз, щоб застосувати формулу до іншої комірки, поки не будуть показані всі потрібні позиції.

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))

По-друге, отримати текст 

1. Виберіть порожню комірку, щоб знайти текст. Тут я вибираю D3.

2. Введіть формулу нижче і натисніть на Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть ручку заповнення вниз, щоб отримати всі тексти клітинок нижче.

=LEFT(B3,C3-1)

Нарешті, отримайте цифри

1. Виберіть порожню комірку, наприклад E3, щоб знайти цифри.

2. Введіть формулу нижче і натисніть на Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть маркер заповнення вниз, щоб отримати всі номери нижченаведених комірок.

=RIGHT(B3,LEN(B3)-C3+1)

Як працюють ці формули?

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789"))

1. FIND({0,1,2,3,4,5,6,7,8,9},B3&"0123456789")

• B3&"0123456789" повертає результат як: ana varela 240.150123456789.
• FIND({0,1,2,3,4,5,6,7,8,9}, "ana varela 240.150123456789"): Функція Find знаходить позицію цифр "0-9" в ана варелі 240.150123456789 і повертає результат як {14,16,12,21,13,17,24,25,26,27}.
  Результат {14,16,12,21,13,17,24,25,26,27} означає, що число 0 знаходиться на 14 позиції в ana varela 240.150123456789, номер 1 знаходиться в 16 позиції в ana varela 240.150123456789…

2. =MIN({14,16,12,21,13,17,24,25,26,27}): Функція MIN отримує мінімальне значення масиву. Тут результат 12.

=LEFT(B3,C3-1)

LEFT(ana varela 240.15,12-1) що означає, що функція LEFT витягує перші 11 символів з лівого боку текстового рядка "ana varela 240.15". Кінцевий результат - ана варела.

=RIGHT(B3,LEN(B3)-C3+1)

1. LEN(B3)-C3+1:

• Функція LEN обчислює загальну довжину текстового рядка (ana varela 240.150123456789) у B3 і повертає результат як 17.
• 17-12 + 1: Тут отримується загальна довжина чисел у ана-варелі 240.15. Результат - 6.

2. =RIGHT(B3,6): Функція ВПРАВО витягує 6 символів з правої сторони ana varela 240.15. І кінцевий результат - 240.15.

Супутні функції

Пов’язані формули