ArraytoText 함수 - 배열을 문자열로 반환하는 함수

1. 의미

배열을 텍스트, 문자열의 결합으로 반환하는데, format 인수의 값에 따라 반환되는 형태가 너무 다릅니다.

2. 구문

ARRAYTOTEXT(array, [format])
 
- array : 텍스트로 변환될 배열 또는 데이터가 들어 있는 범위를 지정합니다.

- format : 대괄호 사이에 있으므로 옵션입니다.
  ① 기본값이 0으로 텍스트는 텍스트 그대로 반환하고, 숫자인 경우는 지정된 서식이 모두 해제되어 표시됩니다. 따라서, 날짜도 날짜 형식이 아니라 숫자로 표시되고, % 표시도 없어집니다. 참, 거짓은 True 또는 False로 표시되며, 오류값은 오류값 그대로 표시됩니다.

 

  ② 1은 텍스트를 큰따옴표로 감싸고, 처음과 끝을 중괄호로 감싸는 점이 다릅니다. 그러나, 숫자, 날짜, 참,거짓, 오류값의 경우는 큰따옴표로 감싸지 않는 점은 0일 때와 같습니다.

3. 적용 범위

Microsoft 365용 Excel Mac용 Microsoft 365용 Excel 웹용 Excel로 매우 제한됩니다.
 
 

4. SUM 함수에서의 배열

A1셀부터 C1셀까지 1, 2, 3을 입력한 다음 D1셀에 커서를 두고

홈 탭 > 편집 그룹 > 자동 합계 명령을 누르면 =SUM(A1:C1)이라는 수식이 자동으로 입력됩니다.

 

이 상태에서 A1:C1을 마우스로 선택한 후 F9키를 누르면

 

{1,2,3}으로 바뀝니다. 이것이 배열의 모습입니다.

 

 

5. 사용법

가. 배열 직접 입력하기

