Разделить текст в ячейке excel по столбцам

Обновлено: 06.07.2024

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

Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части.

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

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

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

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

Содержание

1 Пример 1. Делим текст с ФИО по столбцам с помощью формул
1.1 Приступаем к делению первой части текста — Фамилии
1.2 Приступаем к делению второй части текста — Имя
1.3 Приступаем к делению третьей части текста — Отчество
2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Пример 1. Делим текст с ФИО по столбцам с помощью формул Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях.

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

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

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

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

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

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

Функция будет выглядеть следующим образом:

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

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

Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:

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

Формула будет выглядеть следующим образом:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пропишем формулу и протянем ее вниз.

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

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

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

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

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

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

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

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

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

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

а первого текста как

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

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

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

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

Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.

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

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

Необходимо разделить ФИО по отдельным столбцам

Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:

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

Затем нужно определить формат данных столбца. По умолчанию установлено «Общий». Для нашей информации этот формат наиболее уместен.
В таблице выбираем ячейку, куда будет помещаться отформатированный текст. Отступим от исходного текста один столбец и пропишем соответствующий адресат в адресации ячейки. По окончанию нажимаем «Готово».

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

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

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

Этап №1. Переносим фамилии

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

Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.

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

Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».

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

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

Соглашаемся и кликаем по кнопке «ОК».

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

Этап №2. Переносим имена

Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.

В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.

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

В строке «Искомый текст» указываем пробел.
Кликнув по «Текст для поиска», активируем ячейку.

Возвращаемся к формуле =ПСТР в верхней части экрана.
В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.

Переходим к определению количества знаков – вписываем формулу ПОИСК().
Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.

Этап №3. Ставим Отчество

Примечание эксперта! Формула определит автоматически количество символов.

Заключение

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

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

Первый метод: применение автоматического инструмента

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

Зажимаем левую клавишу мышки и производим выделение всех ячеек, которые мы планируем расцепить на колонки.

Передвигаемся в подраздел «Данные» и жмем ЛКМ по элементу «Текст по столбцам».

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

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

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

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

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

Второй метод: применение формулы расцепления текста

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

Этап первый: расцепление 1-го слова

Подробная инструкция выглядит так:

Этап второй: расцепление 2-го слова

Расцепление 2-го слова – это самый сложный этап, так как оно с 2-х сторон окружено разделителями. Подробная инструкция выглядит так:

Третий этап: расцепление 3-го слова

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

Заключение

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

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

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».

«равно»

(=)

(/)

После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.

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

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.

Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.

Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.

Способ 3: деление столбца на столбец

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

«=»

«/»

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

Способ 4: деление столбца на константу

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

«равно»

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

Способ 5: деление столбца на ячейку

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

«=»

(/)

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

Способ 6: функция ЧАСТНОЕ

Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.

Посмотрим применение данной функции на примере.

«Вставить функцию»

После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:

Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш – «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте также: