7 удивительных вещей, которые могут сделать для вас текст в столбцы Excel

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

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

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

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

Где найти текст в столбцы в Excel

Чтобы получить доступ к тексту в столбцы, выберите набор данных и перейдите в Данные → Инструменты для работы с данными → Текст в столбцы.

Это откроет мастер преобразования текста в столбцы.

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

Чтобы получить доступ к тексту в столбцы, вы также можете использовать сочетание клавиш - ALT + A + E.

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

Пример 1. Разделение имен на имя и фамилию

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

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

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 убедитесь, что выбран параметр «С разделителями» (который также используется по умолчанию). Щелкните Далее.
• На шаге 2 выберите «Пробел» в качестве разделителя. Если вы подозреваете, что между именами могут быть двойные / тройные последовательные пробелы, также выберите вариант «Обрабатывать последовательные разделители как один». Щелкните Далее.
• На шаге 3 выберите целевую ячейку. Если вы не выберете целевую ячейку, существующий набор данных будет заменен именем в первом столбце и фамилией в соседнем столбце. Если вы хотите сохранить исходные данные без изменений, либо создайте копию, либо выберите другую целевую ячейку.
• Щелкните Готово.

Это мгновенно даст вам результаты с именем в одном столбце и фамилией в другом столбце.

Примечание:

• Этот метод хорошо работает, когда имя состоит только из имени и фамилии. Если есть инициалы или отчество, это может не сработать. Щелкните здесь, чтобы получить подробное руководство о том, как решать дела с различными комбинациями имен.
• Результат, который вы получаете от использования функции «Текст в столбцы», является статическим. Это означает, что если есть какие-либо изменения в исходных данных, вам придется повторить процесс, чтобы получить обновленные результаты.

Также читайте: Как сортировать по фамилии в Excel

Пример 2 - Разделение идентификаторов электронной почты на имя пользователя и имя домена

Текст в столбцы позволяет выбрать собственный разделитель для разделения текста.

Это можно использовать для разделения адресов электронной почты на имена пользователей и доменные имена, поскольку они разделены знаком @.

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

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

Вот шаги, чтобы разделить эти имена пользователей и доменные имена с помощью функции Text to Columns.

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 убедитесь, что выбран параметр «С разделителями» (который также используется по умолчанию). Щелкните Далее.
• На шаге 2 выберите «Другое» и введите @ в поле справа от него. Не забудьте отменить выбор любого другого параметра (если он отмечен). Щелкните Далее.
• Измените ячейку назначения на ту, где вы хотите получить результат.
• Щелкните Готово.

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

Пример 3 - Получение корневого домена по URL-адресу

Если вы работаете с URL-адресами в Интернете, вам иногда может потребоваться знать общее количество уникальных корневых доменов.

Например, в случае http://www.google.com/example1 и http://google.com/example2 корневой домен будет одинаковым, то есть www.google.com.

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

Вот шаги, чтобы получить корневой домен из этих URL:

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 убедитесь, что выбран параметр «С разделителями» (который также используется по умолчанию). Щелкните Далее.
• На шаге 2 выберите «Другое» и введите / (косая черта) в поле справа от него. Не забудьте отменить выбор любого другого параметра (если он отмечен). Щелкните Далее.
• Измените ячейку назначения на ту, где вы хотите получить результат.
• Щелкните Готово.

Это разделит URL-адрес и даст вам корневой домен (в третьем столбце, поскольку перед ним стояли две косые черты).

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

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

Пример 4 - Преобразование недопустимых форматов даты в допустимые форматы даты

Если вы получаете данные из таких баз данных, как SAP / Oracle / Capital IQ, или импортируете их из текстового файла, существует вероятность, что формат даты неверен (т. Е. Формат, который Excel не считает датой).

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

Предположим, у вас есть даты в формате ниже (которые не соответствуют допустимому формату даты Excel).

Вот шаги, чтобы преобразовать их в допустимые форматы даты:

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 убедитесь, что выбран параметр «С разделителями» (который также используется по умолчанию). Щелкните Далее.
• На шаге 2 убедитесь, что выбрана опция НЕТ разделителя. Щелкните Далее.
• В формате данных столбца выберите Дата и выберите нужный формат (DMY будет означать дату, месяц и год). Кроме того, измените ячейку назначения на ту, где вы хотите получить результат.
• Щелкните Готово.

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

Пример 5 - Преобразование текста в числа

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

Это может произойти несколькими способами:

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

Проблема заключается в том, что эти числа (в текстовом формате) игнорируются функциями Excel, такими как СУММ и СРЕДНЕЕ.

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

Вот шаги, чтобы использовать текст в столбцы для преобразования текста в числа

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 убедитесь, что выбран параметр «С разделителями» (который также используется по умолчанию). Щелкните Далее.
• На шаге 2 убедитесь, что выбрана опция НЕТ разделителя. Щелкните Далее.
• В формате данных столбца выберите Общие. Кроме того, измените ячейку назначения на ту, где вы хотите получить результат.
• Щелкните Готово.

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

Пример 6 - Извлечение первых пяти символов строки

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

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

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

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

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

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

Пример 7 - Преобразование чисел со знаком минус в конце в отрицательные числа

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

Текст в столбцы - идеальный способ отсортировать это.

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

Вот шаги, чтобы преобразовать эти конечные минусы в отрицательные числа:

• Выберите набор данных.
• Перейдите в Данные → Инструменты для работы с данными → Текст в столбцы. Откроется мастер преобразования текста в столбцы.
• На шаге 1 нажмите «Далее».
• На шаге 2 нажмите «Далее».
• На шаге 3 нажмите кнопку «Дополнительно».
• В диалоговом окне «Дополнительные параметры импорта текста» выберите параметр «Конечный минус для отрицательного числа». Щелкните ОК.
• Задайте ячейку назначения.
• Щелкните Готово.

Это мгновенно поместит знак минус с конца числа в его начало. Теперь вы можете легко использовать эти числа в формулах и расчетах.