У Марти есть рабочий лист с длинным рядом номеров деталей в столбце A.

Они состоят из букв и цифр, например A123BC, AB123C и т. Д. Марти хочет разбить данные на три столбца, чтобы текст перед числами находился в одном столбце, числа во втором столбце и текст после чисел в столбце третий.

Фактор, усложняющий разделение номера детали на сегменты, заключается в том, что не существует установленной длины для каждого компонента объединенного номера детали. Если бы компоненты были стандартной длины, вы могли бы использовать функцию «Текст в столбцы» в Excel. Поскольку это не так и между компонентами нет разделителя, то потенциальный путь для решения закрыт.

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

Предполагая, что номера деталей соответствуют указанному шаблону (текст, цифры, текст) и что первый номер детали находится в ячейке A1, вы можете использовать следующее в ячейке B1:

=LEFT(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0)-1)

Это необходимо ввести как формулу массива, а это значит, что вам нужно ввести ее, используя Ctrl + Shift + Enter. Формула находит первую цифру в номере детали, а затем возвращает все, что находится перед этой цифрой.

Он будет работать с любым номером детали, длина которого не превышает 100 символов.

Чтобы извлечь второй компонент номера детали, вы можете поместить в ячейку C1 следующую формулу:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)))

Опять же, это единственная формула, и ее необходимо ввести как формулу массива (Ctrl + Shift + Enter), чтобы она могла работать с каждым символом в исходном номере детали. Он проверяет номер детали и определяет начальную точку цифр, а затем извлекает все эти цифры. Он возвращает текстовую строку, даже если она состоит из цифр. Если вы хотите, чтобы его действительно считали числом (что, конечно, избавило бы от любых начальных нулей), тогда вам нужно заключить всю формулу в функцию Value, как показано здесь:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

Чтобы получить последний компонент номера детали, вам нужно использовать следующую формулу, снова введенную как формулу массива:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

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

Если вы оказались в такой ситуации, вы можете использовать макрос, чтобы фактически разделить номера деталей. Следующий макрос должен работать с номерами деталей, которые соответствуют шаблону текста, цифр, текста, как уже описано.

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

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

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

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

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

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

Они намного короче, чем предыдущие макросы, и все, что они делают, это возвращают границу, где начинаются числа (в случае pNumber), и границу, где начинается вторая группа текста (в случае pAlpha). Чтобы использовать эти функции, вы используете следующие три формулы для возврата, соответственно, первого, второго и третьего компонентов исходного номера детали:

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10369) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Breaking_Up_Variable-Length_Part_Numbers [Разделение номеров частей с переменной длиной].