CONCATENATE 사용하지 마세요! 엑셀 문자열 합치는 함수(CONCATENATE, CONCAT, TEXTJOIN, &(앰퍼샌드))

문자열을 결합하는 함수에서 줄바꿈을 넣거나 포맷을 유지하는 방법, 주의할 점을 알아보겠습니다.

CONCATENATE를 사용 하면 안되는 이유, TEXTJOIN의 다른 함수들과의 차별점, 다양한 포맷을 엑셀에서 확인하는 방법도 얻어가시기 바랍니다.

 

목차

1. 문자열 합치는 함수
  1-1. CONCATENATE
  1-2. CONCAT
  1-3. TEXTJOIN
  1-4. &(앰퍼샌드)
2. 문자열 함수에서 줄 바꿈을 하고 싶을 때
3. 날짜가 이상하게 나오는 경우
4. 원하는 포맷 만들기

 

 

 

 

---

1. 문자열 합치는 함수

1-1. CONCATENATE

CONCATENATE(text1, [text2], ...)

text1 (필수): 연결하고자 하는 문자 또는 문자열입니다.
text2 (선택): text1과 연결하고자 하는 문자 또는 문자열입니다. 최대 255개 항목, 총 8,192자까지 가능합니다.

CONCATENATE는 엑셀 상위버전과 호환이 안될 수 있으므로 사용을 권장하지 않고 CONCAT이나 &을 사용해야 합니다.

 

CONCATENATE - 사용 중지

 

엑셀에서 함수 추천의 아이콘이 다른 것을 볼 수 있습니다.

그리고 CONCATENATE 함수를 이용하여 문자열을 합쳤는데 중간에 큰따옴표(")가 들어가는 경우가 있습니다.

 

CONCATENATE - 따옴표

 

이런 경우는 텍스트 사이에 쉼표(,)가 빠졌기 때문입니다.

 

 

1-2. CONCAT

CONCAT(text1, [text2],…)

text1 (필수): 연결하고자 하는 문자 또는 문자열, 셀이나 배열입니다.
text2 (선택): text1과 연결하고자 하는 문자 또는 문자열입니다. 최대 253개의 텍스트 인수가 있을 수 있습니다.

 

CONCAT 사용법

 

문자열을 합치는 간단한 함수라서 특이한 점은 없지만 결과 문자열이 32767자를 초과하면 #VALUE! 오류가 발생합니다.

예시와 같이 문자열 사이에 공백을 넣으려면 " "이런 식의 공백을 단어마다 넣어주어야 하는 불편함이 있습니다.

이런 경우 간편하게 TEXTJOIN을 사용할 수 있습니다.

 

 

1-3. TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

delimiter (필수): 문자, 문자열을 합치는 경우 그 사이에 입력될 문자입니다.
ignore_empty (필수): TRUE인 경우 여러 셀을 합칠 때 빈 셀을 무시합니다. FALSE는 빈 셀을 만나도 delimiter를 추가합니다.
text1 (필수): 연결하고자 하는 문자 또는 문자열, 셀이나 배열이지만 TEXTJOIN에서는 주로 셀이나 배열입니다.
text2 (선택): text1을 포함하여 텍스트 항목에 대해 최대 252개의 인수가 가능합니다.

 

설명만 보시면 delimiter가 어떤 역할인지 감이 안 오실 수 있습니다.

예시를 보시면 한 번에 아실 수 있습니다.

 

TEXTJOIN 사용법

 

D1:D4의 내용을 합치는 역할을 하는데 셀 값 중간에 공백이나 쉼표(,)가 들어가는데 이런 문자를 delimiter라고 합니다.

그리고 F3의 식을 보시면 ignore_empty가 false일 때 어떤 변화가 있는지 알 수 있습니다.

D3이 비어 있어도 delimiter를 한 번 더 입력합니다.

이와 반대로 한 셀에 쉼표 또는 공백으로 입력되어 있는 내용을 셀별로 나누려면 텍스트 나누기를 이용하셔야 합니다.

엑셀 문자열 데이터 구분 기호로 나누기 (tistory.com)

 

 

1-4. &(앰퍼샌드)

&(앰퍼샌드): 두 값을 연결하거나 연결하여 하나의 연속 텍스트 값을 생성합니다.

 

엑셀 앰퍼샌드 사용법

 

&(앰퍼샌드)는 함수는 아니고 연산자로 이 중에서 가장 간단히 쓸 수 있지만 내용이 변경되는 경우 취약하고 알아보기 어려운 단점이 있습니다.

 

 

 

 

---

2. 문자열 함수에서 줄 바꿈을 하고 싶을 때

 

문자열을 합칠 때 줄을 바꾸고 싶다면 CHAR(10)을 넣어주면 됩니다.

 

문자열 함수에서 줄 바꿈을 하고 싶을 때

 

각 셀에는 다음과 같은 수식이 입력되어 있습니다.

E2: =CONCATENATE(D2,CHAR(10),D3,CHAR(10),D4)
F2: =CONCAT(D2,CHAR(10),D3,CHAR(10),D4)
G2: =TEXTJOIN(CHAR(10),TRUE,D2:D4)

 

만약 CHAR(10)을 입력해도 줄 바꿈이 되지 않는다면 [텍스트 줄 바꿈]을 활성화하시기 바랍니다.

 

엑셀 줄바꿈 옵션

 

 

 

---

3. 날짜가 이상하게 나오는 경우

 

날짜가 이상하게 나오는 경우 - 예시

 

문자열을 합칠 때 텍스트를 합치는 함수를 사용하면 날짜가 이상하게 나오는 경우가 있습니다.

 

날짜가 이상하게 나오는 경우 - 정상

 

날짜를 우리가 원하는 문자 형식으로 변환해 주어야 합니다.

=CONCAT(TEXT(A1,"YYYY-MM-DD")," ~ ",TEXT(A2,"YYYY-MM-DD"))

 

 

 

---

4. 원하는 포맷 만들기

 

원하는 포맷 만들기 - 예시

 

날짜에서와 마찬가지로 TEXT함수를 이용합니다.

 

원하는 포맷 만들기 - 정상

 

=CONCAT(TEXT(A1,"0.0%")," ~ ",TEXT(A2,"0.0%"))

 

이렇게 텍스트 형식을 이용하면 다양한 포맷에 대응할 수 있습니다.

=TEXT(5634,"0000000") → 0005634
=TEXT(4.34 ,"# ?/?") → 3 1/8

위와 같이 다양한 포맷이 가능하며 포맷에 대한 것은 셀 서식에서 확인할 수 있습니다.

 

원하는 포맷 만들기 - 포맷 확인

 

1. 셀에서 우클릭 → [셀 서식]을 클릭합니다.

2. [표시 형식]을 클릭합니다.

3. [사용자 지정]을 클릭합니다.

4. 원하는 포맷을 찾습니다.