[내.지.공] 엑셀 글자 수 세기, 글자 합치기, 글자 추출 등(예제포함)

EXCEL 활용2020. 8. 10. 21:38

엑셀, 셀 안의 글자를 마음대로!
글자 수, 합치기, 추출, 찾아바꾸기 등

엑셀 데이터 정리할 때 거의 모든 자료는 숫자를 기본 데이터로 하며, 자주 사용하는 함수 또한 숫자를 정리하는 데 주로 사용합니다.

하지만 숫자만큼 더 중요한 자료가 있습니다. 바로 텍스트, 글자입니다.

이번 시간은 엑셀 내 글자의 수를 구하고, 합치고, 추출하는 방법에 대해 알아보도록 하겠습니다.

아이디와 도메인의 데이터를 따로따로 가지고 있을때
완전한 이메일 주소로 만들기 위해선 가지고 있는 글자를 합쳐야 합니다.
각 셀의 글자를 합치기는 방법은 2가지가 있습니다.

CONCATENATE() 함수를 사용하는 방법

& 문자를 사용하는 방법

첫번째

CONCATENATE() 함수를 사용하는 방법은 아래와 같습니다.

=CONCATENATE(합칠 글자 셀1, 합칠 글자 셀2, 합칠 글자 셀3, ...)

CONCATENATE() 함수 괄호 안에 합할 모든 셀을 입력하면 됩니다.
만약 셀이 아닌 일반 글자를 포함시키려면 대괄호를 사용하면 됩니다.
=CONCATENATE(B2,"@",C2)

두번째

&" 문자를 사용하는 방법은 아래와 같습니다.

=합칠 글자 셀1 & 합칠 글자 셀2 & 합칠 글자 셀3 & ....

CONCATENATE() 함수와 마찬가지고 셀이 아닌 일반 글자를 포함시키려면 대괄호를 사용합니다.
=B3&"@"&C3

이번에는 이메일 주소 데이터를 가지고 있을 때
아이디만 표시하거나 도메인만 표시할 수 있는 방법, 글자 추출에 대해 알아보겠습니다.

보통 이메일 주소의 아이디는 '@'를 중심으로 왼쪽에,
도메인은 '@'를 중심으로 오른쪽에 있습니다.

이메일이 다음과 같을 때 아이디만 표시하기 위해서는 LEFT() 함수를 사용합니다.

=LEFT(이메일주소셀, 아이디 글자 수)
=LEFT(B2,7)

그리고 도메인을 표시하기 위해서는 RIGHT() 함수를 사용합니다.

=RIGHT(이메일주소셀, 도메인 글자 수)
=RIGHT(B2,11)

그럼 각각 다음과 같이 표시됩니다.

다음은 셀 안에 있는 글자의 수를 세는 방법을 알아보겠습니다.

글자수를 셀 때 사용하는 함수는 LEN() 함수 입니다.

=LEN(글자를 포함한 셀)
=LEN(B2)

아주 간단하게 구할 수 있겠죠?

이번에는 글자 안에 특정한 글자가 몇번째에 있는지 알여주는 함수 FIND()에 대해 알아보겠습니다.
주어진 데이터에 'F'라는 글자는 몇번째에 있을까요?

=FIND(특정 글자, 데이터 셀) 을 사용하면 왼쪽에서 몇번째에 위치했는지 알 수 있습니다.

=FIND("F",B2)

바로 6번째에 위치했다는 걸 금방 알수있습니다.

앞서 알아본 함수들을 통해 다음 데이터를 처리할 수 있는 방법을 찾을 수 있습니다.
이번엔 이메일이 하나만 있는 것이 아니라
다양한 아이디와 도메인을 가지고 있는 많은 데이터가 있습니다.
아이디, 도메인을 추출하기 위해 글자수를 세서
LEFT(), RIGHT() 함수를 사용하기 에는 시간도 많이 걸리고 정확하지 않을 수 있습니다.
그렇다면 다음과 같은 순서로 많은 양의 데이터를 처리할 수 있습니다.

1. FIND()함수를 활용해 "@" 문자가 왼쪽에서 몇번째 위치했는지 파악한다
(FIND("@", B2))
2. 앞서 구한 FIND() 함수로 구한 @의 순서를 LEFT()함수의 두번째 조건으로 지정한다.
(=LEFT(B2,FIND("@",B2)))
3. "@"는 표시되지 않아야 하기때문에 FIND()함수에서 1을 빼준다.
(=LEFT(B2,FIND("@",B2)-1))

위 방법을 통해 아이디를 표시하고 드래그를 통해 많은 데이터를 처리할 수 있다.

도메인을 구하는 방법은 다음과 같다.

1. 이메일 글자수에서 아이디 글자수를 뺀다
(LEN(B2)-LEN(C2))
2. 앞서 뺀 수를 RIGHT()함수의 두번째 조건으로 지정한다.
(=RIGHT(B2,LEN(B2)-LEN(C2)))
3. "@"는 표시되지 않아야 하기때문에 두번째 조건에서 1을 빼준다.
(=RIGHT(B2,LEN(B2)-LEN(C2)-1))

혹시 연습이 필요하신 분은 올려드린 엑셀 파일을 활용하시기 바랍니다.

엑셀 연습(글자).xlsx

0.01MB