Lumpics lumpics.ru

Разделение текста в Microsoft Excel

Способ 1: Использование автоматического инструмента

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

  1. С зажатой левой кнопкой мыши выделите все ячейки, текст которых хотите разделить на столбцы.
  2. Выбор текста для быстрого разделения при помощи встроенного инструмента Excel
  3. После этого перейдите на вкладку «Данные» и нажмите кнопку «Текст по столбцам».
  4. Переход к инструменту быстрого разделения текста в Excel
  5. Появится окно «Мастера разделения текста по столбцам», в котором нужно выбрать формат данных «с разделителями». Разделителем чаще всего выступает пробел, но если это другой знак препинания, понадобится указать его в следующем шаге.
  6. Выбор типа автоматического разделения текста в Excel
  7. Отметьте галочкой символ разделения или вручную впишите его, а затем ознакомьтесь с предварительным результатом разделения в окне ниже.
  8. Выбор типа разделителя при быстром расцеплении текста в Excel
  9. В завершающем шаге можно указать новый формат столбцов и место, куда их необходимо поместить. Как только настройка будет завершена, нажмите «Готово» для применения всех изменения.
  10. Просмотр предварительного результата автоматического расцепления текста в Excel
  11. Вернитесь к таблице и убедитесь в том, что разделение прошло успешно.
  12. Результат автоматического расцепления текста в Excel

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

Способ 2: Создание формулы разделения текста

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

