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

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

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

К примеру, разделить столбец с полными именами (Фамилия Имя Отчество) на отдельные столбцы с именами (Фамилия, Имя, Отчество).
Давайте разберемся как разбить текст в ячейке по столбцам на определенном примере.

Представим у нас имеется таблица с ФИО служащих компании:

Исходные данные для разделения

Начальные данные для разделения

Для того, чтоб разделить текст в Excel по столбцам идем на панель вкладок и избираем Данные -> Работа с данными -> Текст по столбцам:

Указание формата данных

На первом шаге избираем формат данных:

Шаг 1. Указание формата данных

На выбор есть 2 варианта формата данных:

С разделителями — разделение происходит по знаку-разделителю (пробел, запятая, точка и т.д.);
Фиксированная ширина — разделение происходит по фиксированной ширине столбца.

Выбор разделителя для данных

В случае выбора формата с разделителями, как в нашем примере, на втором шаге избираем какой непосредственно знак-разделитель мы будем применять:

Шаг 2. Выбор разделителя для данных

Можно избрать как из обычных символов (символ табуляции, точка с запятой, запятая, пробел), так и из всех остальных (к примеру, точка, знаки $, @ и т.п.).
Флаг Считать поочередные разделители одним нужен если в начальных данных разделитель быть может продублирован (к примеру, двойной пробел и т.д.).
Параметр Ограничитель строк дозволяет не делить по столбцам текст заключенный в кавычки.

Выбор формата данных для всякого столбца

На 3-ем шаге избираем формат данных для всякого новейшего сформированного столбца:

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

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

При всем этом ни функций, ни процедур, которые делали бы загаданое, в Excel по дефлоту нет. Можно ли? Ответ — да, а дальше — пошаговое управление.

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

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

Примечание: речь в ней о разделении текста и цифр снутри ячейки. Если вы желаете выделить числа в отдельную ячейку — читайте эту статью: извлечь числа из ячейки в Excel.

Отделить числа от текста — формулы Excel

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

буковкы перед цифрами (x100, FX5200, MI6)
цифра/числа перед буковкой/знаками (2021г, 3D, 4K, 4,2Ghz)
число-текст-число (3к2, 22стр3, 1ЕКХ23)

Интересно почитать: В excel ячейка с выпадающим списком

Отделить число справа от текста

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

Дальше уже дело техники — получить фрагменты до данной нам позиции и начиная с нее, в этом посодействуют функции ЛЕВСИМВ и ПСТР. Допустим, если позиция будет вычислена в ячейке A2, а начальный текст в ячейке A1, то формулы для первой и 2-ой части будут, соответственно:

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

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

Отделить числа, стоящие перед текстом

Если же заблаговременно понятно, что текст ячейки начинается с цифр и длится текстовыми знаками, необходимо получить позицию первого нечислового знака. Это можно создать при помощи аналогичной формулы массива, единственная разница только в том, что функция ПОИСКПОЗ отыскивает ИСТИНА, а не ЛОЖЬ:

Дальше порядок действий аналогичен первому примеру.

Постоянные выражения

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

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

В Google таблицах

Формула Google таблиц, которая поделит текст и числа, достаточно незамысловата:

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

В Excel с надстройкой !SEMTools

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

Разделить текст и числа через пробел при помощи постоянных выражений в Excel с !SEMTools

Разбить текст и числа с !SEMTools в 2 клика

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

Все, что для вас необходимо создать, это:

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

Желаете так же стремительно делать данную функцию? Попытайтесь тестовую версию надстройки !SEMTools:

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

Если у вас есть перечень столбцов с данными, и вы желаете разделить их на несколько столбцов при помощи определенного разделителя, как на приведенных ниже скриншотах, как вы сможете разделить их в Excel? Может быть, некие из юзеров задумываются лишь о функции Text to Column, но сейчас я представлю для вас не только лишь функцию Text to Columns, да и код VBA.

Разделите текстовые строчки на несколько столбцов пробелом / запятой / разделителем с текстом в столбцы
Разделить текстовые строчки на несколько строк / столбцов при помощи пробела / запятой / разделителя

