Marty는 A 열에 일련의 긴 부품 번호가있는 워크 시트를 가지고 있습니다.

이들은 A123BC, AB123C 등과 같은 문자와 숫자로 구성됩니다. Marty는 데이터를 세 개의 열로 나누려고합니다. 따라서 숫자 앞의 텍스트는 한 열에, 두 번째 열에는 숫자가, 그리고 숫자 뒤의 텍스트는 제삼.

부품 번호를 세그먼트로 나누는 것을 복잡하게 만드는 요인은 결합 된 부품 번호의 각 구성 요소에 대해 설정된 길이가 없다는 것입니다. 구성 요소가 표준 길이 인 경우 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)으로 입력해야합니다. 부품 번호를 검사하고 숫자의 시작점을 결정한 다음 모든 숫자를 추출합니다. 문자열이 숫자로 구성되어 있어도 텍스트 문자열을 반환합니다. 실제로 숫자로 처리되도록하려면 (물론 선행 0을 제거) 다음과 같이 전체 수식을 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)

이 접근 방식은 매우 잘 작동하지만 배열 수식은 특히 워크 시트에 많은 수식이있는 경우 계산 집약적 인 것으로 악명이 높습니다. 천 개의 부품 번호를 분리해야한다면 결국 3,000 개의 배열 수식이 생성되며, 이는 재 계산 속도가 매우 느릴 수 있습니다.

이 상황에 처한 경우 매크로를 사용하여 실제로 부품 번호를 분리 할 수 ​​있습니다. 다음 매크로는 이미 설명한대로 텍스트, 숫자, 텍스트 패턴을 따르는 부품 번호에서 작동해야합니다.

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)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다. 여기에서 Excel의 이전 메뉴 인터페이스에 대한이 팁 버전을 찾을 수 있습니다.

link : / excel-Breaking_Up_Variable-Length_Part_Numbers [가변 길이 부품 번호 분리].