Шаг 1: Разделение первого слова

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

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

  3. Выберите ячейку, где хотите расположить первое слово, и запишите формулу =ЛЕВСИМВ(.
  4. Создание первой формулы для разделения первого слова из текста в Excel
  5. После этого нажмите кнопку «Аргументы функции», перейдя тем самым в графическое окно редактирования формулы.
  6. Переход к редактированию аргументов функции разделения первого слова текста в Excel
  7. В качестве текста аргумента указывайте ячейку с надписью, кликнув по ней левой кнопкой мыши на таблице.
  8. Выбор ячейки с текстом для разделения первого слова в Excel
  9. Количество знаков до пробела или другого разделителя придется посчитать, но вручную мы это делать не будем, а воспользуемся еще одной формулой — ПОИСК().
  10. Создание функции поиска для нахождения пробела в первом слове при его разделении в Excel
  11. Как только вы запишете ее в таком формате, она отобразится в тексте ячейки сверху и будет выделена жирным. Нажмите по ней для быстрого перехода к аргументам этой функции.
  12. Переход к редактированию аргументов функции поиск при разделении первого слова в Excel
  13. В поле «Искомый_текст» просто поставьте пробел или используемый разделитель, поскольку он поможет понять, где заканчивается слово. В «Текст_для_поиска» укажите ту же обрабатываемую ячейку.
  14. Выбор текста для поиска первого пробела при разделении слова в Excel
  15. Нажмите по первой функции, чтобы вернуться к ней, и добавьте в конце второго аргумента -1. Это необходимо для того, чтобы формуле «ПОИСК» учитывать не искомый пробел, а символ до него. Как видно на следующем скриншоте, в результате выводится фамилия без каких-либо пробелов, а это значит, что составление формул выполнено правильно.
  16. Редактирование формулы ЛЕВСИМВ для отображения первого слова при разделении текста в Excel
  17. Закройте редактор функции и убедитесь в том, что слово корректно отображается в новой ячейке.
  18. Возвращение к таблице для проверки отображения первого слова при разделении в Excel
  19. Зажмите ячейку в правом нижнем углу и перетащите вниз на необходимое количество рядов, чтобы растянуть ее. Так подставляются значения других выражений, которые необходимо разделить, а выполнение формулы происходит автоматически.
  20. Растягивание формулы после разделения первого слова в Excel

Полностью созданная формула имеет вид =ЛЕВСИМВ(A1;ПОИСК(" ";A1)-1), вы же можете создать ее по приведенной выше инструкции или вставить эту, если условия и разделитель подходят. Не забывайте заменить обрабатываемую ячейку.

Шаг 2: Разделение второго слова

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

  1. В этом случае основной формулой станет =ПСТР( — запишите ее в таком виде, а затем переходите к окну настройки аргументов.
  2. Создание формулы для разделения второго слова в Excel

  3. Данная формула будет искать нужную строку в тексте, в качестве которого и выбираем ячейку с надписью для разделения.
  4. Выбор ячейки при поиске строки для разделения второго слова в Excel
  5. Начальную позицию строки придется определять при помощи уже знакомой вспомогательной формулы ПОИСК().
  6. Создание функции ПОИСК для поиска начальной позиции при разделении второго слова в Excel
  7. Создав и перейдя к ней, заполните точно так же, как это было показано в предыдущем шаге. В качестве искомого текста используйте разделитель, а ячейку указывайте как текст для поиска.
  8. Настройка функции ПОИСК для поиска начальной позиции при разделении второго слова в Excel
  9. Вернитесь к предыдущей формуле, где добавьте к функции «ПОИСК» +1 в конце, чтобы начинать счет со следующего символа после найденного пробела.
  10. Редактирование функции для учета пробела при настройке формулы разделения второго слова в Excel
  11. Сейчас формула уже может начать поиск строки с первого символа имени, но она пока еще не знает, где его закончить, поэтому в поле «Количество_знаков» снова впишите формулу ПОИСК().
  12. Переход к настройке функции поиска второго пробела при разделении слова в Excel
  13. Перейдите к ее аргументам и заполните их в уже привычном виде.
  14. Настройка функции поиска второго пробела при разделении слова в Excel
  15. Ранее мы не рассматривали начальную позицию этой функции, но теперь там нужно вписать тоже ПОИСК(), поскольку эта формула должна находить не первый пробел, а второй.
  16. Создание вспомогательной функции для поиска второго пробела в Excel
  17. Перейдите к созданной функции и заполните ее таким же образом.
  18. Настройка вспомогательной функции для поиска второго пробела в Excel
  19. Возвращайтесь к первому "ПОИСКУ" и допишите в «Нач_позиция» +1 в конце, ведь для поиска строки нужен не пробел, а следующий символ.
  20. Редактирование первой функции ПОИСК для второго слова при разделении в Excel
  21. Кликните по корню =ПСТР и поставьте курсор в конце строки «Количество_знаков».
  22. Завершающий этап настройки формулы для разделения второго слова в Excel
  23. Допишите там выражение -ПОИСК(" ";A1)-1) для завершения расчетов пробелов.
  24. Добавление последнего выражения для формулы разделения второго слова Excel
  25. Вернитесь к таблице, растяните формулу и удостоверьтесь в том, что слова отображаются правильно.
  26. Результат работы формулы для разделения второго слова в Excel

Формула получилась большая, и не все пользователи понимают, как именно она работает. Дело в том, что для поиска строки пришлось использовать сразу несколько функций, определяющих начальные и конечные позиции пробелов, а затем от них отнимался один символ, чтобы в результате эти самые пробелы не отображались. В итоге формула такая: =ПСТР(A1;ПОИСК(" ";A1)+1;ПОИСК(" ";A1;ПОИСК(" ";A1)+1)-ПОИСК(" ";A1)-1). Используйте ее в качестве примера, заменяя номер ячейки с текстом.

Шаг 3: Разделение третьего слова

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

  1. В пустой ячейке для расположения будущего текста напишите =ПРАВСИМВ( и перейдите к аргументам этой функции.
  2. Переход к настройке формулы для разделения третьего слова в Excel

  3. В качестве текста указывайте ячейку с надписью для разделения.
  4. Выбор ячейки для разделения третьего слова в Excel
  5. В этот раз вспомогательная функция для поиска слова называется ДЛСТР(A1), где A1 — та же самая ячейка с текстом. Эта функция определяет количество знаков в тексте, а нам останется выделить только подходящие.
  6. Создание функции ДЛСТР для поиска количества символов в строке при разделении слова в Excel
  7. Для этого добавьте -ПОИСК() и перейдите к редактированию этой формулы.
  8. Добавление функции ПОИСК для разделения третьего слова в Excel
  9. Введите уже привычную структуру для поиска первого разделителя в строке.
  10. Стандартная настройка функции ПОИСК для разделения третьего слова
  11. Добавьте для начальной позиции еще один ПОИСК().
  12. Добавление начальной позиции для функции ПОИСК при разделении третьего слова в Excel
  13. Ему укажите ту же самую структуру.
  14. Настройка начальной позиции для функции ПОИСК при разделении третьего слова в Excel
  15. Вернитесь к предыдущей формуле «ПОИСК».
  16. Переход к предыдущей функции ПОИСК для завершения настройки разделения третьего слова в Excel
  17. Прибавьте для его начальной позиции +1.
  18. Настройка начальной позиции для завершения разделения третьего слова в Excel
  19. Перейдите к корню формулы ПРАВСИМВ и убедитесь в том, что результат отображается правильно, а уже потом подтверждайте внесение изменений. Полная формула в этом случае выглядит как =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(" ";A1;ПОИСК(" ";A1)+1)).
  20. Проверка разделения третьего слова при работе формулы в Excel
  21. В итоге на следующем скриншоте вы видите, что все три слова разделены правильно и находятся в своих столбцах. Для этого пришлось использовать самые разные формулы и вспомогательные функции, но это позволяет динамически расширять таблицу и не беспокоиться о том, что каждый раз придется разделять текст заново. По необходимости просто расширяйте формулу путем ее перемещения вниз, чтобы следующие ячейки затрагивались автоматически.
  22. Результат разделения всех трех слов в Excel

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Анастасия

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

3 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Алексей
22 декабря 2022 12:48

Хорошая статья, спасибо!

Аноним
15 февраля 2023 14:01

Отличная статья, очень помогла

Аноним
27 декабря 2023 13:48

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

Задать вопрос