Как в excel разбить ячейку на две или несколько? как разделить слова по ячейкам?

Как в Excel разделить ячейку

Делим текст вида ФИО по столбцам.

Если изучение загадочных поворотов формул Excel не для вас, вам может понравиться визуальный метод разделения ячеек, показанный ниже.

 Колонка А нашей таблицы содержит фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.

Вы можете сделать это с помощью инструмента «Текст в столбцы». Мы достаточно подробно обсуждали этот метод, когда рассматривали, как можно разбить ячейку на столбцы.

Коротко напомним:

На ленте «Данные» выберите «Текст по столбцам» — с разделителями.

Затем выберите пробел в качестве разделителя.

Обратите внимание на то, как наши данные разделены в окне примера. В следующем окне мы определяем формат данных

По умолчанию это будет «Общий». Нас это вполне устраивает, поэтому оставляем как есть. Выберите верхнюю левую ячейку диапазона, в котором будет размещен наш разделенный текст. Если вам нужно сохранить исходные данные нетронутыми, лучше выбрать, например, B1

В следующем окне мы определяем формат данных. По умолчанию это будет «Общий». Нас это вполне устраивает, поэтому оставляем как есть. Выберите верхнюю левую ячейку диапазона, в котором будет размещен наш разделенный текст. Если вам нужно сохранить исходные данные нетронутыми, лучше выбрать, например, B1.

В результате имеем следующее изображение:

При желании вы можете дать заголовки новым столбцам B, C, D.

Теперь давайте получим тот же результат, используя формулы.

Многим так удобнее. Даже по той причине, что если в таблице появятся новые данные, которые необходимо разбить, то не нужно повторять всю процедуру сначала, достаточно просто скопировать существующие формулы.

Итак, чтобы выбрать фамилию из нашего полного имени, мы будем использовать выражение

Мы используем пробел в качестве разделителя. Функция ПРОСМОТР сообщает нам, где находится первое место. И затем именно это количество букв (минус 1, чтобы не выделять сам пробел) мы «обрезаем» слева от нашего полного имени с помощью LEFT.

Кроме того, это будет немного сложнее.

Нам нужно извлечь второе слово, то есть имя. Чтобы разрезать кусок пополам, используйте функцию MID.

Как вы, наверное, знаете, функция MID в Excel имеет следующий синтаксис:

MID(текст; начальная_позиция; количество_символов)

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:

Начальная позиция — это позиция первого пробела плюс 1:

ПОИСК(» «;A2) + 1

Количество символов для извлечения: разница между положением второго и первого пробелов минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В результате имя, которое у нас сейчас есть, написано на C.

Фамилия осталась. Для этого используем выражение:

В этой формуле функция ДЛСТР возвращает общую длину строки, из которой вы вычитаете позицию второго пробела. Мы получаем количество символов после второго пробела, и функция ПРАВИЛЬНО извлекает их.

Это результат нашей работы по разделению фамилии, имени и отчества в отдельные ячейки.

Видео: Формулы в Эксель

Как разделить имена в Excel с помощью мастера

Мастер преобразования текста в столбец это самый простой способ отделить имена и фамилии в Excel в дополнение к отчествам. И инструмент достаточно гибок, чтобы приспособиться к вашим данным.

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

Разделенные имена, разделенные пробелами

Во-первых, если имена разделены пробелами, просто выполните следующие действия.

1. Выберите столбец или ячейки, содержащие имена, которые вы хотите разделить. Столбец или ячейки будут выделены.
2. Нажмите на Данные вкладка и выберите Текст в столбцы в твоей ленте.
3. Во всплывающем окне выберите разграниченный для типа файла, который лучше всего описывает ваши данные.
4. Нажмите следующий,
5. Под Разделители, снимите флажок табуляция и проверить Космос,
6. Нажмите следующий,
7. Под Формат данных столбца, выбирать Текст, Если вам нужно выбрать другой Место назначения для ваших результатов, введите его в это поле.
8. Нажмите Конец,

Этот метод работает для разделения имени и фамилии в Excel, а также отчества или инициала. Итак, если ваши данные находятся в ячейке под именем Сью Салли Смит, Сью С. Смит или Сью С. Смит, все будут работать правильно.

Разделенные имена, разделенные запятыми

