Manik에는 A 열에 “mikeDAVIS”형식의 텍스트 값이있는 워크 시트가 있습니다. 여기서 사람의 이름은 소문자이고 성은 대문자입니다. 그는 텍스트의 경우에 따라 이름을 두 개의 개별 열로 나누고 싶습니다.

이것은 수식이나 매크로를 사용하여 수행 할 수 있습니다. 어떤 접근 방식을 사용하든 핵심은 텍스트가 소문자에서 대문자로 전환되는 위치를 파악하는 것입니다. 이는 문자열의 각 문자를 검사해야만 수행 할 수 있습니다. 따라서 공식적인 접근 방식을 사용하려면 배열 수식을 사용해야합니다. 다음 배열 수식은 A1 셀에있는 모든 항목의 성을 반환합니다.

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=65)

* (CODE(MID(A1,ROW($2:$255),1))<90),)+1,255)

이것은 배열 수식이므로 Ctrl + Shift + Enter를 눌러 입력해야합니다. 찾은 첫 번째 대문자로 시작하는 셀의 모든 것을 반환합니다. 따라서 “mikeDAVIS”에서는 “DAVIS”를 반환하고 “mikeDavis”에서는 “Davis”를 반환합니다. B1 셀에서 배열 수식을 사용한다고 가정하면 다음을 사용하여 이름을 확인할 수 있습니다.

=SUBSTITUTE(A1,B1,"")

이것은 배열 수식이 아닌 일반 수식입니다.

거의 동일한 작업을 수행 할 수있는 유사한 배열 수식이 많이 있습니다. 예를 들어,이 배열 수식은 A1 셀에있는 이름 (첫 번째 대문자까지의 모든 문자)을 반환합니다.

=LEFT(A1,MAX((CODE(MID(A$1,ROW(INDIRECT("1:"& LEN(A1))),1))>96)*ROW(INDIRECT("1:"&LEN(A1)))))

그런 다음 동일한 정규식 (SUBSTITUTE 함수를 사용하는 수식)을 사용하여 성을 파생 할 수 있습니다.

이름을 찾기 위해 매크로 접근 방식을 사용하려면 텍스트의 첫 번째 대문자 위치를 반환하는 수식을 작성하기 만하면됩니다. 다음 코드는 텍스트에서이 “변경점”을 반환합니다.

Function GetFirstUpper(MyCell As Range) As Integer     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstUpper = 99     Else         GetFirstUpper = i     End If End Function

함수를 사용하기 위해 이름이 A1 셀에 있다고 가정합니다. 워크 시트에서 다음 수식을 사용하여 성과 이름을 찾을 수 있습니다.

=LEFT(A1,GetFirstUpper(A1)-1)

=MID(A1,GetFirstUpper(A1),LEN(TRIM(A1))-GetFirstUpper(A1)+1)

매크로가 실제 이름을 반환하는 것을 선호하는 경우 다음을 사용하여 첫 번째 대문자 앞의 모든 것을 반환 할 수 있습니다.

Function GetFirstName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetFirstName = sCellValue     Else         GetFirstName = Left(sCellValue, i - 1)

End If End Function

매크로를 사용하려면 워크 시트 셀에서 다음을 사용하기 만하면됩니다. (이것은 평가할 텍스트 문자열이 A1 셀에 있다고 가정합니다.)

=GetFirstName(A1)

매크로의 사소한 변형을 사용하면 처음 대문자로 시작하는 모든 것으로 가정되는 성을 비슷하게 가져올 수 있습니다.

Function GetLastName(MyCell As Range) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetLastName = sCellValue     Else         GetLastName = Mid(sCellValue, i)

End If End Function

원하는 경우 매크로를 단일 함수로 결합하여 지정한 내용에 따라 이름 또는 성을 반환 할 수 있습니다.

Function GetName(MyCell As Range, sWanted As String) As String     Dim sCellValue As String     Dim i As Integer

sCellValue = Trim(MyCell.Value)

i = 1     Do While (Asc(Mid(sCellValue, i, 1)) > 90 _       Or Asc(Mid(sCellValue, i, 1)) < 65) _       And i < Len(sCellValue) + 1         i = i + 1     Loop     If i > Len(sCellValue) Then         GetName = sCellValue     Else         If LCase(sWanted) = "first" Then             GetName = Left(sCellValue, i - 1)

Else             GetName = Mid(sCellValue, i)

End If     End If End Function

이 결합 된 함수를 사용하려면 원하는 이름을 지정하기 만하면됩니다.

=GetName(A1, "First")

이러한 방식으로 매개 변수로 전달 된 단어 “First”는 이름 (첫 번째 대문자 앞의 모든 것)을 반환합니다. 두 번째 매개 변수로 전달 된 다른 문자열 (예 : “Last”, “xxx”또는 “Rest”또는 “”)은 성이 반환됩니다.

_ 참고 : _

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

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

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

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

link : / excel-Splitting_Cells_by_Case [케이스 별 셀 분할].