(1) ArraytoText(배열)
=ARRAYTOTEXT({"열","월"})라고,
배열은 중괄호 안에 입력하며, 문자열은 큰따옴표로 감싸야합니다.

 
(2) ArraytoText(배열,0)
=ARRAYTOTEXT({"열","월",1,TRUE,#REF!},0)
배열 입력 시 숫자와 참, 거짓값인 Boolean, 오류값 #Ref! 등은 큰따옴표 없이 입력합니다.

 
텍스트를 큰따옴표 없이 입력하면 에러가 발생하므로,

 
확인 버튼을 누르고, Esc키를 눌러 당초 수식으로 돌아갑니다.

 

또한 날짜, 쉼표 또는 % 서식을 적용해서 숫자를 입력할 수도 없습니다.
 
 (3) ArraytoText(배열,1)

위 수식에서 format을 1로 바꿔서

=ARRAYTOTEXT({"열","월",1,TRUE,#REF!},1)이라고 입력하면

 

입력한 그대로 표시되는데, 배열이 아니라 문자열이라는 점이 다릅니다.

 

A4셀에 ={1,2,3}이라고 수식으로 입력하면 A4셀부터 C4셀까지 1, 2, 3이 채워지는 배열이 됩니다.

 

나. 배열을 범위로 입력하기

 
(1) 수식을 값으로 붙여 넣은 후 텍스트 나누기
A2셀을 복사한 후 C1셀에 값으로 붙여 넣고,
데이터 탭 > 텍스트 나누기 명령을 누르면

 

텍스트 마법사 3단계 중 1단계 표시가 나오는데, '구분기호로 분리됨'을 선택하고, 다음을 누릅니다.

 
구분 기호로 쉼표를 클릭한 다음 마침을 누르면

 
쉼표를 기준으로 분리되어 C1셀부터 오른쪽으로 한 셀씩 들어갑니다.

 
(2) 배열을 범위로 지정하여 입력하기
=ARRAYTOTEXT(C1:G1)라고 배열을 범위로 지정하고, 엔터 키를 누르면 
 

 

아래와 같이 큰따옴표와 중괄호 표시없이 쉼표로 연결되어 표시됩니다.

 

다. format을 1로 지정하기

=ARRAYTOTEXT(C1:G1,1)라고, 포맷을 고급으로 지정하게 되면

 
당초 배열로 입력한 형태로 중괄호 사이에 출력되는데, 차이점은 True와 #REF!도 텍스트처럼 큰따옴표가 입력됐다는 것입니다.

 
그러나, 이것은 True와 #REF! 앞에 공백이 있어서 그런 것으로서, 공백을 제거하면 

 
위 5번 '가.배열 직접 입력하기'의 (2)에서 직접 배열로 입력할 때와 같은 결과가 나옵니다.

 
그러나, '월'은 공백을 제거해도 공백이 제거될 뿐 큰따옴표까지 없어지지는 않습니다.

 

 

라. 날짜 또는 서식이 지정된 숫자의 경우

E1셀의 데이터를 1000으로 바꾸고, 홈탭 표시형식 그룹에서 쉼표와 늘림 명령을 눌러 1,000.0으로 표시합니다. 또한 H1셀에 날짜를 추가하고, i1셀에는 1.50%를 입력합니다.

 

그리고, C3셀의 수식 중 범위 C1:H1을 C1에서 i1로 바꾸고 엔터키를 누르면

숫자인 경우 숫자 서식이 없어져서쉼표와 늘림이 지정되지 않고 입력한 숫자인 1000과 %가 아닌 숫자 0.015로 표시되고, 날짜도 날짜 형식이 아니라 1900-01-01부터의 일련 번호로 표시됩니다.

 

마. 값이 세로로 입력된 경우

아래와 같이 값이 가로와 세로로 입력되어 있는 경우

 
D8셀에 =ARRAYTOTEXT(A8:C12)라고 입력하면 큰 따옴표 없이 글자만 쉼표로 구분되어 표시되는데,

 
D9셀에
=ARRAYTOTEXT(A8:C12,1)라고 format을 1로 지정하면
글자가 큰따옴표로 감싸이고, 가로로 글자가 먼저 표시되고, 그다음 줄로 넘어가는데,
가로방향일 때는 쉼표로 구분되는데, 줄이 바뀔 때는 쉼표가 아닌 ;(세미 콜론)으로 구분되는 점도 주목해야 합니다.
 

 

5. 적용 예

가. Select Case 문에 사용하기 위한 문자열 생성

https://lsw3210.tistory.com/entry/%EB%91%90-%EB%AC%B8%EC%9E%A5%EC%9D%98-%EA%B0%99%EC%9D%80-%EB%8B%A8%EC%96%B4-%EB%B9%84%EA%B5%904-%EC%96%B4%EB%AF%B8-%EC%A1%B0%EC%82%AC-%EB%93%B1-%EC%A0%9C%EA%B1%B0-%EB%8F%84%EC%A0%84
에서
 
Select Case문을 사용하면서
조사를 입력할 때 아래와 같이 글자를 큰 따옴표로 감싸서 입력했는데, ArraytoText와 다른 점은 중괄호가 없다는 점입니다.

Case "은", "는", "이", "가", "을", "를", "의", "에", "와", "과", "한", "할", "와", ","

 
위에서 case 다음을 복사한 후 A5셀에 붙여 넣으면 한 셀씩 값이 들어갑니다. 그런데, 위와 마찬가지로 앞에 공백이 있으므로 

 
홈 탭에서 '찾기 및 선택'을 누른 다음 바꾸기 명령을 누르고,

 
찾을 내용에는 스페이스 바를 한번 밀어 공백 한 칸을 입력하고, 바꿀 내용은 그대로 두고, 모두 바꾸기 버튼을 누릅니다.

 
아래와 같이 앞의 공백이 제거된 모양으로 바뀝니다.

 
이번에는 큰따옴표를 없애기 위해 찾을 내용에 "를 입력하고, 바꿀 내용은 그대로 두고 모두 바꾸기를 누르면

 
아래와 같이 한 글자씩 입력된 모습이 됩니다. 그런데 문제는 맨 뒤에 ,(쉼표)가 사라졌으므로 ,를 입력합니다.

 
이제 ArraytoText 함수에서 format을 1로 바꿔서
=ARRAYTOTEXT(A5:N5,1)로 입력하는데, 좌우에 중괄호가 있으므로 Mid 함수를 이용해 2번째부터 문자열의 길이에서 2를 뺀 길이만큼을 가져와야 합니다.

 
그러면 위 Case문에 적용할 수 있는 문자열이 만들어집니다.

 

나. 이메일 주소를 쉼표로 묶기

 
위와 같이 이메일 주소가 입력되어 있을 경우
C15셀에 =ARRAYTOTEXT(A15:A17)라고 format 없이 입력하면 큰따옴표 없이 텍스트가 표시되므로

 
동시에 여러 사람에게 보낼 이메일 주소를 쉽게 만들 수 있습니다.

 

이 때 format을 1로 지정하면 큰따옴표와 중괄호가 들어가므로 안됩니다.

지메일을 연 다음 받는 사람에 붙여 넣으면 아래와 같이 이메일 주소가 구분되어 입력됩니다.

ArraytoText 사용예.xlsx

0.01MB