Если имя и фамилия разделены запятыми, вам потребуется лишь небольшая корректировка приведенных выше инструкций. На шаге 5 под Разделители, проверять запятая, Вы можете сохранить Космос проверил, если данные также имеют пробелы.

Разделение имен и удаление данных

Допустим, у вас есть имена, которые включают имя, отчество и фамилию, но вы не хотите оставлять отчество. Начните с тех же шагов, что и выше, пока не дойдете до шага 7, затем выполните следующие действия.

1. Под Формат данных столбца, Выбрать Не импортировать столбец (Пропустить),
2. в Предварительный просмотр выбранных данныхщелкните, чтобы выделить столбец данных, которые вы хотите удалить из результатов.
3. Нажмите Конец,

Как разделить текст в ячейке Excel по столбцам

Здравствуйте. Сегодня расскажу вам, как разделить текст в Эксель в разные ячейки. Это актуально, когда таблица выгружена из СУБД без разбивки, или создавалась «экзотическим способом», все данные записаны в одной строке.

Например, нужно разделить ФИО на фамилию имя и отчество в разных ячейках. Вот исходные данные:

Чтобы добиться желаемого, выполним последовательность:

1. Выделим всю таблицу с данными
2. Нажмем на ленте Данные – Работа с данными – Текст по столбцам
3. В открывшемся Мастере на первом шаге укажем Формат данных – С разделителями
4. На втором шаге – поставим галку Символ-разделитель – пробел
5. На третьем шаге, если это необходимо, назначим тип данных для каждого столбца и ячейки, куда поместить результат разделения. Жмём Готово

Теперь у нас вместо одной колонки стало три. То, чего мы и добивались:

Вы можете в качестве разделителя использовать указанные символы, либо задать свой, которого нет в списке. Главная логика – когда программа встретит в тексте указанный разделитель – в этом месте будет переход в новый столбец.

Теперь, когда вы знаете, как разделить данные в ячейке Excel на несколько ячеек, полученные структурированные данные будет легче сортировать, фильтровать, применять функции работы со строками.

Текст по столбцам в Excel

Этап третий

Ставим формулу: время конца минус время начала,

формат ячеек Все форматы :мм,

протянем формулу вниз до конца. Мы получили время в часах. В соседней ячейке напишем: =К4*60 Enter. Получим время в минутах.

Правда с секундами… Не нашел способа лучше, чем скопировать столбец, вставить его в блокнот, нажать CTRL+H (Найти и заменить), в найти написал :00

а заменить оставил пустым….

Нажал заменить все…Текст без :00 скопировал из блокнота и вставил в таблицу.

С количеством часов поступил проще, скопировал в блокнот и вставил в таблицу. Одна проблема: 24 часа стоят как 24:00:00.

Не проблема, раз мы знаем, как в экселе разделить ячейку, то тут можно и Copy&Paste применить, но с хитростью — в ячейке перед 24:00 поставить апостроф ‘ -одиночную кавычку ’24:00

и вставить всюду. Складывать автосуммой можно только минуты, сумму часов получим, разделив сумму минут на 60, а количество дней разделим полученное число часов на 24….

В моем примере нечто из 31 дня проработало 28 дней,из 744 часов -673 часа и из 44640 минут -40411 минут… Не важно что это, главное что на этом примере вы узнали как как в экселе разделить текст по столбцам в ячейке, как можно не стандартно использовать блокнот и менять формат ячеек, добавляя к ним апостроф

В этом видео подробно показано, как разделить текст по столбцам:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Новые статьи

• Как использовать функцию МИН в excel — 20/05/2017 11:36
• Как использовать функцию МАКС в excel — 20/05/2017 11:33
• Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
• Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29
• Как использовать функцию СЧЕТЕСЛИ в excel — 20/05/2017 11:26
• Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
• Как использовать функцию ПОИСК в эксель — 10/03/2017 21:28
• Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
• Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
• Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
• Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
• Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07
• Как использовать функцию ЕСЛИ в эксель — 24/02/2017 19:37
• Как использовать функцию СУММЕСЛИ в Excel — 22/02/2017 19:08
• Как использовать функцию СУММ в эксель — 20/02/2017 19:54
• Печать документа в Excel и настройка печати — 16/02/2017 19:15
• Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
• Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
• Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
• Как использовать эксель в качестве фотошопа — 04/06/2016 09:01

Рассоединяем текст с помощью формул

