일상적인 업무에서 텍스트를 여러 열로 분리해야 하는 경우가 자주 있습니다. Microsoft Excel은 이러한 작업에 매우 유용한 도구이며, 관련된 자료도 이미 많이 찾아볼 수 있습니다. 하지만 이 글의 특별한 점은 원본 데이터를 덮어쓰지 않고 텍스트를 열로 변환하는 다양한 방법을 소개한다는 것입니다. 즉, 작업을 수행하는 동안에도 원본 열은 그대로 보존됩니다. 지금부터 하나씩 살펴보고 직접 실습해 보세요.
Excel에서 텍스트를 덮어쓰지 않고 열로 변환하는 5가지 방법
이해를 돕기 위해 한 기관의 학생 이름 및 나이 목록 데이터를 사용하겠습니다. 이 데이터셋에는 B열에 이름(Name)과 나이(Age)가 함께 입력되어 있습니다.
이제 B열의 텍스트를 덮어쓰지 않고 여러 열로 나누는 방법을 알아보겠습니다. 여기서 '덮어쓰지 않음'이란 작업 과정에서 원본 텍스트가 그대로 유지된다는 의미입니다. 이 글은 Microsoft Excel 365 버전을 기준으로 작성되었으며, 사용 중인 버전에 맞게 그대로 적용할 수 있습니다.
1. LEN, MID, REPT, SUBSTITUTE, TRIM 함수 조합하기
수식 활용을 좋아하는 분들에게 추천하는 방법입니다. 여러 함수를 조합하여 하나의 수식을 완성합니다. 단계별로 자세히 살펴보겠습니다.
📌 단계:
- 먼저 C열과 D열에 각각 이름, 나이라는 제목의 두 개 열을 만듭니다.
- C5와 D5 셀에 각각 1과 2를 입력합니다. 이 숫자들은 새로 만든 열의 번호 역할을 하며, 그 기능은 이후 단계에서 확인할 수 있습니다.
- C6 셀을 선택하고 아래 수식을 입력합니다.
=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))
여기서 B6은 첫 번째 이름, 나이 데이터를, C5는 숫자 1을 참조합니다.
수식 설명:
- LEN($B6) → LEN 함수는 텍스트 문자열의 문자 수를 반환합니다. 여기서 B6 셀이 text 인수이며 결과값은 11입니다.
- 결과 → 11
- REPT(" ",LEN($B6)) → REPT 함수는 텍스트를 지정한 횟수만큼 반복합니다. 공백(" ")을 text 인수로, 11을 number_times 인수로 사용하여 공백 11개를 삽입합니다.
- 결과 → " "
- SUBSTITUTE($B6,",",REPT(" ",LEN($B6))) → SUBSTITUTE 함수는 문자열 내 기존 텍스트를 새 텍스트로 대체합니다. B6은 text, 쉼표(",")는 old_text, REPT 결과값은 new_text 인수로, 쉼표가 공백으로 바뀝니다.
- 결과 → John 18 yr
- MID(SUBSTITUTE(...),(C$5-1)*LEN($B6)+1,LEN($B6)) → MID 함수는 시작 위치와 길이를 기준으로 문자열 중간의 문자를 반환합니다. SUBSTITUTE 결과가 text, (C$5-1)*LEN($B6)+1이 start_num, LEN($B6)이 num_chars 인수가 됩니다.
- 결과 → John
- TRIM(MID(SUBSTITUTE(...))) → 마지막으로 TRIM 함수가 앞뒤의 불필요한 공백을 모두 제거합니다.
- 결과 → John
- 그런 다음 Enter 키를 누릅니다.
- 채우기 핸들(Fill Handle)을 행 방향으로 드래그하여 수식을 복사합니다.
이제 D6 셀에서 텍스트의 오른쪽 부분, 즉 나이 값이 추출된 것을 확인할 수 있습니다.
- C6과 D6 셀을 함께 선택한 뒤, 커서를 D6 셀의 오른쪽 아래 모서리에 놓으면 채우기 핸들이 다시 나타납니다.
- 더블클릭하면 아래 셀들에 수식이 자동으로 적용됩니다.
완성된 결과는 아래와 같습니다.
2. LEFT, LEN, RIGHT, SEARCH 함수 활용하기
복잡한 수식을 작성하고 여러 함수를 조합하는 것이 부담스럽다면, 두 번째 방법이 답입니다. 이번에도 수식을 사용하지만 훨씬 더 이해하기 쉽습니다.
📌 단계:
- 먼저 C5 셀에 아래 수식을 입력합니다.
=LEFT(B5,SEARCH(", ",B5,1)-1)
여기서 B5는 첫 번째 이름, 나이 데이터입니다.
수식 설명:
- SEARCH(",",B5) → SEARCH 함수는 한 문자열이 다른 문자열 내에서 처음 나타나는 위치를 반환합니다. 여기서 ","는 find_text, B5는 within_text 인수이며, 쉼표(,)의 위치를 반환합니다.
- 결과 → 5
- LEFT(B5,SEARCH(",",B5)-1) → LEFT 함수는 문자열 시작부터 지정한 개수만큼의 문자를 반환합니다. B5가 text, 5가 num_chars 인수이므로 왼쪽에서 5글자를 가져옵니다.
- 결과 → John
- 다음으로 Enter 키를 누릅니다.
이번에는 비슷한 방식으로 D5 셀의 값을 구합니다. 앞선 수식에서는 LEFT 함수로 문자열의 첫 번째 부분을 가져왔다면, 여기서는 두 번째 부분을 추출합니다.
- D5 셀에 아래 수식을 입력합니다.
=RIGHT(B5,LEN(B5)-SEARCH(",",B5))
수식 설명:
- LEN(B5)-SEARCH(",",B5) → LEN 함수가 B5 셀 문자열의 전체 길이를 반환하고, SEARCH 함수가 쉼표(,)의 위치를 반환합니다.
- 결과 → 11 – 5 → 6
- RIGHT(B5,LEN(B5)-SEARCH(",",B5)) → RIGHT 함수는 문자열 끝에서 지정한 개수만큼의 문자를 반환합니다. B5가 text, 6이 num_chars 인수이므로 오른쪽에서 6글자를 가져옵니다.
- 결과 → 18 yr
- 마지막으로 Enter 키를 누르면 완료됩니다.
3. 텍스트 나누기(Text to Columns) 기능 사용하기
이 방법은 Excel의 텍스트 나누기(Text to Columns) 기능을 활용합니다. 덮어쓰지 않고 텍스트를 열로 변환할 수 있는 매우 편리한 도구입니다. 아래 단계를 따라 해 보세요.
📌 단계:
- 먼저 B5:B14 범위의 셀을 선택합니다.
- 데이터(Data) 탭으로 이동합니다.
- 데이터 도구(Data Tools) 그룹에서 텍스트 나누기 아이콘을 클릭합니다.
- 드롭다운 목록에서 텍스트 나누기(Text to Columns) 기능을 선택합니다.
곧바로 텍스트 마법사 대화 상자가 열립니다.
- 1단계에서 구분 기호로 분리됨(Delimited)을 선택합니다.
- 다음(Next) 버튼을 클릭합니다.
- 2단계에서 구분 기호 항목의 쉼표(Comma)를 선택합니다.
- 다음(Next)을 클릭합니다.
- 3단계에서 대상 상자(Destination)를 C5로 설정합니다. 이렇게 하면 원본 열이 아닌 새 위치에 결과가 저장됩니다.
- 마침(Finish) 버튼을 클릭합니다.
단계를 완료하면 경고 메시지 상자가 나타날 수 있지만 걱정하지 않아도 됩니다.
- 확인(OK)을 클릭하면 됩니다.
이제 원본을 덮어쓰지 않고 텍스트가 여러 열로 성공적으로 분리되었습니다.
4. 플래시 채우기(Flash Fill) 기능 활용하기
복잡한 수식 사용이 어렵다면 다음 방법이 적합합니다. Excel의 플래시 채우기(Flash Fill) 기능을 사용하여 텍스트를 덮어쓰지 않고 열로 변환할 수 있습니다.
📌 단계:
- 먼저 C5 셀에 John이라고 직접 입력합니다. 이것이 해당 셀에 원하는 결과이며, 패턴 학습을 위해 처음 한 번만 수동으로 입력하면 됩니다.
- 홈(Home) 탭으로 이동합니다.
- 편집(Editing) 그룹의 채우기(Fill) 드롭다운 아이콘을 클릭합니다.
- 플래시 채우기(Flash Fill)를 선택합니다.
Excel이 자동으로 나머지 셀들을 채워줍니다.
같은 방법으로 D열의 나이(Age) 열도 처리합니다.
5. VBA 코드 적용하기
수식을 사용하는 것은 빠른 편집 방법이지만, 해석이 어려울 수 있습니다. 또한 이 작업을 반복적으로 자동화해야 한다면 아래의 VBA 코드를 활용하는 것이 좋습니다.
📌 단계:
- 먼저 개발 도구(Developer) 탭으로 이동합니다.
- 코드(Code) 그룹에서 Visual Basic을 선택합니다.
- 키보드 단축키 Alt + F11을 사용해도 동일하게 실행할 수 있습니다.
Microsoft Visual Basic for Applications 창이 열립니다.
- 프로젝트 탐색기(Project Explorer)에서 해당 시트(예: Sheet6(VBA))를 더블클릭합니다.
오른쪽에 코드 모듈이 열립니다.
- 아래 코드를 복사하여 모듈에 붙여넣습니다.
Sub Text_to_Columns_without_Overwriting()
Dim Arr() As String, cnt As Long, j As Variant
For k = 5 To 14
Arr = Split(Cells(k, 2), ",")
cnt = 3
For Each j In Arr
Cells(k, cnt) = j
cnt = cnt + 1
Next j
Next k
End Sub
코드 설명:
덮어쓰지 않고 텍스트를 열로 변환하는 데 사용된 VBA 코드를 설명하겠습니다. 코드는 크게 두 부분으로 나뉩니다.
- 첫 번째 부분에서는 서브루틴 이름을 정의합니다. 여기서는 Text_to_Columns_without_Overwriting()입니다.
- 변수 Arr, cnt, j를 각각 String, Long, Variant 형식으로 선언합니다.
- 두 번째 부분에서는 For 루프를 사용하여 각 셀을 순회하며 쉼표로 구분된 텍스트를 분리합니다.
- For k = 5 To 14 문은 데이터의 시작 행과 끝 행을 의미하며, 여기서는 5행부터 14행까지입니다.
- Arr = Split(Cells(k, 2), ",")의 ","는 구분 기호이며, 필요에 따라 세미콜론, 파이프(|) 등으로 변경할 수 있습니다.
- 마지막으로 cnt = 3은 결과가 저장될 세 번째 열(C열)을 나타냅니다.
- 이제 리본 메뉴의 초록색 재생 버튼, 즉 실행(Run) 버튼을 클릭합니다. 키보드에서 F5를 눌러도 동일하게 실행됩니다.
최종 결과는 아래 스크린샷과 같습니다.
연습 문제
직접 연습해 볼 수 있도록 각 시트의 오른쪽에 연습(Practice) 섹션을 마련해 두었습니다. 배운 내용을 스스로 실습해 보시기 바랍니다.
결론
이 글에서는 Excel에서 원본 데이터를 덮어쓰지 않고 텍스트를 여러 열로 변환하는 다섯 가지 방법을 간결하고 명확하게 소개했습니다. 수식 조합, 텍스트 나누기, 플래시 채우기, VBA 코드까지 상황에 맞는 방법을 선택해 활용해 보세요. 궁금한 점이나 제안 사항이 있다면 댓글로 알려주세요. 더 많은 Excel 활용 팁은 원스톱 Excel 솔루션 사이트 Exceldemy에서 확인하실 수 있습니다.
함께 읽으면 좋은 글
- Excel에서 텍스트 나누기로 날짜 변환하기 (초간단 단계별 가이드)
- [해결됨!] Excel 텍스트 나누기 시 데이터가 삭제되는 문제
- Excel에서 여러 구분 기호로 텍스트를 열로 변환하는 방법