Разделение текстовых строк на несколько столбцов при помощи пробела / запятой / разделителя при помощи функции текста в столбцы

Функция преобразования текста в столбцы весьма полезна для разделения перечня на несколько столбцов в Excel. Этот способ гласит о том, как разделить данные по обозначенному разделителю при помощи функции Text to Column в Excel. Пожалуйста, сделайте последующее:

Интересно почитать: В эксель перенос текста в ячейке

1. Изберите перечень столбцов, который вы желаете разделить по разделителю, и нажмите Данные > Текст в столбцы. Смотрите снимок экрана:

2. Потом Мастер преобразования текста в столбцы возникает диалоговое окно и проверьте разграниченный и нажмите Next клавиша. Смотрите снимок экрана:

3. В открывшемся диалоговом окне «Мастер преобразования в текст в столбцы — шаг 2 из 3» проверьте разделитель, по которому нужно разделить данные.

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

4. Нажмите Окончить. Сейчас вы сможете созидать, что перечень избранных столбцов был разбит на несколько столбцов обозначенным разделителем.

Извлекать числа (либо текст) лишь из одной ячейки / столбца в различные столбцы / строчки

Kutools for Excel улучшает Разделить клеточки утилита и поддерживает пакетное извлечение всех текстовых знаков либо чисел из одной ячейки / столбца в два столбца / строчки. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel — Включает наиболее 300 комфортных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Разделите текстовые строчки на несколько строк / столбцов по пробелу / запятой / разделителю при помощи Kutools for Excel

Вышеупомянутый способ может лишь разбивать текстовые строчки на несколько столбцов. Этот способ представит Kutools for Excel’s Разделить клеточки утилита для обычного разделения текстовых строк на несколько строк либо столбцов при помощи обозначенного разделителя в Excel.

1. Изберите спектр, в котором вы желаете разделить текстовые строчки (в нашем случае мы избираем спектр A1: A9), и нажмите Kutools > Текст > Разделить клеточки.

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

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

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

Как в excel 2007 разделить текст по столбцам на две, три и наиболее ячеек. Обычно для примера употребляют разбивку ячеек с ФИО, чтобы данные были раздельно.

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

Мне по работе пришлось делить время в ячейках таблицы excel , пример: 0:00-15:00. А меж значениями разделитель точка с запятой 0:00-15:00;16:00-17:00;22:00-24:00. Эти данные в таблице по денькам, в ячейках различное количество значений меж разделителем. Нужно было разделить время по любому деньку, в столбик под датой проставить время, разделить время на начало и конец, посчитать разницу и все сложить совместно.

Интересно почитать: Как в excel защитить от редактирования отдельные ячейки

Шаг 1-ый. Разделить время в ячейках

Выделяем спектр ячеек, на ленте жмем Данные-Текст по столбцам, покажется Мастер текстов.

Ничего не меняем, если у вас переключатель стоит в положении: с разделителем, нажимаем дальше.

Снимаем галку «символ табуляция», ставим галку «иной»

и в окошко вносим точку с запятой [ ; ]. Нажимаем или дальше, хотя там особо глядеть не что, верней надавить готово. Будет задан вопросец: поменять содержимое ячеек? Ответ да!

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

Две ячейки-две строчки. Выделяем ячейки, копируем,

и под первой ячейкой нажимаем правой клавишей «Особая вставка»- «Транспонировать».

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

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

Шаг 2-ой. Как в экселе разделить ячейку на время начала и конца

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

Вынесем столбец за нашу таблицу, скопировав и вставив ячейки рядом. Работаем с мастером: Данные-Текст по столбцам , шаг 1-ый ничего не меняем, на втором шаге в окошке иной [ — ] ставим минус, жмем готово.

Шаг 3-ий

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

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

протянем формулу вниз до конца. Мы получили время в часах. В примыкающей ячейке напишем: =К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, там Вы отыщите много увлекательного видео, которое выходит довольно нередко. Приятного просмотра!