Как разделить текст в excel с помощью формулы

Автор: | 28.11.2021

Для начала скажу, что зарабатываю через этого брокера, это проверено временем! А вот и наглядный пример заработка, человек ОЧЕНЬ МНОГО зарабатывает через интернет и все на примерах показывает, иди читайте! Добавить эту страницу в закладки. Теперь прочтите информацию ниже и напишите свой отзыв.

Для этого мы использовали инструмент «Текст по столбцам» в Excel».

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

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

Пример 1. Разделение текста полного имени по столбцам с использованием формул. Если мы рассмотрим это на примере разделения полного имени, то можно будет разделить текст с использованием текстовых формул Excel с помощью функций MID и FIND, которые мы ранее рассматривали в статьях.

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

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

Затем давайте добавим столбцы первой и второй позиции в пробелы. Используя функцию НАЙТИ, как мы уже обсуждали в предыдущей статье, мы найдем положение первых пробелов. Для этого в ячейку «H2» записываем формулу

= НАЙТИ («» -A2-1)

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

Это означает, что вам нужно изменить третий аргумент в функции НАЙТИ, начальную позицию, которая является позицией, с которой функция будет искать желаемый текст.

Мы видим, что второй пробел в любом случае находится после первого пробела, и мы уже нашли позицию первого пробела, поэтому, добавив 1 к позиции первых пробелов, мы сообщим функции НАЙТИ поиск пробела, начиная с первая буква после первого пробела.

Функция будет выглядеть так:

= НАЙТИ («» -A2-H2 + 1)

Далее растягиваем формулу и получаем позиции 1-го и 2-го пробелов.

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

Для этого воспользуемся функцией PSTR, запомните синтаксис этой функции:

= MID (текст-start_num_num_chars), где

  • текст — это полное имя, в нашем примере это ячейка A2;
  • start_position — в нашем случае это 1, то есть начинается с первой буквы;
  • num_chars — видим, что фамилия состоит из всех символов, начиная с первой буквы и до 1-го пробела. И мы уже знаем, где находится первый космос. Это будет количество символов минус 1 символ самого пробела.

Формула будет выглядеть так:

= PSTR (A2-1-H2-1)

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

Опять же, используйте функцию = MID (text-start_position -number_of_chars), где

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

Получаем окончательную формулу:

= PSTR (A2-H2 + 1-I2-H2)

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

И снова function = MID (text-start_position -number_of_chars), где

  • текст — это тот же текст, полное имя, в нашем примере это ячейка А2;
  • start_position — у нас есть второе имя после двух пробелов, что означает, что начальная позиция будет равна позиции второго пробела плюс один символ или I2 + 1;
  • — в нашем случае знаков после отчества нет, поэтому мы можем просто взять любое число, главное, чтобы оно было больше, чем возможное количество знаков в отчестве, я взял число с большим отрывом — 50

Получаем функцию

= PSTR (A2-I2 + 1-50)

Затем выделите все три ячейки и перетащите формулы вниз и получите нужный нам результат. На этом можно закончить или написать промежуточные расчеты положения пробелов в самих формулах разделения текста.

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

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

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

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

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

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

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

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

Как разделить текст в Excel по формуле
Напомним, что вручную (без формул) эту задачу очень легко решить с помощью инструмента «Текст по столбцам», который мы уже рассматривали. В нашем случае это необходимо делать по формулам. Во-первых, вам нужно найти общее деление, по которому мы будем делить текст.

В нашем случае это запятая, но, например, в первом действии мы отделили полное имя, а разделителем был пробел. Наш второй пример более универсален (удобнее с большим объемом данных), поэтому, например, мы могли бы удобно разделить не только полное имя на отдельные ячейки, но и все предложение, каждое слово в отдельную ячейку.

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

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

Шаг 1. Во вспомогательном столбце найдите положение первого разделителя с помощью функции НАЙТИ. Подробно описывать функцию не буду, так как мы уже рассматривали ее ранее. Записываем формулу в D1 и распространяем ее на все строки

= НАЙТИ (B1-A1-1)

То есть мы ищем запятую в тексте, начиная с позиции 1

Шаг 2. Затем в ячейке E1 запишите формулу, чтобы найти второй знак (в нашем случае запятую). Формула та же, но с небольшими изменениями.

= НАЙТИ ($ B1- $ A1-D1 + 1)

  1. Во-первых: исправьте столбец с нужным значением и текстом, чтобы при перетаскивании формулы вправо ссылки на ячейки не перемещались. Для этого нужно написать доллар перед столбцами B и A — вручную или выбрать A1 и B1, трижды нажать клавишу F4, после чего ссылки станут не относительными, а абсолютными.
  2. Во-вторых: третий аргумент — начало позиции, мы будем вычислять как позицию предыдущего разделителя (мы нашли его выше) плюс 1, то есть D1 + 1, поскольку мы знаем, что второй разделитель находится точно после первого разделителя, и мы не надо иметь в виду.

Напишем формулу и перетащим ее.

Шаг 3. Найдите расположение всех остальных разделителей. Для этого мы растянем формулу, чтобы найти второй разделитель (шаг 2) справа для этого количества ячеек, сколько отдельных разбитых значений может быть всего с небольшим запасом. Получаем все позиции разделителей. Ошибка #Value означает, что значения исчерпаны и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделите первое число от текста с помощью функции MID.

= СРЕДНИЙ (A1-1-D1-1)

Наша начальная позиция — 1, мы вычисляем количество символов как позицию первого разделителя минус 1: D1-1 мы удлиняем формулу

Как разделить текст в Excel по формуле

Шаг 5. Точно так же найдите второе слово с помощью функции MID в ячейке P1

= СРЕДНЕЕ ($ A1-D1 + 1-E1-D1-1)

Начальная позиция второго числа начинается после первой десятичной точки. У нас есть позиция первой запятой в ячейке D1, давайте добавим ее и получим начальную позицию нашего второго числа.

Количество символов — это разница между положением третьего разделителя и второго и минус один символ, то есть E1-D1-1 Исправить столбец A исходного текста, чтобы он не перемещался при перетаскивании формулы вправо.

Шаг 6. Растяните полученную на шаге 5 формулу вправо и вниз и получите текст в отдельные ячейки.

Как разделить текст в Excel по формуле

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

Вы также можете сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получаем окончательное решение проблемы

= SEERROR (СРЕДНИЙ ($ A1-D1 + 1-E1-D1-1)- «»)

Как разделить текст в Excel по формуле

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

При написании задачи заметил, что задачу можно упростить. Для этого в столбце C нужно было записать 0 значения первого разделителя. Далее находим значение первого разделителя

= НАЙТИ ($ B1- $ A1-C1 + 1)

и первый текст как

= СРЕДНЕЕ ($ A1-C1 + 1-D1-C1-1)

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

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