엑셀 텍스트 합치기, 버전별 함수 사용법 및 실전예제
엑셀 텍스트 합치기 총정리 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 텍스트 합치기 버전별 사용 예제예제파일
이번 강의에 사용된 기초 함수
엑셀 2019 이후 버전을 사용중이시라면, 엑셀 2019 이후 버전에서 기본으로 제공되는 TEXTJOIN 함수를 사용하면 여러 셀에 나누어 입력된 텍스트를 매우 간단하게 합칠 수 있습니다.
= TEXTJOIN ( 구분자, 빈칸무시여부, 범위1, [범위2], ... )만약 MS365 버전을 사용중이라면, CONCAT 함수를 사용할 수도 있습니다.
= CONCAT ( 범위1, [범위2], ... )단, CONCAT 함수는 병함되는 단어 중간에 구분자를 추가할 수 없습니다. 따라서 보편적으로 TEXTJOIN 함수가 많이 사용됩니다.
- 예제파일을 실행한 뒤, [2019이상 - TEXTJOIN&CONCAT] 시트로 이동합니다. 이후 G5셀에 아래 수식을 입력합니다.
=TEXTJOIN(",",TRUE,C5:F5)
G5셀에 TEXTJOIN 함수를 입력합니다. - 이후 수식을 아래방향으로 자동채우기 하면 각 직원별 해외주재원 희망 국가가 쉼표로 병합됩니다.
수식을 아래로 자동채우기 하면 희망 근무지 병합이 완료됩니다.
& 기호는 서로 다른 두 문자열을 결합할 때 사용되는 기호입니다. 엑셀에서 사용되는 모든 연산자 종류와 연산자의 자세한 사용방법은 아래 포스트에서 확인하실 수 있습니다.
- 예제파일의 두번째 시트인 [2016이전 - 기초예제] 시트로 이동합니다. 이후 F5셀에 아래 수식을 입력합니다.
=B3&C3&D3&E3
- 수식을 아래로 자동채우기하면 텍스트 합치기가 완료됩니다.
- 항목 사이에 쉼표(,)를 추가해야 될 경우, F5셀에 아래 수식을 입력하면 항목 사이에 쉼표를 추가할 수 있습니다.
=B3&","&C3&","&D3&","&E3
CONCATENATE 함수 사용법
모든 엑셀 버전에서 제공되는 CONCATENATE 함수를 사용할 수도 있습니다. & 기호를 사용하는 것 보다는 편리하지만, 함수의 인수로 범위를 입력할 수 없기 때문에, 각각의 셀을 나눠서 입력해줘야 한다는 단점이 있습니다.
= CONCATENATE ( 셀1, [셀2], [셀3], ... )- [2016 이전 - 기초예제] 시트의 F9셀에 아래 수식을 입력합니다.
=CONCATENATE(B9,C9,D9,E9)
F9 셀에 CONCATENATE 함수를 입력합니다. - 이후 수식을 아래로 자동채우기 하면 텍스트 합치기가 완료됩니다.
수식을 아래로 자동채우기 하면 텍스트 합치기가 완료됩니다. - 각 항목을 쉼표로 구분할 경우, F9셀에 아래 수식을 입력하면 항목 사이에 쉼표를 추가할 수 있습니다.
=CONCATENATE(B9,",",C9,",",D9,",",E9)
중간에 쉼표를 추가할 수도 있습니다.
엑셀 2013 또는 2016 버전을 사용중이라면 빠른채우기를 사용하여 보다 손쉽게 텍스트를 병합할 수 있습니다.
엑셀 빠른채우기 기능은 채우기 할 범위의 왼쪽방향으로 미리 입력된 데이터의 패턴을 분석하여 값을 자동으로 채워주는 기능입니다.
TEXTJOIN 함수나 CONCAT 함수 처럼 실시간으로 변하는 값을 합치는 데에는 제한이 있지만, 두 함수를 사용할 수 없는 환경에서 훌륭한 대안책으로 사용할 수 있는 기능입니다.
이 외에도 빠른채우기는 다양한 상황에서 응용할 수 있는데요. 빠른채우기 기능의 다양한 사용법은 아래 영상강의에서 자세히 설명해드렸습니다.
- [2016 이전 - 기초예제] 시트의 F15 셀에 아래 값을 입력합니다. 왼쪽 범위에 미리 입력된 국가 목록인 "미국,그리스,덴마크,독일"을 차례대로 입력해주세요.
첫번째 줄에 입력된 국가를 쉼표로 구분하여 입력합니다. - 이후 F16셀로 이동한 뒤, [홈] - [채우기] - [빠른채우기]를 클릭하거나, 단축키 Ctrl + E 를 누르면 빠른채우기가 되면서 텍스트 합치기가 완료됩니다.
아래 셀로 이동한 뒤, CTRL + E 를 누르면 텍스트 합치기가 완료됩니다.
만약 실시간으로 변하는 텍스트를 자주 병합하는데, 엑셀 2016 이전 버전을 사용중이라면 엑셀 모든 버전에서 제공되는 PHONETIC 함수를 사용할 수도 있습니다.
엑셀 PHONETIC 함수는 원래 한자/일본어의 일본어 발음(후리가나)을 표시하도록 제작된 함수인데요. 이 함수는 입력된 값이 한자/일본어가 아닌 다른 문자(예: 한글, 영문, 숫자(숫자일 경우 앞에 반드시 어퍼스트로피(')를 추가)일 경우 해당 문자를 그대로 병합하여 반환하는 성질이 있습니다. 따라서 PHONETIC 함수의 이러한 동작원리를 응용하여 텍스트 합치기에 사용할 수 있습니다.
세로방향으로 입력된 텍스트 합치기
- [2016 이전 - 기초예제] 시트 C21:C24 범위에 쉼표(,)를 입력합니다.
C21:C24 범위에 쉼표를 입력합니다. - 이후 B25셀에 아래 수식을 입력하면 세로 방향으로 입력된 텍스트가 쉼표로 구분되어 병합됩니다.
=PHONETIC(B21:C24)
PHONETIC 함수를 사용하여 범위 안의 텍스트를 손쉽게 병합할 수 있습니다.
가로방향으로 입력된 텍스트 합치기
- [2016 이전 - 기초예제] 시트 D22:G22 범위에 쉼표(,)를 입력합니다.
D22:G22 범위에 쉼표를 입력합니다. - 이후 D21:G22 범위를 복사한 뒤, E24셀을 선택합니다. 이후 우클릭하여 '선택하여 붙여넣기' - '값' - '행/열바꿈'으로 붙여넣기 합니다.
선택하여 붙여넣기 - 값 - 행열바꿈으로 붙여넣기 합니다. - E28셀에 아래 수식을 입력하면 가로 방향으로 입력된 텍스트가 쉼표로 구분되어 병합됩니다.
=PHONETIC(E24:F27)
PHONETIC 함수를 사용하여 붙여넣기 된 텍스트를 병합합니다.
이 외에도 PHONETIC 함수와 함께 다양한 기능을 응용하면 여러셀에 나누어 입력된 텍스트도 병합할 수 있습니다. PHONETIC 함수 실전예제에 사용된 행마다 머릿글을 반복 출력하는 방법은 아래 영상강의에서 자세히 안내해드렸습니다.
이 외에도 텍스트 합치기 기능을 자주 사용해야 할 경우, 홈페이지에서 제공해 드리는 TEXTJOIN 함수 추가기능을 사용하면 엑셀 2016 이전 버전에서도 TEXTJOIN 함수를 엑셀 최신버전과 동일하게 사용할 수 있습니다.
엑셀 TEXTJOIN 함수 추가기능 다운로드 바로가기 - 예제파일을 실행한 뒤, [2019이상 - TEXTJOIN&CONCAT] 시트로 이동합니다. 이후 G5셀에 아래 수식을 입력합니다.
질문이 있습니다!
예를 들어 a열은 문자정보이고, b열은 숫자정보입니다.
하지만, b열은 형식이 000000에 맞추어야 합니다.
예를들어, 352번의 경우, 000352로 표기가 되게끔 셀서식을 설정하였습니다.
# 예시)
[a2] = 강개똥
[b2] = 352(실제 화면에는 000352)
[c2]에 textjoin을 하려고 할때, [c2] = textjoin(,,a2,b2)으로 입력을 하면 강개똥352로 표기가 됩니다.
이것을 강개똥000352로 표시되게 하려면 별도의 방법이 있을까요?
로 함수를 입력해보시겠어요? ^^ 바로 해결되실거에요.
감사합니다.
잘 배웠습니다.
PHONETIC 함수는 셀의 값이 '문자'일 때만 사용할 수 있습니다.
만약 입력된 값이 숫자 형식일 경우
앞에 어퍼스트로피(')를 추가하여 문자형태로 하나씩 바꾸거나
또는 범위선택 - 우클릭 - 셀 서식에서 셀의 표시형식을 텍스트로 바꾼 후 사용해보세요.
아래 관련 강의도 함께 참고해보시길 바랍니다. 감사합니다.
https://www.oppadu.com/%ec%97%91%ec%85%80-%ec%88%ab%ec%9e%90-e-%ed%91%9c%ec%8b%9c-%ed%95%b4%ea%b2%b0/
셀을 우클릭 - 셀 서식으로 이동해서 셀의 표시형식을 '일반'으로 변경한 후, 수식을 다시 입력해보시길 바랍니다.
엑셀 수식이 텍스트로 입력될 때, 클릭 한 번으로 해결하기 - 오빠두엑셀 (oppadu.com)
감사합니다.