Excel에서 여러 구분 기호를 사용해 텍스트를 열로 변환하는 6가지 방법

텍스트 나누기(텍스트를 열로 변환)는 업무에서 자주 마주치는 작업입니다. 하나의 셀에 여러 정보가 붙어 있을 때 이를 개별 열로 분리해야 하는 경우가 많은데, 바로 이럴 때 Microsoft Excel이 큰 힘을 발휘합니다.
이 글에서는 Excel에서 여러 구분 기호를 사용해 텍스트를 열로 변환하는 방법을 단계별로 자세히 살펴보겠습니다.

Excel에서 여러 구분 기호로 텍스트를 열로 변환하는 6가지 방법

이번 가이드에서는 B4:B13 범위에 있는 베스트셀러 목록 데이터셋을 예시로 사용합니다. 이 데이터에는 책 이름, 저자, 장르 정보가 포함되어 있으며, 우리의 목표는 이 세 가지 정보를 각각 별도의 열로 분리하는 것입니다. 지금부터 각 방법을 하나씩 자세히 알아보겠습니다.

여기서는 Microsoft Excel 365 버전을 사용했지만, 본인에게 맞는 다른 버전을 사용하셔도 무방합니다.

방법 1: 텍스트 나누기 기능 활용하기

가장 먼저 소개할 방법은 Excel의 기본 제공 기능인 텍스트 나누기(Text to Columns)입니다. 여러 구분 기호로 된 텍스트를 손쉽게 열로 변환할 수 있는 유용한 도구입니다. 아래 단계를 따라 해보세요.

📌 단계:

• 먼저 B5:B13 셀을 선택합니다 >> 데이터 탭으로 이동 >> 텍스트 나누기 옵션을 클릭합니다.

그러면 곧바로 텍스트 마법사 창이 나타납니다.

• 구분 기호로 분리됨(Delimited) 옵션을 선택 >> 다음 버튼을 클릭합니다.

• 쉼표(Comma) 구분 기호에 체크 표시 >> 다음 버튼을 누릅니다.

• 원하는 위치에 대상 셀(Destination)을 지정합니다. 여기서는 C5 셀을 지정 >> 마침 버튼을 클릭합니다.

• 경고 메시지가 나타나면 확인 버튼을 클릭하세요.

완료되면 아래 스크린샷과 같은 결과를 얻을 수 있습니다.

추가 팁: Excel에서 캐리지 리턴을 구분 기호로 텍스트 나누기 사용하는 방법

방법 2: TRIM, MID, SUBSTITUTE, REPT, LEN 함수 조합하기

함수는 Excel 스프레드시트의 핵심이라고 할 수 있습니다. 이 방법에서는 TRIM, MID, SUBSTITUTE, REPT, LEN 함수를 조합하여 여러 구분 기호가 있는 텍스트를 열로 분리합니다. 각 함수의 역할은 다음과 같습니다. LEN 함수는 문자열의 길이를 반환하고, REPT 함수는 텍스트를 반복합니다. SUBSTITUTE 함수는 기존 텍스트를 새 텍스트로 대체하며, MID 함수는 텍스트 중간의 특정 문자들을 반환합니다. 마지막으로 TRIM 함수는 불필요한 공백을 제거합니다.

📌 단계:

• 먼저 C6 셀로 이동 >> 아래 수식을 입력합니다.

=TRIM(MID(SUBSTITUTE($B6,",",REPT(" ",LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)))

여기서 B6와 C5 셀은 각각 책 이름, 저자, 장르 열과 숫자 1을 참조합니다.

수식 분석:

• LEN($B6) → 텍스트 문자열의 문자 수를 반환합니다. B6 셀이 text 인수이며, 결과값은 43입니다.
  • 결과 → 43 ”          “
• REPT(” “,LEN($B6)) → 다음과 같이 계산됩니다
  • REPT(” “,43) → 텍스트를 지정된 횟수만큼 반복합니다. ” “는 공백을 의미하는 text 인수이고, 43은 공백을 43번 반복하라는 number_times 인수입니다.
  • 결과 → ”          “
• SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))) → 문자열 내 기존 텍스트를 새 텍스트로 교체합니다. B6는 text 인수, “,”는 old_text 인수, REPT(” “,LEN($B6))는 쉼표를 공백으로 대체하는 new_text 인수입니다.
  • 결과 → “Poor Economics     Abhijit Banarjee     Economics”
• MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6)) → 시작 위치와 길이를 기준으로 텍스트 문자열 중간의 문자를 반환합니다. (C$5-1)*LEN($B6)+1은 start_num 인수, LEN($B6)은 num_chars 인수입니다.
  •  결과 → “Poor Economics     “
