Загальна формула
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Резюме
Щоб відокремити текст і цифри, ви можете використовувати формулу, засновану на функції FIND, MIN і LEN з функцією LEFT або RIGHT, залежно від того, чи потрібно витягувати текст або число. У наведеному прикладі формула в С5 має вигляд:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
який повертає 7, положення числа 3 у рядку "яблука30".
Пояснення
Огляд
Формула виглядає складною, але механіка насправді досить проста.
Як і у більшості формул, що розділяють або витягують текст, ключовим є пошук місця того, що ви шукаєте. Отримавши позицію, ви можете використовувати інші функції для вилучення того, що вам потрібно.
У цьому випадку ми припускаємо, що цифри та текст поєднуються, і що число з’являється після тексту. З оригінального тексту, який відображається в одній комірці, потрібно розділити текст і числа на окремі комірки, наприклад:
| Оригінал | Текст | Кількість |
| Яблука30 | Яблука | 30 |
| персики24 | персики | 24 |
| апельсини12 | апельсини | 12 |
| персики0 | персики | 0 |
Як було зазначено вище, ключовим моментом у цьому випадку є пошук початкової позиції числа, що можна зробити за допомогою такої формули:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Отримавши позицію, для вилучення лише тексту використовуйте:
=LEFT(A1,position-1)
І, щоб отримати лише число, використовуйте:
=RIGHT(A1,LEN(A1)-position+1)
У першій формулі вище ми використовуємо функцію FIND, щоб знайти вихідну позицію числа. Для тексту find_text ми використовуємо константу масиву (0,1,2,3,4,5,6,7,8,9), це змушує функцію FIND виконувати окремий пошук кожного значення в константі масиву. Оскільки константа масиву містить 10 чисел, результатом буде масив із 10 значеннями. Наприклад, якщо вихідний текст - "яблука30", отриманий масив буде таким:
(8,10,11,7,13,14,15,16,17,18)
Кожне число в цьому масиві представляє позицію елемента в константі масиву всередині вихідного тексту.
Далі функція MIN повертає найменше значення у списку, яке відповідає позиції першого числа, що з’являється в оригінальному тексті. По суті, функція FIND отримує всі числові позиції, а MIN надає нам першу цифрову позицію: зверніть увагу, що 7 - це найменше значення в масиві, яке відповідає положенню числа 3 в оригінальному тексті.
Можливо, вам цікаво про непарну конструкцію для Within_text у функції пошуку:
B5&"0123456789"
Ця частина формули поєднує всі можливі числа 0-9 з оригінальним текстом у В5. На жаль, FIND не повертає нуль, коли значення не знайдено, тому це лише розумний спосіб уникнути помилок, які можуть виникнути, коли число не знайдено.
У цьому прикладі, оскільки ми припускаємо, що число завжди буде виглядати в оригінальному тексті другим, це працює добре, оскільки MIN змушує повертати лише найменше або перше входження числа. Поки число відображається в оригінальному тексті, ця позиція буде повернута.
Якщо оригінальний текст не містить цифр, буде повернуто "фіктивну" позицію, рівну довжині оригінального тексту + 1. У цій фіктивній позиції формула LEFT вище все одно поверне текст, а формула RIGHT - порожній рядок ("").
