엑셀에서 여러 데이터를 다루다 보면 흩어진 데이터들을 하나의 셀에 합쳐 보여줄 필요도 생긴다.
여러 데이터를 단순히 순서대로 연결해서 합치려면 엑셀 문자 합치기를 해야한다. 원래 데이터 타입이 숫자 또는 날짜 등등.. 다양한 형태일 수 있는데 경우에 따라서는 가공을 한 후 문자 합치기를 사용해야할 수도 있다.
문자를 합치는 방법은 연산자 &를 사용하거나 concatenate라는 함수를 사용하면 된다.
어짜피 두 방법의 결과는 같으므로 선택적으로 사용해도 되지만 단순히 문자만 합칠 생각이라면 &연산자를 사용하는 것이 더 간결하다.
엑셀 문자 합치기
다음은 문자를 합치는 간단한 예제다.
="제 이름은" & B3 &"입니다." & C3 & "에 삽니다."
&연산자로 5 개의 데이터를 연결하였다.
"제 이름은"에서 이중 따옴표는 수식에서 텍스트(문자열)을 표현한다. 따라서 위 수식은
문자열 & 셀주소 & 문자열 & 셀주소 & 문자열 의 형태다. 셀 주소 내에 문자열을 참조하여 모두 연결하면 위와 같은 결과가 나오고 자동채우기를 하면 다음과 같은 결과를 볼 수 있다.
=CONCATENATE("제 이름은", B5,"입니다.",C5,"에 삽니다.") 와 같이 사용해도 동일한 결과
서식을 지정해야 하는 경우
날짜와 같이 서식이 적용된 데이터 타입은 위와 같은 방식으로 합치면 문제가 발생한다. 날짜는 원래 정수값을 가지며 여기에 서식을 적용해서 우리가 알아볼 수 있는 날짜서식( 1999-10-10, 1999년 10월 10일 등)을 가지게 되는 것이다. 그래서 단순히 날짜 데이터를 합치면 서식이 적용되기 전의 날짜데이터(정수값)으로 보여진다. 다음은 그 예제다.
43117이라는 값은 어디에서 나온 것일까? 결론을 말하면 2018년 1월 17일은 1900년 1월 1일로 부터 43117일 만큼 지난 날짜다. 이렇게 날짜 데이터는 1900년을 기준으로 지난 일 수 많큼의 크기를 갖는 데이터다. 그리고 43117과 같은 날짜 데이터에 다양한 날짜 서식을 적용하여 우리가 알아 볼 수 있는 날짜 형식으로 변환되어 보이는 것이다.
따라서 단순히 문자 합치기를 하면 원래의 날짜데이터가 보여지는 것이다. 그렇다면 서식을 적용하는 방법을 알아보자.
TEXT(B3, "yyyy-mm-dd")
TEXT 함수를 사용하면 서식을 적용하여 아예 텍스트 문자로 바꿔준다. 위 예제는 날짜 데이터에 "yyyy-mm-dd"라는 서식을 적용해서 텍스트 문자로 바꾼 결과를 연결한 결과다.
전화번호에 대해
문자 합치기보다는 TEXT 함수에 대한 예제라고 볼 수 있는데 이야기가 나온김에 하나만 더 보도록 하자.
전화번호는 보통 0으로 시작하는데 숫자 형식으로 입력하면 앞에 0이 자동으로 사라진다.(예를들어 010이 10으로 입력됨) 따라서 위와 같이 텍스트 형식으로 입력하거나 아에 서식을 텍스트로 지정하여 입력하기도 한다.
어찌 되었든 이렇게 입력된 형식의 전화번호를 문자열로 합치려고 할 때 자동으로 문자열이 순자로 변환된 후에 합쳐지는 현상이 발생할 수 있다. 다음과 같이 수식을 쓴 경우에 그렇다.
TEXT(B3, "###-####-####")
위와 같이 숫자 서식을 적용하면 B3가 숫자 데이터 타입으로 바뀌면서 앞에 0이 사라진다.
따라서 다음과 같이 서식을 적용하면 문제를 해결할 수 있다.
TEXT(B3, "0##-####-####")
전화번호가 숫자로 바뀌더라도 항상 앞에 0이 자동으로 입력된다.
이 글에서 서식에 대한 내용은 설명을 하지 않았지만 yyyy가 4자리 년도, mm이 2자리 월, dd가 2자리 일, #는 숫자를 의미한다는 것은 눈치로 짐작할 수 있을 것이다.