Как разделить числа по столбцам в Excel?
При работе с большими наборами данных в Excel вы можете столкнуться с ситуацией, когда у вас есть список чисел — зачастую довольно длинных, — и каждую цифру этих чисел необходимо разнести по отдельным столбцам. Например, число «9487» из одной ячейки нужно разделить так, чтобы каждая цифра заняла свой собственный столбец. Такая задача часто возникает при анализе данных, особенно когда требуется обработка на уровне отдельных цифр, в программировании или в случаях, когда каждая цифра несёт определённое значение — например, в кодах, метках или статистических показателях.
В этом руководстве вы найдете несколько практичных способов быстро и эффективно разделить числа по отдельным столбцам в Excel.
Разделение чисел по отдельным столбцам с помощью функции «Текст по столбцам»
Разделение чисел по отдельным столбцам с помощью Kutools для Excel 
Разделение чисел по отдельным столбцам с использованием формулы Excel (MID и COLUMN/ROW)
Разделение чисел по отдельным столбцам с использованием макроса VBA
Разделение чисел по отдельным столбцам с помощью функции «Текст по столбцам»
В Excel вы можете воспользоваться функцией «Текст по столбцам», чтобы эффективно разбить каждую цифру числа по отдельным столбцам — особенно если числа имеют одинаковую или предсказуемую длину.
Применимый сценарий: Наилучшим образом подходит для чисел с одинаковым количеством цифр или небольших наборов данных, где можно вручную задать позиции разбиения. Менее удобен для чисел разной длины или очень больших объёмов данных.
1. Выделите ячейки с числами, которые нужно разделить. Затем перейдите на вкладку Данные и нажмите Текст по столбцам.
2. Появится Мастер преобразования текста в столбцы. Чтобы разделить числа по каждой цифре, выберите Фиксированная ширина и нажмите Далее.
3. На следующем шаге добавьте линии разрыва между каждой цифрой. Убедитесь, что разделители стоят именно там, где вы хотите разбить число. Например, если все ваши числа четырёхзначные, вставьте разрыв после первой, второй и третьей цифры.
Совет: если длина чисел различается, этот метод может вызвать смещение.
4. Нажмите Далее, чтобы перейти к последнему шагу. Выберите ячейку назначения для вывода — именно с неё результаты операции «Разделить данные» начнут заполнять столбцы. Убедитесь, что справа от выбранной ячейки достаточно пустых столбцов, чтобы избежать перезаписи существующих данных.
5. Нажмите Готово — теперь каждая цифра будет отображаться в отдельном столбце. Проверьте результаты на наличие объединённых или пропущенных цифр, если разрывы были заданы некорректно.
Советы и устранение неполадок:
- Если вы столкнулись с ошибкой или неожиданным результатом, дважды проверьте разрывы строк и убедитесь, что целевая область пуста — это поможет избежать случайной перезаписи данных.
- Этот метод требует ручного ввода; для данных с числами переменной длины рекомендуем рассмотреть другие подходы, описанные ниже.
Разделение чисел по отдельным столбцам с помощью Kutools для Excel
Kutools для Excel предлагает утилиту Разделить ячейки, которая позволяет мгновенно разбивать цифры чисел по отдельным столбцам — даже в объёмных или сложных наборах данных. Эта функция особенно полезна, когда вам нужны удобство и расширенные возможности, такие как разделение по заданной ширине или обработка существующих данных без ручной расстановки точек разбиения.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выделите ячейки с числами, которые нужно разделить, затем на вкладке Kutools нажмите Объединить и разделить > Разделить ячейки. Откроется диалоговое окно «Разделить ячейки».
2. В диалоговом окне Разделить ячейки установите флажок Разделить на столбцы в разделе Тип. Затем в разделе Разделить по выберите Указать ширину и введите 1 в текстовое поле. Это обеспечит разделение каждой цифры по отдельному столбцу.
3. Нажмите OK. Появится диалоговое окно с запросом выбора ячейки назначения для вывода. Выберите ячейку, справа от которой достаточно пустых столбцов, затем нажмите OK. Цифры чисел будут разделены по отдельным столбцам, как показано ниже.
С помощью утилиты Разделить ячейки из Kutools для Excel вы можете разделять содержимое ячеек по различным шаблонам, включая разделение одной ячейки на несколько строк — когда это необходимо.
Преимущества: Быстрая пакетная обработка, минимальный риск ошибок при ручном разделении, поддержка различных режимов разделения и бесшовная интеграция в существующий рабочий процесс.
Примечание: Если ваши числа содержат десятичные точки или другие нецифровые символы, обратите внимание: они также будут разделены. Всегда дважды проверяйте выделенный диапазон и ячейку вывода, чтобы случайно не перезаписать важные данные.
Разделение чисел по отдельным столбцам с использованием формулы Excel (MID и COLUMN/ROW)
Для сценариев, требующих динамического подхода на основе формул и автоматического обновления при изменении исходных данных, можно использовать формулы Excel для извлечения каждой цифры числа в отдельные столбцы. Это особенно полезно при выполнении повторяющихся задач, автоматизации процессов или когда длина чисел в разных строках варьируется.
Применимый сценарий: Идеально подходит, когда все числа находятся в одном столбце и их нужно разбить по нескольким столбцам, сохранив при этом динамическую связь формул с исходными данными. Подходит для наборов данных любого размера. Однако требует осторожного обращения с числами, содержащими ведущие нули или нечисловые символы.
1. В первой ячейке справа от вашего числа (например, если число находится в A2, введите формулу в B2) введите следующую формулу, чтобы получить первую цифру:
=MID($A2, COLUMN(A1),1)2. Нажмите Enter, чтобы подтвердить формулу, затем перетащите маркер заполнения вправо на столько столбцов, сколько цифр в самом длинном числе вашего набора данных — каждый столбец извлечёт соответствующую цифру.
Пояснение:
- Функция
MIDизвлекает символ из строки на указанной позиции. COLUMN(A1)возвращает 1 в первом выходном столбце (то есть B), 2 — в следующем столбце (C) и так далее, сопоставляя позицию цифры для извлечения.- Использование
$A2фиксирует ссылку в формуле на ячейку со значением в строке 2. Скопируйте формулу вниз, чтобы обработать дополнительные строки.
Советы:
- Если ваши числа отформатированы как числа (а не как текст), ведущие нули отображаться не будут — при необходимости заранее преобразуйте их в текст.
- Формула будет возвращать пустые значения в столбцах, выходящих за пределы фактического количества цифр у более коротких чисел.
Устранение неполадок: Если вы видите неожиданные символы или ошибки, дважды проверьте ссылку на ячейку и убедитесь, что формула скорректирована для правильной строки и столбца. При необходимости используйте VALUE(), чтобы принудительно получить числовой результат.
Разделение чисел по отдельным столбцам с использованием макроса VBA
Если у вас большой объём данных или вы стремитесь к большей автоматизации и гибкости — например, для обработки ведущих нулей, чисел переменной длины или сложных сценариев разбора, — макрос VBA станет эффективным решением. Приведённый ниже макрос разделяет каждую цифру числа по соседним столбцам для каждой строки в выбранном вами диапазоне. Это особенно полезно, если задачу нужно выполнять многократно для множества строк или требуется настроить поведение за пределами стандартных возможностей Excel.
Применимый сценарий: Идеально подходит для обработки объёмных списков и автоматизации рутинных задач. Обеспечивает расширенный контроль при настройке поведения (например, обработка нестандартных символов или углублённая предварительная и последующая обработка). Требует включения макросов и базового знакомства со средой VBA в Excel.
1. Чтобы настроить макрос, перейдите в Средства разработчика > Visual Basic, затем в окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль. Скопируйте и вставьте приведённый ниже код в окно модуля:
Sub SplitNumberToColumns()
Dim WorkRng As Range
Dim Rng As Range
Dim i As Integer
Dim CellValue As String
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to split digits:", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
CellValue = CStr(Rng.Value)
For i = 1 To Len(CellValue)
Rng.Offset(0, i).Value = Mid(CellValue, i, 1)
Next i
Next Rng
End Sub2. Нажмите кнопку 
, чтобы запустить макрос. Откроется диалоговое окно, в котором нужно выбрать диапазон чисел для обработки. Цифры из каждой выбранной ячейки будут распределены по соседним столбцам справа от исходного значения — по одной цифре в каждом столбце.
Устранение неполадок и инструкции:
- Если ячейка пуста или содержит нечисловые данные, макрос всё равно обработает её в соответствии с фильтром по длине строки. Вручную проверьте результат, если ваши данные смешанные.
- Чтобы обработать все числа без перезаписи, убедитесь, что справа от выделенного диапазона есть свободные столбцы.
- Если появится предупреждение безопасности макросов, включите все макросы в параметрах центра управления безопасностью Excel перед запуском кода.
- Отмена (Ctrl+Z) недоступна для действий макроса, поэтому в целях лучшей практики заранее создайте резервную копию данных.
Демонстрация: разбивка или разделение числа на отдельные цифры в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек