Как разделить текст на отдельные столбцы по заглавным буквам в Excel?
При работе с данными в Excel вы можете сталкиваться с ячейками, где несколько слов слиты воедино, причём каждое из них начинается с заглавной буквы — например, «FirstNameLastName» или «SalesMarchApril». Для очистки или анализа таких данных часто требуется разбить содержимое на отдельные столбцы по каждой заглавной букве, как показано на снимке экрана ниже. Однако Excel не предлагает встроенной функции для такого разделения. Какие практические способы позволят быстро и точно разбить слова на отдельные столбцы, ориентируясь на заглавные буквы?
Ниже приведены три эффективных решения этой задачи, каждое из которых подходит для определённого сценария и имеет свои преимущества:
Разделение текста на отдельные столбцы по заглавным буквам с помощью формул
Если ваши данные состоят из значений ячеек, содержащих ровно два слова, соединённых заглавными буквами, формулы Excel легко разделят их на отдельные столбцы. Это простое решение не требует написания кода или установки надстроек и идеально подходит для небольших наборов данных или менее сложных задач разделения.
1. В пустой ячейке рядом с вашими данными (например, в ячейке C2) введите следующую формулу массива, чтобы извлечь первое слово из ячейки A2 (где находится текст, который нужно разделить):
=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)После ввода формулы обязательно одновременно нажмите Ctrl + Shift + Enter. Это требуется для формул массива в некоторых версиях Excel. Если всё сделано правильно, вокруг формулы в строке формул появятся фигурные скобки {}. Формула работает так: она находит вторую заглавную букву в ячейке, а затем извлекает все символы до этой позиции, получая первое слово.
2. Чтобы применить формулу ко всем записям, выделите ячейку C2 и перетащите маркер заполнения вниз — формула автоматически скопируется в остальные ячейки столбца, и первые слова из каждой строки будут мгновенно извлечены.
3. Затем в ячейку справа (например, D2) введите следующую формулу, чтобы извлечь оставшуюся часть текста (второе слово):
=REPLACE(A2,1,LEN(C2),"")Эта формула удаляет первое слово, извлечённое на предыдущем шаге, и оставляет только второе. Просто нажмите Enter, чтобы подтвердить.
4. Аналогично выделите ячейку D2 и потяните за маркер заполнения, чтобы скопировать формулу вниз по строкам. Теперь текст из каждой ячейки исходного диапазона будет разделён на два столбца по позиции заглавной буквы.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Разделение текста на отдельные столбцы по заглавным буквам с помощью кода VBA
Описанный выше метод на основе формул отлично подходит для разделения двух слов, но не справляется с ячейками, содержащими более двух соединённых слов (например, «FirstNameMiddleNameLastName»). В таких случаях используйте код VBA, чтобы автоматически вставить пробел перед каждой заглавной буквой и тем самым разделить слова. После добавления пробелов вы сможете применить встроенную функцию Excel Текст по столбцам для разнесения текста по отдельным столбцам.
Сначала примените код VBA, чтобы добавить пробелы перед заглавными буквами и отделить слова.
1. Нажмите ALT + F11, чтобы открыть окно редактора Microsoft Visual Basic for Applications.
2. В редакторе VBA выберите Вставка > Модуль и вставьте следующий код в новое окно модуля.
Код VBA: вставка пробелов перед каждой заглавной буквой в ячейке
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
Dim I As Integer
SplitWords = Left(Str, 1)
For I = 2 To Len(Trim(Str))
If (Asc(Mid(Str, I, 1)) > 64) And _
(Asc(Mid(Str, I, 1)) < 91) And _
(Mid(Str, I - 1, 1) <> " ") Then _
SplitWords = SplitWords & " "
SplitWords = SplitWords & Mid(Str, I, 1)
Next
End Function
После ввода или вставки кода закройте редактор VBA, чтобы вернуться на лист. Этот код создаёт новую функцию под названием splitwords, которую можно использовать прямо в ячейках, как обычную формулу. Обратите внимание: чтобы функция работала, может потребоваться включить макросы и сохранить книгу в формате .xlsm — только так код VBA останется в файле.
3. В пустой ячейке рядом с вашими данными введите следующую формулу, чтобы добавить пробелы перед каждой заглавной буквой в ячейке A2:
=splitwords(A2)Эта формула вызывает пользовательскую функцию для обработки каждой ячейки. После нажатия клавиши Enter вы увидите свой текст с пробелами, вставленными перед каждой заглавной буквой, — так отдельные слова станут чётко различимыми.
4. Скопируйте эту формулу во все нужные ячейки с помощью маркера заполнения, чтобы каждая строка обрабатывалась корректно.
5. На этом этапе ячейки содержат формулы. Возможно, вы захотите скопировать и вставить только значения в другое место, чтобы сохранить обработанный текст без привязки к исходным формулам. Для этого выделите обработанные ячейки, скопируйте их, щёлкните правой кнопкой мыши в нужном месте и выберите «Вставить специально» > «Значения».
Во-вторых, воспользуйтесь функцией «Текст по столбцам», чтобы разделить содержимое ячеек на отдельные столбцы по пробелу.
6. Теперь выделите ячейки со значениями, содержащими пробелы (результаты предыдущего шага). На ленте перейдите в меню Данные > Текст по столбцам. Когда появится Мастер преобразования текста в столбцы (шаг 1), выберите переключатель С разделителями.
7. Нажмите Далее, и на шаге 2 в разделе «Разделители» выберите параметр Пробел. Это укажет Excel использовать пробелы в качестве разделителей для распределения данных по столбцам.
8. На шаге 3 убедитесь, что для формата данных столбца выбрано значение Общий (или при необходимости выберите другой формат), и укажите ячейку, куда следует поместить разделённые данные, щёлкнув 
. Убедитесь, что диапазон назначения не перезаписывает существующие данные.
9. Нажмите Готово, и Excel разделит текст на несколько столбцов — по одному для каждого слова, изначально отделённого заглавной буквой.
Если возникнет ошибка, из-за которой некоторые слова не разделяются корректно, проверьте наличие особых случаев — например, аббревиатур (нескольких заглавных букв подряд) или пробелов в конце. Рассмотрите возможность доработки кода VBA или повторной проверки данных на выбросы. Кроме того, не забывайте регулярно сохранять свою работу при использовании VBA или выполнении массовых операций с данными.
Этот метод особенно полезен при работе с несогласованными длинами текста или разным количеством слов в ячейках. Однако если запуск кода VBA вызывает у вас дискомфорт, рекомендуем рассмотреть альтернативные методы, приведённые ниже.
Разделение текста на отдельные столбцы по заглавным буквам с помощью Kutools для Excel
Kutools для Excel предлагает удобное и эффективное решение для пользователей любого уровня — разделять текст по заглавным буквам. Эта функция особенно полезна при работе с большими наборами данных или выполнении повторяющихся задач. Используя совместно функции Добавить текст и Разделить ячейки, вы легко справитесь с задачей, минимизируя усилия и риски.
1. Выделите ячейки с исходными данными, которые нужно разделить.
2. Щёлкните Kutools > Текст > Добавить текст. Этот инструмент позволяет вставить символ или разделитель — например, пробел — перед каждой заглавной буквой.
3. В диалоговом окне Добавить текст введите пробел (или другой разделитель, если нужно) в поле Текст. Затем в разделе Добавлять только если выберите первая буква заглавная. Это указывает Kutools добавлять выбранный вами разделитель перед каждой заглавной буквой в выделенных ячейках.
4. Нажмите ОК. Kutools автоматически добавит пробелы перед каждой заглавной буквой, мгновенно подготовив ваши данные к разделению.
5. Не снимая выделения с обработанного диапазона, перейдите в меню Kutools > Объединение и разделение > Разделить ячейки, чтобы начать разделение текста на столбцы по разделителям.
6. В диалоговом окне Разделить ячейки выберите Разделить на столбцы в разделе Тип и установите флажок напротив пункта Пробел (или любого другого ранее добавленного разделителя), чтобы указать места для разделения.
7. Нажмите кнопку ОК. Появится запрос, позволяющий выбрать начальную ячейку для размещения результатов разделения. Внимательно укажите место назначения, убедившись, что имеется достаточно свободного места для всех новых столбцов.
8. Наконец, нажмите кнопку ОК, чтобы завершить операцию разделения. Теперь ваши данные будут распределены по отдельным столбцам перед каждой заглавной буквой — быстро и точно.
Этот метод идеально подходит для работы со сложными, объемными или часто обновляемыми данными — он избавляет от необходимости вручную настраивать формулы или писать код. Помните, что Kutools для Excel предлагает множество других функций, значительно повышая вашу эффективность как в повседневных задачах, так и при решении сложных рабочих вопросов.
Скачайте и бесплатно протестируйте Kutools для Excel прямо сейчас!
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек