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

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

Хороший денек почетаемый читатель!

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

Разобрать слитый текст на нужные составляющие может быть произвести при помощи:

1. Мастера разбора текстов;
2. Формулы;
3. Макросов VBA.

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

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

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

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

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

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

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

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

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

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

И если с первыми 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 слов, в формулу добавьте поиск нужных разделителей.

Выдергиваем слова при помощи макросов VBA

Разглядим два метода разделить текст в ячейке:

1. Выдергиваем отдельные слова по разделителю;
2. Делим текст без пробелов.

Метод №1.

Так как вас интересует автоматическое деление текста, означает нужно написать неплохую функцию на VBA и ввести ее в рабочую книжку. Для начала перебегаем на вкладку «Разраб» и избираем «Visual Basic» либо вызываем эту возможность при помощи жаркого сочетания кнопок Alt+F11. (детальнее в статье «Как сделать макрос в Excel»).

Создаем новейший модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

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

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

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

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

1. Открываем документ с допущенной ранее ошибкой.
2. Выделяем текст, зажав ЛКМ и растянув выделение до последней нижней ячейки.

1. В верхней ленте находим «Данные» – перебегаем.

1. Опосля открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и перебегаем в последующее диалоговое окно.

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

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

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

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

6

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

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

Шаг №1. Переносим фамилии

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

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

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

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

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

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

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

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

Шаг №2. Переносим имена

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

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

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

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

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

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

Шаг №3. Ставим Отчество

1. Активировав ячейку и перейдя в аргументы функции, избираем формулу ПРАВСИМВ. Нажимаем «ОК».

1. В поле «Текст» вписываем адресацию редактируемой ячейки.
2. Там, где нужно указать число символов, пишем ДЛСТР(A2).

Примечание профессионала! Формула обусловит автоматом количество знаков.

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

1. Перейдите к формуле ПРАВСИМВ и удостоверьтесь, что все деяния произведены верно.

Заключение

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

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые нужно разделить на несколько отдельных столбцов. Самые всераспространенные актуальные примеры:

• ФИО в одном столбце (а нужно — в 3-х отдельных, чтоб удобнее было сортировать и фильтровать)
• полное описание продукта в одном столбце (а нужно — отдельный столбец под фирму-изготовителя, отдельный — под модель для построения, к примеру, сводной таблицы)
• весь адресок в одном столбце (а нужно — раздельно индекс, раздельно — город, раздельно — улица и дом)
• и т.д.

Метод 1. Текст по столбцам

Выделите ячейки, которые будем разделять и изберите в меню Данные — Текст по столбцам (Data — Text to columns) . Покажется окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы избрали формат с разделителями (как в нашем примере) — нужно указать какой конкретно знак является разделителем:

Если в тексте есть строчки, где для чего-то попорядку идут несколько разделителей (несколько пробелов, к примеру), то флаг Считать поочередные разделители одним (Treat consecutive delimiters as one) принудит Excel принимать их как один.

Выпадающий перечень Ограничитель строк (Text Qualifier) нужен, чтоб текст заключенный в кавычки (к примеру, заглавие компании «Иванов, Манн и Фарбер») не делился по запятой
снутри наименования.

И, в конце концов, на 3-ем шаге для всякого из получившихся столбцов, выделяя их за ранее в окне Мастера, нужно избрать формат:

• общий — оставит данные как есть — подступает в большинстве случаев
• дата — нужно выбирать для столбцов с датами, при этом формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем перечне
• текстовый — этот формат нужен, по большенному счету, не для столбцов с ФИО, заглавием городка либо компании, а для столбцов с числовыми данными, которые Excel непременно должен воспринять как текст. К примеру, для столбца с номерами банковских счетов клиентов, где в неприятном случае произойдет округление до 15 символов, т.к. Excel будет обрабатывать номер счета как число:

Клавиша Подробнее (Advanced) дозволяет посодействовать Excel верно распознать символы-разделители в тексте, если они различаются от обычных, данных в региональных настройках.

Метод 2. Как выдернуть отдельные слова из текста

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

• в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic(Tools — Macro — Visual Basic Editor)
• в Excel 2007 и новее — вкладка Разраб — Редактор Visual Basic (Developer — Visual Basic Editor) либо сочетание кнопок Alt+F11

Вставляем новейший модуль (меню Insert — Module) и копируем туда текст вот данной для нас пользовательской функции:

Сейчас можно отыскать ее в перечне функций в группы Определенные юзером (User Defined) и употреблять со последующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

• Txt — адресок ячейки с текстом, который делим
• Delimeter — символ-разделитель (пробел, запятая и т.д.)
• n — порядковый номер извлекаемого фрагмента

Метод 3. Разделение слипшегося текста без пробелов

Тяжкий вариант, но тоже бывает. Имеем текст совершенно без пробелов, слипшийся в одну длинноватую фразу (к примеру ФИО «ИвановИванИванович»), который нужно разделить пробелами на отдельные слова. Тут может посодействовать маленькая макрофункция, которая будет автоматом добавлять пробел перед большими знаками. Откройте редактор Visual Basic как в прошлом методе, вставьте туда новейший модуль и скопируйте в него код данной для нас функции:

Сейчас можно употреблять эту функцию на листе и привести слипшийся текст в обычный вид:

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

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

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

Пример 1. Делим текст с ФИО по столбцам при помощи формул

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

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

Попробуем весьма тщательно обрисовать план деяния и разобьем решение задачки на несколько шагов.

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

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

и протянем вниз. Формулу разъяснять не буду — смотрите предшествующую статью

Сейчас нам нужно отыскать порядковый номер второго пробела. Формула будет таковая же, но с маленьким различием. Если прописать такую же формулу, то функция отыщет нам 1-ый пробел, а нам нужен 2-ой пробел. Означает на нужно поменять 3-ий аргумент в функции НАЙТИ — исходная позиция — другими словами позиция с которой функция будет находить разыскиваемый текст. Мы лицезреем, что 2-ой пробел находится в любом случае опосля первого пробела, а позицию первого пробела мы уже отыскали, означает прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ находить пробел начиная с первой буковкы опосля первого пробела. Функция будет смотреться последующим образом:

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

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

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

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

1. текст — это ФИО, в нашем примере это ячейка A2;
2. начальная_позиция — в нашем случае это 1, другими словами начиная с первой буковкы;
3. число_знаков — мы лицезреем, что фамилия состоит из всех символов, начиная с первой буковкы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество символов минус 1 символ самого пробела.

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

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

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

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

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

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

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

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

=ПСТР( 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 для работы с текстом и как они разрешают разделять текст автоматом при помощи формул однотипные данные. Если для вас приглянулась статья, то будем признательны за нажатие на +1 и мне нравится. Подписывайтесь и вступайте в нашу группу вконтакте.

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

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

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

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