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