Для этого способа нам понадобятся возможности сочетаний функций ПОИСК и ПСТР. При помощи функции ПОИСК мы будем искать все пробелы, которые есть между словами (например, между фамилией, именем и отчеством). Потом функцией ПСТР выделяем необходимое количество символов для дальнейшего разделения.

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

• Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
• Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

Если же значение в ячейке будете делить на две части, то ищете только один пробел (или иной разделитель), а вот чтобы разделить более 4 слов, в формулу добавьте поиск необходимых разделителей.

Пример функция ПСТР для разделения текста на части в Excel

Деление строки

Функция ЗАМЕНИТЬ

Данная функция заменяет часть строки в заданном количестве символов, начиная с указанного по счету символа на новый текст.

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

• старый_текст – строка либо ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа слева направо, с которого нужно производить замену;
• количество_знаков – количество символов, начиная с начальная_позиция включительно, которые необходимо заменить новым текстом;
• новый_текст – строка, которая подменяет часть старого текста, заданного аргументами начальная_позиция и количество_знаков.

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

В случае обработки небольшого количества ячеек такой способ вполне приемлем. Но если обработать необходимо несколько тысяч различных строк, то процесс составления формул станет трудоемким. Поэтому переделаем рассмотренный пример, используя знания по тем функциям, которые уже описаны в начале статьи, а именно:

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

Посмотрите на принтскрин окна функции. Возвращаемые результаты аргументов не поменялись, зато теперь их можно определять динамически, составив формулу один раз.

Как распределить текст с разделителями на множество столбцов.

После изучения представленных выше примеров, думаю, у многих из вас возник вопрос: «А что делать, если у меня не 3 слова, а больше? Если вам нужно разбить текст в ячейке на 5 столбцов?»

Если действовать описанными выше способами, то формулы будут просто мегасложными. Вероятность ошибки при их использовании очень высока. Поэтому воспользуемся другим методом.

У нас есть список предметов одежды с различными характеристиками, перечисленные через дефис. Как видите, таких признаков у нас может быть от 2 до 6. Мы разбиваем текст в наших ячейках на 6 столбцов, чтобы дополнительные столбцы в отдельных строках просто оставались пустыми.

Для первого слова (название одежды) используем:

Как видите, это ничем не отличается от того, что мы рассмотрели выше. Ищем позицию первого дефиса и отделяем нужное количество символов.

Для второго столбца и далее вам нужно более сложное выражение:

