Excel 에서 대문자를 기준으로 텍스트를 별도의 열로 분할하려면 어떻게 해야 하나요？
Excel 에서 데이터를 작업하다 보면, 각 단어가 대문자로 시작되는 여러 단어가 연결된 셀 값을 종종 접하게 됩니다. 예를 들어 「FirstNameLastName」이나 「SalesMarchApril」과 같은 형태입니다. 데이터 정제나 분석을 위해 아래 스크린샷처럼 각 대문자 위치에서 텍스트를 별도의 열로 분할해야 할 수도 있습니다. 그러나 Excel 에는 대문자를 기준으로 직접 분할하는 기본 제공 기능이 없습니다。 그렇다면 대문자를 기준으로 단어를 빠르고 정확하게 개별 열로 분리할 수 있는 실용적인 방법은 무엇일까요？
아래에서는 이 문제를 해결하기 위한 세 가지 효과적인 방법을 소개합니다。 각 방법은 적용 가능한 상황과 장점이 다릅니다。
수식을 사용하여 대문자를 기준으로 텍스트를 별도의 열로 분할
데이터에 대문자로 연결된 두 단어만 포함된 셀 값이 있는 경우， Excel 수식을 사용해 내용을 별도의 열로 분할할 수 있습니다。 이 방법은 간단하고 코드나 추가 기능 없이도 가능하며， 소규모 데이터셋이나 덜 복잡한 분할 요구사항에 적합합니다。
1。 데이터 옆의 빈 셀(예： C2 셀)에 A2 셀(분할할 텍스트가 포함된 셀)에서 첫 번째 단어를 추출하는 다음 배열 수식을 입력하세요。
=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)수식을 입력한 후에는 일부 Excel 버전에서 배열 수식을 사용하기 위해 반드시Ctrl + Shift + Enter를 동시에 눌러야 합니다。 올바르게 입력되면 수식 표시줄의 수식 양쪽에 중괄호 {}가 표시됩니다。 이 수식은 셀에서 두 번째 대문자의 위치를 찾아 그 앞의 모든 문자를 추출함으로써 첫 번째 단어를 반환합니다。
2。 다른 항목에도 수식을 적용하려면 C2 셀을 선택한 후 채우기 핸들을 아래로 드래그하여 열의 추가 셀에 수식을 복사하세요。 그러면 각 행의 해당 첫 번째 단어가 자동으로 추출됩니다。
3。 다음으로 오른쪽 셀(예： D2)에 나머지 텍스트(두 번째 단어)를 추출하는 다음 수식을 입력하세요。
=REPLACE(A2,1,LEN(C2),"")이 수식은 이전 단계에서 추출한 첫 번째 단어를 제거하여 두 번째 단어만 남깁니다。 확인을 위해 간단히Enter를 누르세요。
4。 마찬가지로 D2 셀을 선택하고 채우기 핸들을 사용해 이 수식을 다른 행으로 드래그하세요。 그러면 원본 범위의 각 셀 텍스트가 대문자 위치를 기준으로 두 열로 분할됩니다。
KUTOOLS AI 와 함께 엑셀의 마법을 경험하세요
- 스마트 실행： 간단한 명령어로 셀 작업을 수행하고， 데이터를 분석하며， 차트를 생성하세요。
- 사용자 지정 수식： 워크플로우를 간소화할 맞춤형 수식을 생성하세요。
- VBA 코딩： VBA 코드를 손쉽게 작성하고 적용하세요。
- 수식 해석： 복잡한 수식도 쉽게 이해하세요。
- 텍스트 번역： 스프레드시트 내에서 언어 장벽을 허물어 보세요。
VBA 코드를 사용하여 대문자를 기준으로 텍스트를 별도의 열로 분할
앞서 설명한 수식 기반 방법은 두 단어만 분할해야 하는 경우에 적합하지만, 'FirstNameMiddleNameLastName'처럼 두 개 이상의 연결된 단어가 포함된 셀에는 적용할 수 없습니다. 이런 경우에는 VBA 코드를 사용해 각 대문자 앞에 공백을 삽입하여 단어를 구분할 수 있습니다. 공백을 추가한 후에는 Excel 의 기본 제공텍스트를 열로 분리기능을 사용해 텍스트를 개별 열로 분할할 수 있습니다。
먼저 VBA 코드를 적용하여 단어를 구분하기 위해 대문자 앞에 공백을 삽입합니다。
1.ALT + F11를 눌러Microsoft Visual Basic for Applications편집기 창을 엽니다。
2。 VBA 편집기에서삽입>모듈을 클릭하고 새 모듈 창에 다음 코드를 붙여넣습니다。
VBA 코드： 셀 내 각 대문자 앞에 공백 삽입
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
Dim I As Integer
SplitWords = Left(Str, 1)
For I = 2 To Len(Trim(Str))
If (Asc(Mid(Str, I, 1)) > 64) And _
(Asc(Mid(Str, I, 1)) < 91) And _
(Mid(Str, I - 1, 1) <> " ") Then _
SplitWords = SplitWords & " "
SplitWords = SplitWords & Mid(Str, I, 1)
Next
End Function
코드를 입력하거나 붙여넣은 후 VBA 편집기를 닫고 워크시트로 돌아옵니다。 이 코드는splitwords라는 새 함수를 정의하며， 일반 워크시트 수식처럼 사용할 수 있습니다。 이 함수를 사용하려면 매크로를 활성화해야 하며， VBA 코드를 유지하려면 통합 문서를 .xlsm 파일로 저장해야 합니다。
3。 데이터 옆의 빈 셀에 A2 셀의 모든 대문자 앞에 공백을 삽입하는 다음 수식을 입력하세요。
=splitwords(A2)이 수식은 사용자 정의 함수를 호출하여 각 셀을 처리합니다. Enter 를 누른 후에는 각 대문자 앞에 공백이 삽입되어 개별 단어가 명확히 구분된 텍스트를 확인할 수 있습니다。
4。 채우기 핸들을 사용해 이 수식을 관련 셀 전체에 복사하여 각 행이 적절히 처리되도록 합니다。
5。 이 시점에서 셀에는 수식이 포함되어 있습니다。 수식 연결 없이 처리된 텍스트만 유지하려면 값을 다른 위치에 복사하여 붙여넣을 수 있습니다。 이를 위해 처리된 셀을 선택하고 복사한 후， 원하는 위치에서 마우스 오른쪽 버튼을 클릭하고 '특수 붙여넣기‘ > 『값』을 선택하세요。
다음으로 공백을 기준으로 셀 내용을 별도의 열로 분할하기 위해 텍스트를 열로 분리 기능을 적용합니다。
6。 이제 공백이 포함된 셀 값(이전 단계의 결과)을 선택합니다。 리본에서데이터>텍스트를 열로 분리를 클릭합니다。텍스트를 열로 변환 마법사가 나타나면(1 단계)，구분 기호 사용라디오 버튼를 선택합니다。
7.다음을 클릭하고 2 단계에서 구분 기호 아래에 있는공백옵션을 선택합니다. 이렇게 하면 Excel 이 공백을 기준으로 데이터를 열로 분할하도록 지시됩니다。
8. 3 단계에서 열 데이터 형식으로일반이 선택되어 있는지 확인하세요(필요한 경우 다른 형식을 선택할 수도 있음)。 또한 데이터 분할이 표시될 셀을
를 클릭하여 선택하세요。 대상 범위가 기존 데이터를 덮어쓰지 않도록 주의하세요。
9.마침을 클릭하면 Excel 이 텍스트를 여러 열로 분리하여 각 열에 원래 대문자로 구분되었던 단어를 표시합니다。
일부 단어가 예상대로 분할되지 않는 오류가 발생하면 약어(연속된 대문자)나 뒤쪽의 공백 같은 특수한 상황을 확인하세요. VBA 코드를 개선하거나 데이터의 이상치를 다시 점검하는 것이 좋습니다. 또한 VBA 를 사용하거나 일괄 데이터 작업을 수행할 때는 정기적으로 작업 내용을 저장하세요。
이 방법은 셀당 단어 수나 텍스트 길이가 일관되지 않은 경우에 특히 유용합니다。 다만 일부 사용자는 VBA 코드 실행에 익숙하지 않을 수 있으므로 아래의 대체 방법을 고려할 수도 있습니다。
대문자를 기준으로 텍스트를 별도의 열로 분할하는 Kutools for Excel
Kutools for Excel는 모든 숙련도 수준의 사용자가 대문자를 기준으로 텍스트를 분할할 수 있도록 친근하고 효율적인 솔루션을 제공합니다。 특히 대규모 데이터셋이나 반복 작업에 유용하며，텍스트 추가및셀 분할기능을 함께 사용하면 최소한의 노력과 리스크로 작업을 완료할 수 있습니다。
1。 분할하려는 원본 데이터가 포함된 셀을 선택합니다。
2.Kutools>텍스트>텍스트 추가。 이 도구를 사용하면 공백과 같은 문자나 구분 기호를 각 대문자 앞에 삽입할 수 있습니다。
3.텍스트 추가대화 상자에서텍스트입력란에 공백(또는 필요시 다른 구분 기호)을 입력합니다。 그런 다음다음 경우에만 추가섹션에서첫 글자가 대문자인 경우를 선택합니다. 이렇게 하면 Kutools 가 선택한 셀의 모든 대문자 앞에 지정한 구분 기호를 추가합니다。
4.확인을 클릭하면 Kutools 가 각 대문자 앞에 자동으로 공백을 추가하여 데이터를 즉시 분할 준비 상태로 만듭니다。
5。 다음으로 처리된 범위가 계속 선택된 상태에서 다음으로 이동합니다。Kutools>병합 및 분할>셀 분할을(를) 클릭하여 구분 기호를 기준으로 텍스트를 열로 분리합니다。
6.셀 분할대화상자에서열로 분할을(를) 선택하고，유형섹션에서공백(또는 이전에 추가한 구분 기호) 확인란을 선택하여 분할 위치를 지정합니다。
7.확인을(를) 클릭합니다。 분할 결과를 저장할 시작 셀을 선택할 수 있는 프롬프트가 나타납니다。 분할된 열이 모두 들어갈 수 있도록 대상 위치를 신중하게 지정하세요。
8。 마지막으로확인을(를) 클릭하여 분할 작업을 완료합니다。 이제 데이터가 모든 대문자 위치에서 정확하고 효율적으로 별도의 열로 나누어집니다。
이 방법은 복잡하거나 대규모이며 자주 업데이트되는 데이터에 이상적이며 수동 수식 조정이나 코딩을 피하는 데 도움이 됩니다. Kutools for Excel 은 다양한 추가 기능을 제공하여 일반적이고 고급 작업 모두에서 효율성을 더욱 높여줍니다。
지금 바로 Kutools for Excel 을(를) 다운로드하고 무료로 체험해 보세요！
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for 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 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약