• TRIM(MID(SUBSTITUTE($B6,”,”,REPT(” “,LEN($B6))),(C$5-1)*LEN($B6)+1,LEN($B6))) → 최종 수식은 다음과 같이 완성됩니다
  • TRIM(“Poor Economics     ”)  → 텍스트에서 불필요한 공백을 모두 제거하여 깔끔한 결과를 얻습니다.
  •  결과 → “Poor Economics”

• 다음으로 채우기 핸들(Fill Handle) 도구를 사용해 수식을 행 방향으로 복사합니다.

• 그런 다음 C6:E6 셀을 선택 >> 채우기 핸들을 아래로 드래그하여 나머지 셀에도 수식을 적용합니다.

모든 과정을 마치면 아래 그림과 같은 최종 결과를 얻을 수 있습니다.

추가 학습: Excel에서 수식으로 텍스트를 자동으로 열로 분리하는 방법

방법 3: LEFT, RIGHT, MID, LEN, FIND 함수 결합하기

또 다른 방법으로 LEFT, RIGHT, MID, LEN, FIND 함수를 조합하여 여러 구분 기호가 있는 텍스트를 서로 다른 열로 분리할 수 있습니다. 이때 FIND 함수는 배열 내에서 특정 문자의 위치를 찾고, LEN 함수는 텍스트 문자열의 길이를 반환합니다. MID 함수는 문자열 중간의 문자를 반환하며, LEFT와 RIGHT 함수는 각각 문자열의 왼쪽과 오른쪽 끝에서 텍스트를 추출합니다.
예를 들어 B4:B12 범위에 있는 고객 목록 데이터셋이 있다고 가정해 보겠습니다. 이 데이터는 세미콜론(;)으로 구분된 이름, 국가, 도시 열을 포함하고 있으며, 우리는 이를 별도의 열로 나누려고 합니다. 자세한 과정을 살펴보겠습니다.

📌 단계:

먼저 C5 셀로 이동 >> 수식 입력줄에 아래 식을 입력합니다.

=LEFT(B5,FIND(";",B5)-1)

위 수식에서 B5 셀은 이름, 국가, 도시 열을 참조합니다.

수식 분석:

• FIND(“;”,B5) → 한 텍스트 문자열 안에서 다른 텍스트의 시작 위치를 반환합니다. “;”는 find_text 인수이고 B5는 within_text 인수입니다. 즉, FIND 함수는 문자열에서 세미콜론(;) 문자의 위치를 알려줍니다.
  • 결과 → 7
• LEFT(B5,FIND(“;”,B5)-1) → 다음과 같이 계산됩니다
  • LEFT(B5,7)  → 문자열 시작 부분부터 지정된 개수의 문자를 반환합니다. B5 셀은 text 인수이고 7은 num_chars 인수로, 왼쪽에서 7개의 문자를 가져옵니다.
  •  결과 → Trevor

• 이제 D5 셀로 이동 >> 아래 수식을 입력합니다.

=MID(B5,FIND(";",B5)+1,FIND("@",B5)-FIND(";",B5)-1)

수식 설명:

• FIND(“@”,B5)-FIND(“;”,B5)-1 → FIND 함수가 문자열 내 세미콜론(;)과 @ 문자의 위치를 각각 반환합니다.
  • 16 – 7 – 1 → 8
• FIND(“;”,B5)+1 → FIND 함수가 문자열에서 세미콜론(;)의 위치를 찾습니다.
  • 7 + 1 → 8
• MID(B5,FIND(“;”,B5)+1,FIND(“@”,B5)-FIND(“;”,B5)-1) → 다음과 같이 계산됩니다
  • MID(B5,8,8)  → B5 셀은 text 인수, 첫 번째 8은 start_num 인수, 두 번째 8은 num_chars 인수로, 앞의 8자 이후 8개의 문자를 반환합니다.
  •  결과 → Iceland

• 마지막으로 E5 셀에 아래 수식을 입력합니다.

=RIGHT(B5,LEN(B5)-FIND("@",B5))

수식 작동 원리:

• LEN(B5)-FIND(“@”,B5) → LEN 함수는 B5 셀 문자열의 길이를 반환하고, FIND 함수는 @ 문자의 위치를 반환합니다.
  •  26 – 16 → 10

• RIGHT(B5,LEN(B5)-FIND(“@”,B5)) → 다음과 같이 계산됩니다
  • RIGHT(B5,10) → 문자열 끝에서부터 지정된 개수의 문자를 반환합니다. B5 셀은 text 인수이고 10은 num_chars 인수로, 오른쪽에서 10개의 문자를 가져옵니다.
  •  결과 → Reykjavik

모든 단계를 완료하면 아래 스크린샷과 같은 결과가 나타납니다.

방법 4: 빠른 채우기(Flash Fill) 활용하기

복잡한 수식을 사용하기 어렵다면 이번 방법이 딱 맞을 것입니다. Excel의 빠른 채우기(Flash Fill) 기능을 활용하면 몇 번의 클릭만으로 여러 구분 기호가 있는 텍스트를 열로 변환할 수 있습니다.

