엑셀을 다루다 보면 이메일 데이터를 가끔 접하게 됩니다. 엑셀 이메일 주소 추출하기 위해 사용자명과 도메인을 분리하겠습니다. 오늘 다루게 되는 함수 Search, Find, len, left, mid 사용 방법을 알아보고 수식을 작성하겠습니다.
1. 이메일 주소 추출하기
이메일 주소는 사용자명 + @ +도메인으로 구성되어 있습니다. 이러한 주소를 추출하기 위해서는 “@”를 기준으로 사용자명과 도메인을 분류할 수 있습니다. 이메일 주소를 추출하기 위해 사용될 함수는 아래와 같습니다.
| 함수 | 수식 형태 | 특징 |
|---|---|---|
| SEARCH | = SEARCH (찾을 문자, 문자열, 문자를 찾을 시작 위치) | – 찾을 문자 위치를 반환 – 대소문자 구분 안 됨 – 와일드 카드 사용 가능 |
| FIND | = FIND (찾을 문자, 문자열, 문자를 찾을 시작 위치) | – 찾을 문자 위치 반환 – 대소문자 구분 가능 – 와일드 카드 사용 불가 |
| LEN | = LEN(텍스트) | – 공백 포함 모든 글자수를 셀 수 있음 |
| LEFT | = LEFT(텍스트, 추출 개수) | – 지정한 문자 반환 |
| MID | =MID(텍스트, 추출 시작번호, 추출 개수) | – 시작번호, 추출개수에 맞춰 문자 반환 |
1) 사용자명 추출하기
이메일 주소의 “@”앞에 위치한 사용자명을 함수를 이용하여 추출하겠습니다. B3셀에 있는 kgkl2503@gmail.com 이메일 주소를 사용하여 수식을 작성하겠습니다. 우선 @의 위치를 찾고, 앞에 있는 사용자명을 LEFT 함수를 이용하여 불러오겠습니다. 함수를 사용하여 작성된 수식은 아래와 같습니다. 작성된 수식을 실제로 사용하시려면 이메일 주소에 해당하는 B3셀을 사용자 서식에 맞게 변경해서 사용하시기 바랍니다.
=LEFT(B3,(FIND(“@”,B3)-1))
<단계별 수식 해석>
① FIND(“@”,B3)은 이메일의 사용자명과 도메인의 경계인 “@”의 위치인 “9”를 반환합니다.
② (FIND(“@”,B3)-1)은 “@” 앞에 있는 사용자명을 불러오기 위해 글자수를 -1만큼 빼줍니다.
③ LEFT(B3,(FIND(“@”,B3)-1))은 LEFT 함수를 이용하여 B3 셀에서 8번째 글자까지 반환합니다.
2) 도메인 추출하기
이메일 주소의 “@” 뒤에 있는 도메인을 추출하겠습니다. MID 함수를 이용하여 도메인 시작 위치와 마지막 위치를 작성하고, 상황에 맞게 LEN 함수를 추가하여 수식을 작성하겠습니다. 여기서는 FIND 함수 대신 SEARCH 함수를 사용하겠습니다. 대소문자, 와일드 카드를 사용하지 않기 때문에 결과는 같습니다.
=MID(B3,(SEARCH(“@”,B3)+1),LEN(B3))
<단계별 수식 해석>
① SEARCH(“@”,B3)은 이메일의 사용자명과 도메인의 경계인 “@”의 위치인 “9”를 반환합니다.
② (SEARCH(“@”,B3)+1)은 “@” 뒤에 있는 도메인을 불러오기 위해 글자수를 +1만큼 더해줍니다. (결과는 10을 반환)
③ LEN(B3)은 MID 함수의 추출 개수를 지정하기 위한 수식
– 실제 필요한 문자 추출 개수 보다 큰 값을 입력해도 출력 가능한 문자수만 반환합니다.
④ MID(B3,(SEARCH(“@”,B3)+1),LEN(B3))은 B3셀의 “@” 뒤에 있는 문자위치 10부터 마지막 문자 위치까지 내용을 반환합니다.
