Как разделить текст на отдельные столбцы по заглавным буквам в Excel?
При работе с данными в Excel иногда можно столкнуться с ячейками, содержащими несколько слов, объединенных вместе, каждое из которых начинается с заглавной буквы, например, "ИмяФамилия" или "ПродажиМартАпрель". Для очистки данных или анализа может потребоваться разделить содержимое на отдельные столбцы по каждой заглавной букве, как показано на скриншоте ниже. Однако Excel не предлагает прямой встроенной функции для разделения текста по заглавным буквам. Какие практические подходы помогут быстро и точно разделить слова на отдельные столбцы на основе заглавных букв?
Ниже вы найдете три эффективных решения данной проблемы, каждое из которых имеет свои сценарии применения и преимущества:
Разделение текста на отдельные столбцы по заглавным буквам с помощью формул
Если ваши данные состоят из значений ячеек, содержащих только два слова, соединенных заглавными буквами, формулы Excel могут помочь разделить содержимое на отдельные столбцы. Это решение простое, не требует кода или дополнительных надстроек и подходит для небольших наборов данных или менее сложных задач разделения.
1. В пустой ячейке рядом с вашими данными (например, C2) введите следующую формулу массива, чтобы извлечь первое слово из ячейки A2 (где 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
Указанный выше метод, основанный на формулах, хорошо подходит для случаев, когда нужно разделить только два слова, но не может обработать ячейки, содержащие более двух объединенных слов (например, 'ИмяОтчествоФамилия'). В таких случаях можно использовать код 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. В диалоговом окне Добавить текст введите пробел (или другой разделитель, если необходимо) в поле ввода Текст. Затем, в разделе Только добавить к, выберите 1-я буква заглавная. Это указывает Kutools добавлять выбранный разделитель перед каждой заглавной буквой в выбранных ячейках.
4. Нажмите OK. Kutools автоматически добавит пробелы перед каждой заглавной буквой, сразу готовя ваши данные к разделению.
5. Далее, при выборе обработанного диапазона, перейдите к Kutools > Объединить и разделить > Разделить ячейки, чтобы начать разделение текста на столбцы по разделителям.
6. В диалоговом окне Разделить ячейки выберите Разделить на столбцы в разделе Тип, и отметьте чекбокс Пробел (или любой другой разделитель, который был добавлен ранее), чтобы указать, где должны происходить разделения.
7. Нажмите OK. Появится запрос, позволяющий выбрать начальную ячейку для результатов разделения. Укажите место назначения внимательно, обеспечивая достаточно места для разделенных столбцов.
8. Наконец, нажмите OK для завершения операции разделения. Теперь ваши данные будут распределены по отдельным столбцам в местах каждой заглавной буквы эффективно и точно.
Этот метод идеален для сложных, больших или часто обновляемых данных и помогает избежать ручной корректировки формул или программирования. Помните, что 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек