A 열에 많은 제품 코드가있는 워크 시트가 있다고 가정 해 보겠습니다.

이러한 코드는 A4, B12, AD4 등의 형식입니다. 회사 운영 방식이 변경 되었기 때문에 모든 제품 코드를 변경하여 문자와 숫자 사이에 대시가 포함되도록합니다.

이 작업을 수행 할 수있는 몇 가지 방법이 있습니다. 제품 코드의 구조가 일관된 경우 대시를 삽입하는 것은 간단합니다.

예를 들어, 항상 단일 문자 뒤에 숫자가있는 경우 다음과 같은 공식을 사용할 수 있습니다.

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

데이터가 구조화되지 않을 가능성이 높습니다. 즉, 하나 또는 두 개의 문자 뒤에 최대 3 자리 숫자가 올 수 있습니다. 따라서 A4와 QD284는 모두 유효한 제품 코드가됩니다. 이 경우 솔루션 공식은 좀 더 창의력이 필요합니다.

이를 처리하는 한 가지 방법은 배열 수식을 사용하는 것입니다. 다음 공식을 고려하십시오.

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

값이 A1-A10에있는 경우이 수식을 B1에 넣은 다음 열 아래로 복사 할 수 있습니다. 배열 수식이므로 Ctrl + Shift + Enter를 눌러 입력해야합니다. 수식은 셀에서 첫 번째 숫자의 위치를 ​​찾고 그 앞에 대시를 삽입합니다.

예를 들어 셀 A1에 BR27이 포함되어 있다고 가정합니다. 수식의 가장 안쪽 부분 인 INDIRECT ( “1 : 100”)는 텍스트를 1 : 100 범위로 변환합니다. 행 삽입 또는 삭제가 수식에 영향을주지 않도록 사용됩니다. 수식의 다음 부분 인 ROW (INDIRECT ( “1 : 100”))는 기본적으로 1-100 : 1,2,3, …​, 99,100 값의 배열을 만듭니다. 이것은 셀의 각 문자에 대해 작동하는 데 사용됩니다.

다음 부분 인 MID (A1, ROW (INDIRECT ( “1 : 100”)), 1)은 문자열의 각 개별 문자를 참조합니다. 결과적으로 “B”, “R”, “2”및 “7”배열이 생성됩니다. 배열에 1 (공식의 다음 부분) 곱하기

각 개별 문자가 숫자로 변환됩니다. 문자가 숫자가 아닌 경우이 변환은 오류를 생성합니다. 변환중인 문자열 (BR27)의 경우 결과는 #VALUE, #VALUE, 2 및 7입니다.

다음 단계는 곱셈 결과에 ISERROR 함수를 적용하는 것입니다. 이것은 오류를 TRUE로, 비 오류를 FALSE로 변환하여 TRUE, TRUE, FALSE 및 FALSE를 생성합니다. MATCH 함수는 FALSE의 정확한 일치를 위해 TRUE 및 FALSE 값의 배열을 찾습니다. 이 예에서 MATCH 함수는 첫 번째 FALSE 값이 배열의 세 번째 위치에 있으므로 숫자 3을 반환합니다. 이 시점에서 우리는 본질적으로 셀에서 첫 번째 숫자의 위치를 ​​알고 있습니다.

마지막 함수는 REPLACE로, 세 번째 문자부터 시작하여 소스 문자열에 실제로 대시를 삽입하는 데 사용됩니다.

알 수 있듯이 변환을 수행하는 공식은 해독하기가 다소 어려울 수 있습니다. 이러한 경향이있는 경우 사용자 정의 함수를 만드는 것이 더 쉬울 수 있습니다. 다음 매크로는 적절한 위치에 대시가있는 문자열을 반환하는 매크로의 예입니다.

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

매크로는 원래 문자열의 각 문자를 검사하고 첫 번째 숫자를 찾으면 해당 지점에 대시를 삽입합니다. 다음과 같은 방식으로 함수를 사용합니다.

=DashIn(A1)

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (2613)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다. 여기에서 Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁의 버전을 찾을 수 있습니다.

link : / excelribbon-Inserting_Dashes_between_Letters_and_Numbers [문자와 숫자 사이에 대시 삽입].