⚡ Разделение текста в Excel: как быстро разобрать данные по символам, пробелам и шаблонам. Пошаговое руководство по разделению текста в Excel: функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР, готовые формулы и макросы VBA для автоматизации.
Разделение текста в Excel — ЛЕВСИМВ, ПРАВСИМВ, ПСТР, ДЛСТР и VBA примеры
Склеивание текста в Excel — СЦЕПИТЬ, амперсанд, мгновенное заполнение и VBA
💬 Введение
Когда вы работаете с выгрузками из 1С, SAP или CRM-систем, часто сталкиваетесь с ситуацией, когда в одной ячейке содержится сразу несколько данных:
офис продаж, фамилия сотрудника, артикул товара.
Всё это выглядит примерно так:
Анализировать такую таблицу невозможно, пока вы не разделите текст на отдельные столбцы.
Сегодня вы научитесь:
- делить ячейку на части с помощью формул;
- определять длину и позицию текста;
- вытягивать нужные фрагменты слева, справа и из середины строки;
- автоматизировать всё это с помощью VBA.
🔥 В середине статьи вас ждёт готовый макрос, который делает всю работу за вас.
📘 Почему нельзя ограничиться «Текст по столбцам»
Да, в Excel есть встроенная функция “Текст по столбцам”, но у неё три проблемы:
- Работает только вручную — нельзя вставить в формулу;
- Не подходит, если данные меняются динамически;
- Не позволяет гибко извлекать нужные символы.
Поэтому мы используем формулы — они автоматизируют процесс и адаптируются под изменения в данных.
🧩 Функция ЛЕВСИМВ (LEFT) — извлекаем данные слева
Формула:
=ЛЕВСИМВ(текст; количество_знаков)
Пример:
=ЛЕВСИМВ(A2;3)
👉 Вернёт первые 3 символа из ячейки A2.
В нашем примере:
Это быстрое решение, если офис всегда записан тремя буквами.
Если длина офиса разная, можно сделать умнее — найти символ подчёркивания с помощью НАЙТИ:
=ЛЕВСИМВ(A2;НАЙТИ("_";A2)-1)
Теперь Excel сам вычислит, где заканчивается название офиса.
🔧 Функция ПРАВСИМВ (RIGHT) — достаём данные справа
Формула:
=ПРАВСИМВ(текст; количество_знаков)
Если в конце строки у нас артикул из 4 цифр, формула будет:
=ПРАВСИМВ(A2;4)
Результат: 4582
Но если длина артикулов разная, используем комбинацию:
=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("";ПОДСТАВИТЬ(A2;"";"*";2)))
Здесь Excel ищет второе подчёркивание и отрезает всё, что справа от него.
🧠 Функция ПСТР (MID) — извлекаем текст из середины
Формула:
=ПСТР(текст; нач_позиция; количество_знаков)
Чтобы получить фамилию между двумя подчёркиваниями, используем:
=ПСТР(A2;НАЙТИ("";A2)+1;НАЙТИ("";A2;НАЙТИ("";A2)+1)-НАЙТИ("";A2)-1)
📊 Результат:
Теперь таблица стала пригодной для анализа.
🧮 Функция ДЛСТР (LEN) — считаем количество символов
Формула:
=ДЛСТР(A2)
Полезна, если вы хотите понять длину строки перед извлечением фрагментов.
Можно использовать её внутри других формул, чтобы динамически подбирать нужное количество знаков.
🪄 Комбинируем функции
Иногда нужно достать не фиксированный кусок текста, а часть до или после символа, который повторяется.
Тогда применяем комбинации:
⚡ Пример: разделяем ФИО, офис и артикул
Формулы:
- B2: =ЛЕВСИМВ(A2;НАЙТИ("_";A2)-1)
- C2: =ПСТР(A2;НАЙТИ("_";A2)+1;НАЙТИ("_";A2;НАЙТИ("_";A2)+1)-НАЙТИ("_";A2)-1)
- D2: =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("_";ПОДСТАВИТЬ(A2;"_";"*";2)))
Протяните вниз — и каждая запись разбивается на три отдельные части.
🧰 VBA-автоматизация: как разделить ячейки одной кнопкой
Если таких таблиц десятки, удобнее автоматизировать процесс через макрос.
Вот пример кода:
Sub РазделитьЯчейки()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Данные")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long
For i = 2 To lastRow
Dim parts() As String
parts = Split(ws.Cells(i, "A").Value, "_")
ws.Cells(i, "B").Value = parts(0)
ws.Cells(i, "C").Value = parts(1)
ws.Cells(i, "D").Value = parts(2)
Next i
MsgBox "Разделение выполнено!", vbInformation
End Sub
📌 Этот код автоматически проходит по всем строкам, разделяет текст по символу _ и заполняет соседние столбцы.
💬 Подписка и комментарии
Если вы часто работаете с выгрузками из 1С или CRM — сохраните этот пост и поделитесь им с коллегами.
🔥 Подписывайтесь на наш Телеграм и Дзен — там я выкладываю шаблоны, формулы и готовые макросы для Excel.
💬 Напишите в комментариях: в каких задачах вам чаще всего приходится разбивать текст на части?
📘 Вывод
Теперь вы знаете, как в Excel:
- извлекать части текста с помощью ЛЕВСИМВ, ПРАВСИМВ, ПСТР;
- автоматически находить нужные позиции через НАЙТИ и ДЛСТР;
- полностью автоматизировать разделение данных с помощью VBA.
Вы избавились от ручного копирования и получили гибкий инструмент для аналитики и отчётности.