엑셀 텍스트 합치기, 버전별 함수 및 실전예제 총정리

이번 강의에 사용된 기초 함수

엑셀 2019 이후 버전을 사용한다면, 기본으로 제공되는 TEXTJOIN 함수를 활용하면 여러 셀에 나누어 입력된 텍스트를 가장 간결하게 합칠 수 있습니다.

MS365 버전을 사용한다면 CONCAT 함수를 사용할 수도 있습니다.

다만 CONCAT 함수는 병합되는 값 사이에 구분자를 추가할 수 없어, 실무에서는 보편적으로 TEXTJOIN 함수가 더 많이 사용됩니다.

1. 예제파일을 실행한 뒤, [2019이상 - TEXTJOIN&CONCAT] 시트로 이동합니다. 이후 G5셀에 아래 수식을 입력합니다.
   =TEXTJOIN(",",TRUE,C5:F5)

   

2. 이후 수식을 아래 방향으로 자동채우기하면 각 직원별 해외주재원 희망 국가가 쉼표로 구분되어 병합됩니다.

   

& 기호는 서로 다른 두 문자열을 결합할 때 사용하는 연산자입니다. 엑셀에서 사용되는 모든 연산자의 종류와 자세한 사용법은 아래 포스트에서 확인하실 수 있습니다.

1. 예제파일의 두 번째 시트인 [2016이전 - 기초예제] 시트로 이동합니다. 이후 F5셀에 아래 수식을 입력합니다.
   =B3&C3&D3&E3

   

2. 수식을 아래로 자동채우기하면 텍스트 합치기가 완료됩니다.
   
3. 항목 사이에 쉼표(,)를 추가하려면 F5셀에 아래 수식을 입력합니다. 그러면 항목 사이에 쉼표가 추가됩니다.
   =B3&","&C3&","&D3&","&E3

   

CONCATENATE 함수 사용법

모든 엑셀 버전에서 제공되는 CONCATENATE 함수를 사용할 수도 있습니다. & 기호를 사용하는 것보다 편리하지만, 함수의 인수로 범위를 입력할 수 없어 셀을 하나씩 나눠 입력해야 한다는 단점이 있습니다.

1. [2016 이전 - 기초예제] 시트의 F9셀에 아래 수식을 입력합니다.
   =CONCATENATE(B9,C9,D9,E9)

   

2. 이후 수식을 아래로 자동채우기하면 텍스트 합치기가 완료됩니다.

   

3. 각 항목을 쉼표로 구분하려면 F9셀에 아래 수식을 입력합니다. 그러면 항목 사이에 쉼표가 추가됩니다.
   =CONCATENATE(B9,",",C9,",",D9,",",E9)

   

엑셀 2013 또는 2016 버전을 사용한다면 빠른채우기 기능으로 텍스트를 더욱 손쉽게 병합할 수 있습니다.

엑셀 빠른채우기는 채우기 범위의 왼쪽에 미리 입력된 데이터의 패턴을 분석해, 그 규칙에 맞는 값을 자동으로 채워주는 기능입니다.

TEXTJOIN 함수나 CONCAT 함수처럼 실시간으로 변하는 값을 합치는 데에는 제한이 있지만, 두 함수를 사용할 수 없는 환경에서 훌륭한 대안으로 활용할 수 있습니다.

빠른채우기는 텍스트 합치기 외에도 다양한 상황에 응용할 수 있습니다. 빠른채우기의 자세한 사용법은 아래 영상강의에서 단계별로 안내해 드렸습니다.

1. [2016 이전 - 기초예제] 시트의 F15셀에 아래 값을 입력합니다. 왼쪽 범위에 미리 입력된 국가 목록인 "미국, 그리스, 덴마크, 독일"을 차례대로 입력해 주세요.

   

2. 이후 F16셀로 이동한 뒤 [홈] - [채우기] - [빠른채우기]를 클릭하거나, 단축키 Ctrl + E 를 누르면 빠른채우기가 실행되면서 텍스트 합치기가 완료됩니다.

   

실시간으로 변하는 텍스트를 자주 병합해야 하는데 엑셀 2016 이전 버전을 사용한다면 모든 엑셀 버전에서 제공되는 PHONETIC 함수를 활용할 수도 있습니다.

엑셀 PHONETIC 함수는 본래 한자/일본어의 일본어 발음(후리가나)을 표시하기 위해 만들어진 함수입니다. 이 함수는 입력된 값이 한자/일본어가 아닌 다른 문자(예: 한글, 영문, 숫자 — 숫자일 경우 앞에 반드시 어퍼스트로피('))일 때는 해당 문자를 그대로 병합해 반환하는 특성이 있습니다. 이러한 동작 원리를 응용하면 텍스트 합치기에 그대로 활용할 수 있습니다.

세로방향으로 입력된 텍스트 합치기

1. [2016 이전 - 기초예제] 시트의 C21:C24 범위에 쉼표(,)를 입력합니다.

   

2. 이후 B25셀에 아래 수식을 입력하면 세로 방향으로 입력된 텍스트가 쉼표로 구분되어 병합됩니다.
   =PHONETIC(B21:C24)

   

가로방향으로 입력된 텍스트 합치기

1. [2016 이전 - 기초예제] 시트의 D22:G22 범위에 쉼표(,)를 입력합니다.

   

2. 이후 D21:G22 범위를 복사한 뒤 E24셀을 선택합니다. 이어서 우클릭 - '선택하여 붙여넣기' - '값' - '행/열 바꿈' 순서로 붙여넣기를 실행합니다.

   

3. E28셀에 아래 수식을 입력하면 가로 방향으로 입력된 텍스트가 쉼표로 구분되어 병합됩니다.
   =PHONETIC(E24:F27)

   

이 외에도 PHONETIC 함수와 다른 기능을 함께 응용하면 여러 셀에 나누어 입력된 텍스트도 자유롭게 병합할 수 있습니다. PHONETIC 함수 실전예제에서 사용된 행마다 머릿글을 반복 출력하는 방법은 아래 영상강의에서 자세히 안내해 드렸습니다.