📌 단계:

• 먼저 C5 셀에 직접 Trevor라고 입력 >> 홈 탭에서 채우기 드롭다운 클릭 >> 빠른 채우기 옵션을 선택합니다.

그러면 Excel이 나머지 셀을 자동으로 채워줍니다.

같은 방식으로 국가와 도시 열에도 적용하면 아래 이미지와 같은 최종 결과를 얻을 수 있습니다.

방법 5: 파워 쿼리(Power Query) 사용하기

파워 쿼리(Power Query)는 데이터를 빠르고 효율적으로 정리·분석할 수 있는 강력한 도구임에도 불구하고 의외로 많이 간과되는 Excel 기능입니다. 이 섹션에서는 클릭 몇 번 만으로 여러 구분 기호가 있는 텍스트를 열로 변환하는 방법을 소개합니다.

📌 단계:

• 먼저 B4 셀로 이동 >> CTRL + T 단축키를 눌러 Excel 테이블을 삽입 >> 확인을 누릅니다.

• 다음으로 데이터 탭으로 이동 >> 테이블/범위에서 옵션을 클릭합니다.

곧 파워 쿼리 편집기가 열립니다.

• 열 분할 드롭다운을 클릭 >> 구분 기호 기준 옵션을 선택합니다.

• 세미콜론 옵션을 선택 >> 구분 기호의 각 항목에 체크 >> 확인 버튼을 누릅니다.

• 그런 다음 열 머리글을 더블클릭하여 이름을 변경 >> 닫기 및 로드 옵션을 눌러 파워 쿼리 창을 종료합니다.

모든 단계를 완료하면 아래와 같은 결과를 얻을 수 있습니다.

추가 학습: Excel 텍스트 나누기에서 줄 바꿈을 구분 기호로 사용하는 방법

방법 6: VBA 코드 적용하기

Excel에서 여러 구분 기호로 텍스트를 열로 변환하는 작업을 자주 수행한다면 아래의 VBA 코드를 활용해 보세요. 간단하고 쉽게 따라 할 수 있습니다.

📌 단계:

• 먼저 개발 도구 탭으로 이동 >> Visual Basic 버튼을 클릭합니다.

즉시 Visual Basic 편집기가 새 창에서 열립니다.

• 다음으로 삽입 탭으로 이동 >> 모듈(Module)을 선택합니다.

참고를 위해 아래 코드를 복사해서 편집기 창에 붙여넣으세요.

Sub Separate_Text_String()

Dim Arr() As String, _
cnt As Long, _
j As Variant

For k = 5 To 13
 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 코드를 설명합니다. 코드는 크게 2단계로 구성되어 있습니다.

• 첫 번째 부분에서는 서브루틴의 이름을 정의합니다. 여기서는 Separate_Text_String()입니다.
• 다음으로 변수 Arr, cnt, j를 각각 String, Long, Variant 형식으로 선언합니다.

• 두 번째 부분에서는 For 루프를 통해 각 셀을 순회하며 세미콜론으로 구분된 텍스트를 분할합니다.
• 코드에서 “For k = 5 To 13” 문장은 데이터의 시작 행과 끝 행을 나타내며, 여기서는 5행부터 13행까지입니다.
• “Arr = Split(Cells(k, 2), “;”)” 문의 “;”는 구분 기호로, 필요에 따라 쉼표, 파이프(|) 등으로 변경할 수 있습니다.
• 마지막으로 “cnt = 3”은 세 번째 열(C열)을 의미합니다.

• 이후 실행(Run) 버튼 또는 키보드의 F5 키를 누릅니다.

최종적으로 아래 스크린샷과 같은 결과가 화면에 나타납니다.

연습 문제

각 시트의 오른쪽에 연습 영역을 제공하고 있으니 직접 실습해 보세요. 스스로 해보는 것이 실력 향상에 가장 좋습니다.

마무리

지금까지 Excel에서 여러 구분 기호를 사용해 텍스트를 열로 변환하는 6가지 효과적인 방법을 살펴보았습니다. 글 전체를 꼼꼼히 읽고 무료 워크북을 다운로드하여 직접 연습해 보세요. 이 글이 도움이 되었기를 바라며, 추가 질문이나 제안 사항이 있다면 언제든지 댓글로 남겨주세요. 이와 같은 유용한 글을 더 보고 싶다면 ExcelDemy를 방문해 주세요.

관련 글

• Excel에서 날짜에 텍스트 나누기 사용하는 방법 (쉬운 단계별 가이드)
• [해결!] Excel 텍스트 나누기 시 데이터가 삭제되는 문제
• Excel에서 데이터 덮어쓰기 없이 텍스트를 열로 변환하는 방법