Идея здесь в том, что с помощью функции ПОДСТАВИТЬ мы удаляем из исходного содержимого имя, которое мы уже извлекли (например, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-М-39-42-50». В нем снова ищем позицию первого дефиса, как делали это раньше. И с помощью ЛЕВОЙ снова выделяем первое слово (то есть «Синее»).

А потом можно просто «растянуть» формулу в С2 по строке, т.е скопировать в остальные ячейки. В результате в D2 получаем

Обратите внимание, что изменения, которые произошли во время копирования, выделены жирным шрифтом. То есть мы теперь удаляем из исходного текста все, что было найдено и извлечено ранее: содержимое B2 и C2

И снова в полученной фразе берем первое слово, до дефиса.

Если больше брать нечего, то функция ЕСЛИОШИБКА обработает это событие и подставит в качестве результата пустое значение «».

Скопируйте формулы в строки и столбцы по мере необходимости. Результат вы можете увидеть на скриншоте.

Таким образом, вы можете разделить текст ячейки на любое количество столбцов. Главное, чтобы использовались одинаковые разделители.

Как разделить (разбить) ячейки в программе «Excel»

Первый способ: с предварительным объединением ячеек

Именно так можно визуально разделить две ячейки, которые не подлежат делению на несколько частей. Для этого выполните пошаговую инструкцию.

1. Открываем чистый лист электронной книги, переходим во вкладку «Главная» и в блоке «Выравнивание» нажимаем кнопку «Объединить и поместить в центре». Чтобы манипуляция сработала, обязательно нужно выделить объединяемые ячейки.

1. Мы видим, что ячейки соединились, продолжаем форматирование. Теперь построим таблицу. Для этого выделим весь диапазон и поставим границы. Чтобы это сделать, перейдите в блок «Шрифт» на «Главной», затем в инструменте «Границы» кликните на пункте «Все границы».

1. Если присмотреться, видно, как ячейка была разделена на две части, несмотря на то, что никакой соответствующий инструмент не практиковался.

Полезно знать! Помимо основных способов разделения, в зависимости от требований, могут использоваться альтернативные варианты, которые подразумевают разграничение разными графическими приемами.

Приступаем к делению второй части текста — Имя

Снова используем функцию =ПСТР(текст- начальная_позиция- число_знаков), где

• текст — это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
• число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

=ПСТР(A2-H2+1-I2-H2)

Мастер разбора текстов

Разглядим самый обычный метод разделить текст в ячейке, не по сущности, а по выполнению. Для примера, весьма наглядно это можно показать на ФИО в одной ячейке, которые нужно разделить на отдельные столбики для удобства сортировки и фильтрации.

Для выполнения задачки вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

1. Для начала необходимо выделить данные, которые нужно разделить, последующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Избираем 2 вида форматов:

• С разделителями – это когда существует текст либо знак, который условно будет отделять будущее содержимое отдельных ячеек;
• Фиксированной ширины – это когда с помощью пробелов в тексте имитируется столбики схожей ширины.

1. Вторым шагом, в нашем примере, указываем знак, выполняющий роль разделителя. В вариантах, когда в тексте идут попорядку пару разделителей, несколько пробелов, например, то установка флага для пт «Считать поочередные разделители одним» укажет для Excel принимать их за один разделитель. Доп условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не разделять (например, заглавие компании «Рудольф, Петер и Саймон»);
2. Крайним шагом, для уже разделённых столбиков, необходимо указать в диалоговом окне мастера, за ранее выделив их, избрать нужный формат получаемых данных:

• Общий – не проводит конфигурации данных, оставляя их в начальном виде, будет хорошим выбором в большинстве случаев;
• Текстовый – данный формат, в основном, нужен для столбиков с числовыми значениями, которые программка в неотклонимом порядке обязана интерпретировать как текст. (Например, это числа с разделителем по тыще либо номер пластмассовой карточки);
• Дата – этот формат употребляется для столбиков с датами, к слову, формат самой даты можно избрать в выпадающем перечне.

В случае, когда будете употреблять знаки, которые не похожи на обычные, назначенные в региональных настройках, сможете употреблять клавишу «Подробнее» для правильного их определения.

Теперь основная суть приема.

Видео: Формулы в Эксель

=ПСТР(A2-I2+1-50)

Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста.

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией

• Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
• Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
• Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

Теперь промежуточные вычисления позиции пробелом можно смело удалить. Это один из приемов, когда для простоты сначала ищутся промежуточные данные, а потом функцию вкладывают одну в другую. Согласитесь, если писать такую большую формулу сразу, то легко запутаться и ошибиться.

Надеемся, что данный пример наглядно показал вам, как полезны текстовые функции Excel для работы с текстом и как они позволяют делить текст автоматически с помощью формул однотипные данные.

Рассоединяем текст при помощи формул

Для этого метода нам пригодятся способности сочетаний функций ПОИСК и ПСТР. С помощью функции ПОИСК мы будем находить все пробелы, которые есть меж словами (к примеру, меж фамилией, именованием и отчеством). Позже функцией ПСТР выделяем нужное количество знаков для предстоящего разделения.

И если с первыми 2-мя словами понятно, что и как делить, то разделителя для крайнего слова нет, а это означает что необходимо указать в качестве аргумента условно огромное количество знаков, как аргумент «число_знаков» для функции ПСТР, к примеру, 100, 200 либо больше.

А сейчас поэтапно разглядим формирование формулы для разделения текста в ячейке:

• Во-1-х, нам нужно отыскать два пробела, которые делят наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
• Во-2-х, определяем, сколько знаков необходимо выделить в строке. Так как позиции разделителя мы уже обусловили, то знаков для разделения у нас будет на один меньше. Означает, будем употреблять функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» итог работы предшествующей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а сейчас определим крайнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

В итоге мы разделили ФИО на три слова, что дозволит с ними отлично работать.

Если же значение в ячейке будете разделять на две части, то ищете лишь один пробел (либо другой разделитель), а вот чтоб разделить наиболее 4 слов, в формулу добавьте поиск нужных разделителей.

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды в виде Название-Цвет-Размер и вы хотите разделить его на 3 отдельные части. Здесь слово-разделитель — дефис. Мы будем работать с ним.

1. Чтобы извлечь название продукта (все символы до первого дефиса), вставьте следующее выражение в ячейку B2, а затем скопируйте его в столбец:

Здесь функция сначала определяет позицию первого дефиса («-«) в строке, а LEFT извлекает из этой позиции все нужные символы. Вы вычитаете 1 из положения тире, потому что не хотите извлекать сам тире.

1. Чтобы извлечь цвет (то есть все буквы между первым и вторым дефисом), напишите в C2, а затем скопируйте ниже:

Логику работы PSTR мы рассмотрим чуть выше.

1. Чтобы извлечь размер (все символы после третьего дефиса), введите в D2 следующее выражение:

Точно так же в Excel вы можете разделить содержимое ячейки на разные ячейки с помощью любого другого разделителя. Все, что вам нужно сделать, это заменить «-» на нужный символ, например, пробел («»), косую черту («/»), двоеточие («:»), точку с запятой («;») и т д

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству символов в разделителе. В нашем примере это дефис (т.е. 1 символ). Если ваш разделитель состоит из двух символов, таких как запятая и пробел, укажите в своих выражениях только запятую («,») и используйте +2 и -2 вместо +1 и -1.

Как разделить ячейку вида ‘текст + число’.

Начнем с того, что универсального решения, работающего для всех буквенно-цифровых выражений, не существует. Выбор зависит от конкретного шаблона, по которому вы хотите разделить клетку. Ниже вы найдете формулы для двух наиболее распространенных сценариев.

Предположим, у вас есть столбец со смешанным содержимым, в котором число всегда следует за текстом. Естественно, Excel считает такую ​​конструкцию символьной. Вы хотите разделить их, чтобы текст и числа отображались в отдельных ячейках.
Результат может быть достигнут двумя различными способами.

Метод 1. Подсчитайте цифры и извлеките это количество символов

Самый простой способ разделить выражение, в котором после текста стоит число:

Чтобы извлечь числа, найдите в строке все возможные числа от 0 до 9, получите их общее количество и удалите такое же количество символов из конца строки.

Если мы работаем с ячейкой A2:

Чтобы извлечь буквы, вы подсчитываете, сколько их у нас есть. Для этого вычтем количество извлеченных цифр (C2) из ​​общей длины исходной ячейки A2. После этого с помощью LEFT мы вырезаем это количество символов из начала ячейки.

здесь A2 — исходная ячейка, а C2 — извлеченное число, как показано на снимке экрана:

Метод 2: узнать позицию 1- й цифры в строке

Альтернативным решением является использование этой формулы массива для определения положения первой цифры:

Как видите, мы последовательно ищем каждое число в массиве {0,1,2,3,4,5,6,7,8,9}. Чтобы избежать ошибки, если цифра не найдена, мы добавляем эти 10 цифр после содержимого ячейки A2. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге снова получаем массив из 10 цифр — номера позиций, в которых они были найдены. И из них функция MIN выбирает наименьшее число. Это будет позиция, с которой начинается группа цифр, которая должна быть отделена от основного содержания.

Также обратите внимание, что это формула массива и ее ввод необходимо завершить не как обычно, а комбинацией клавиш CTRL+SHIFT+ENTER. Как только первая цифра найдена, вы можете разделить буквы и цифры, используя очень простые формулы LEFT и RIGHT

Как только первая цифра найдена, вы можете разделить буквы и цифры, используя очень простые формулы LEFT и RIGHT.

Чтобы получить текст:

Чтобы получить числа:

Где A2 — исходная строка, а B2 — позиция первого числа.

Чтобы избавиться от вспомогательного столбца, в котором мы вычисляем положение первой цифры, вы можете встроить MIN в функции LEFT и RIGHT:

Чтобы извлечь текст:

Для чисел:

Того же результата можно добиться немного другим способом.

Сначала мы извлекаем числа из ячейки, используя следующее выражение:

То есть мы сравниваем длину нашего текста без цифр с его исходной длиной и получаем количество цифр, которое нужно взять справа. Например, если текст без цифр был укорочен на 2 символа, то он должен «обрезать» на 2 символа вправо, что и будет нашим искомым номером.

А потом берем остальное:

Как видите, результат тот же. Вы можете использовать любой метод.