Разделение текста из одной ячейки на несколько столбцов с сохранением исходной информации и приведением ее к нормальному состоянию — проблема, с которой когда-нибудь может столкнуться каждый пользователь Excel. Для разделения текста на столбцы используются различные методы, которые определяются исходя из предоставленной информации, необходимости получения конечного результата и степени профессионализма пользователя.
Необходимо разделить ФИО по отдельным столбцам
Для первого примера возьмем таблицу с именами нескольких зарегистрированных в ней людей. Это делается с помощью инструмента «Текст по столбцам». После составления одного из документов обнаружилась ошибка: фамилии, имена и отчества прописаны в одну колонку, что создает некоторые неудобства при последующем заполнении документов. Чтобы получить качественный результат, нужно разбить название на отдельные столбцы. Как это сделать — рассмотрим далее. Описание действий:
- Давайте откроем документ с ошибкой, которую мы допустили ранее.
- Выделите текст, удерживая ЛКМ и растягивая выделение до самой нижней ячейки.
- Найдите «Данные» в верхней ленте — вперед.
- После открытия находим в группе «Работа с данными» «Текст по столбцам». Щелкните ЛКМ и перейдите к следующему диалоговому окну.
- По умолчанию формат исходных данных будет установлен с разделителями. Выйдите и нажмите кнопку «Далее».
- В следующем окне вам нужно определить, что такое разделитель в нашем тексте. У нас есть этот «пробел», что означает, что мы ставим галочку перед этим значением и соглашаемся с действиями, предпринимаемыми, нажимая кнопку «Далее».
От эксперта! Для разделения текста можно использовать запятые, точки, двоеточия, точки с запятой, пробелы и другие знаки.
- Далее вам нужно определить формат данных столбца. По умолчанию — «Общие». Для нашей информации этот формат является наиболее подходящим.
- В таблице выберите ячейку, в которую будет помещен форматированный текст. Мы отступаем на один столбец от исходного текста и записываем соответствующего получателя в адрес ячейки. Когда закончите, нажмите «Готово».
Примечание эксперта! Введенный форматированный текст из-за разного количества символов в полном имени может не поместиться в выбранные ячейки, поэтому итоговую таблицу необходимо отредактировать. Для этого используется увеличение размера ячеек.
6
Разделение текста с помощью формулы
Сложные формулы можно использовать для независимого разделения текста. Они нужны для точного расчета положения слов в ячейке, обнаружения пробелов и разделения каждого слова на отдельные столбцы. Например, мы также будем использовать таблицу с полным названием. Чтобы сделать разделение, вам нужно выполнить три шага.
Этап №1. Переносим фамилии
На разделение первого слова требуется меньше всего времени, потому что нужно переместить только один пробел, чтобы определить правильную позицию. Далее мы рассмотрим пошаговые инструкции, чтобы понять, для чего нужны расчеты в конкретном случае.
- Таблица с введенными полными именами уже создана. Для удобства разделения информации создайте 3 столбца в отдельной области и напишите определение вверху. Отрегулируйте размер ячеек.
- Выберите ячейку, в которой будет записана информация о фамилии сотрудника. Активируйте его, нажав ЛКМ.
Нажмите на кнопку «Аргументы и функции», активация которой откроет окно редактирования формулы.
Здесь в заголовке «Категория» нужно прокрутить вниз и выбрать «Текст».
- Далее находим продолжение формулы ЛЕВОЙ и нажимаем на эту строку. Соглашаемся с действиями, выполняемыми нажатием кнопки «ОК».
- Появится новое окно, в котором нужно указать адресацию настраиваемой ячейки. Для этого нажмите на столбец «Текст» и активируйте нужную ячейку. Адресация вводится автоматически.
- Чтобы указать необходимое количество символов, вы можете вручную подсчитать их и ввести данные в соответствующий столбец или использовать другую формулу: ПОИСК().
- После этого формула отобразится в тексте ячейки. Щелкните по нему, чтобы открыть следующее окно.
- Найдите поле «Искать текст» и щелкните разделитель, указанный в тексте. В нашем случае это недостаток.
- В поле «Текст для поиска» должна быть активирована редактируемая ячейка, в результате чего произойдет автоматический перенос адресации.
- Активируйте первую функцию, чтобы вернуться к ее редактированию. Это действие автоматически укажет количество символов перед пробелом.
Принимаем и нажимаем кнопку «ОК».
В результате вы можете увидеть, что ячейка верна и фамилия была введена правильно. Чтобы изменения во всех строках вступили в силу, перетащите маркер выделения вниз.
Этап №2. Переносим имена
Чтобы отделить второе слово, потребуется немного больше усилий и времени, так как слово разделяется двумя пробелами.
- В качестве основной формулы прописываем аналогично предыдущему способу = PSTR(.
- Выделите ячейку и укажите, где написан основной текст.
- Перейдите в столбец «Исходное положение» и введите формулу ПОИСК().
- Перейдем к нему по предыдущей инструкции.
- Укажите пробел в поле «Искать текст.
- Нажав на «Текст для поиска», активируйте ячейку.
- Вернемся к формуле = PSTR в верхней части экрана.
- В строке «Исходное положение» мы присваиваем формуле +1. Отсчет начнется со следующего символа пробела.
- Приступаем к определению количества символов: вставляем формулу ПОИСК().
- Следуйте этой формуле выше и введите все данные так, как вы уже понимаете.
- Теперь в строке «Исходная позиция» можно написать формулу поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, ничего не уточняя в «Начальной позиции».
- Перейдите к предыдущей формуле ПОИСКА и добавьте +1 к «Исходной позиции.
- Вернемся к формуле = PSTR и в строку «Количество символов» добавим выражение ПОИСК («»; A2) -1.
Этап №3. Ставим Отчество
- После активации ячейки и передачи аргументов функции выберите ПРАВИЛЬНУЮ формулу. Щелкните «ОК».
- В поле «Текст» введите адрес измененной ячейки.
- Там, где необходимо указать количество знаков, пишется DLSTR (A2).
Примечание эксперта! Формула автоматически определит количество символов.
- Чтобы точно определить количество символов в конце, нужно написать: -ПОИСК().
- Перейти к изменению формулы. Введите пробел в поле «Поисковый текст». В «Текст для поиска» — адресация ячеек. В поле «Исходное положение» введите формулу ПОИСК (). Измените формулу с теми же значениями.
- Перейти к предыдущему ПОИСКУ и добавить +1 в строку «Исходная позиция.
- Перейдите к ПРАВИЛЬНОЙ формуле и убедитесь, что все сделано правильно.
Заключение
В статье мне известны два распространенных метода разделения информации в ячейках на столбцы. Следуя простым инструкциям, вы легко овладеете знаниями этих методов и сможете применять их на практике. Сложность разделения столбцов с использованием формул может отпугнуть неопытных пользователей Excel с первого раза, но практическое применение метода поможет вам привыкнуть к нему и без проблем применять его в будущем.