엑셀(Excel)로 문자열 중에 원하는 부분만 찾아서 추출하는 방법을 보겠습니다.
사용할 함수는 MID함수와 SEARCH함수입니다.
함수에 대한 자세한 설명은 생략하겠습니다.
추출하고 싶은 부분이 앞부분에 있는 경우도 있고 중간에 있는 경우도 있고 또는 마지막에 있는 경우도 있습니다.
여러 가지 경우를 살펴보도록 하겠습니다.
단, 함수를 사용하는 것이기 때문에 문자들이 어느 정도 패턴을 가지고 있어야만 가능합니다.
예를 들어 메일 주소라던가 전화번호 등등 어느 정도 구분이 되는 데이터일수록 원하는 부분을 잘라내기 쉽습니다.
앞부분 추출
문자열에서 앞부분에 있는 부분만을 잘라내고 싶은 경우를 먼저 보도록 하겠습니다.
아래와 같은 메일 주소가 있습니다.
메일 주소는 ponyo@tistory.com 처럼 일정한 규칙이 있습니다.
여기서 @앞 부분만을 잘라내는 방법을 보겠습니다.
=MID(문자열, 시작 위치, 추출할 문자수)
예제
=MID(“ponyo@tistory.com”,1,5)
결과
ponyo는 다섯 글자기 때문에 추출할 문자 수에 5를 설정했습니다.
하지만 메일 주소 글자 수는 일정하지 않기 때문에 5라고 고정으로 지정하면 원하는 부분을 잘라낼 수 없습니다.
그래서 SEARCH함수와 같이 사용을 하도록 하겠습니다.
=SEARCH(검색 문자, 문자열)
예제
=MID(“ponyo@tistory.com”, 1, SEARCH(“@”, “ponyo@tistory.com”)-1)
결과
예제 사용 수식
=MID(A1, 1, SEARCH(“@”, A1)-1)
MID함수의 추출할 문자수 부분에 SEARCH함수를 넣어줬습니다.
SEARCH함수는 문자열에서 검색 문자를 찾아 위치를 알려줍니다.
검색 문자인 @는 6번째 위치에 있기 때문에 6을 반환합니다.
잘라내고 싶은 부분은 @앞이기 때문에 SEARCH함수 반환값에 -1을 해주었습니다.
앞부분 잘라내기
=MID(문자열, 1, SEARCH(검색 문자, 문자열)-1)
뒷부분 추출
이번에는 @뒷부분을 잘라내는 방법을 보겠습니다.
예제
=MID(“ponyo@tistory.com”, SEARCH(“@”, “ponyo@tistory.com”)+1, LEN(“ponyo@tistory.com”))
결과
예제 사용 수식
=MID(A1, SEARCH(“@”, A1)+1, LEN(A1))
MID함수의 시작 위치 부분에 SERACH함수를 설정하였습니다.
이번에는 SEARCH함수에 +1을 했습니다.
추출할 문자 수에는 LEN함수를 사용했습니다.
LEN함수는 문자열의 길이를 반환해주는 함수입니다.
마지막까지 문자를 잘라내야 하기 때문에 문자열의 길이를 반환해주는 LEN함수를 설정했습니다.
뒷부분 잘라내기
=MID(문자열, SEARCH(검색 문자, 문자열)+1, LEN(문자열))
가운데 추출
이번에는 가운데 부분을 추출하는 방법을 보도록 하겠습니다.
함수들을 여러 번 사용하기 때문에 복잡해 보일 수도 있지만 하나씩 확인해보면 의외로 간단합니다.
이번에 추출할 부분은 ponyo@tistory.com 에서 tistory를 잘라내도록 하겠습니다.
예제
=MID(“ponyo@tistory.com”, SEARCH(“@”, “ponyo@tistory.com”)+1,SEARCH(“.”, “ponyo@tistory.com”)-SEARCH(“@”, “ponyo@tistory.com”)-1)
결과
예제 사용 수식
=MID(A1, SEARCH(“@”, A1)+1, SEARCH(“.”, A1)-SEARCH(“@”, A1)-1)
시작 위치부터 확인하겠습니다.
SEARCH(“@”, A1)+1
SEARCH 함수로 @찾아 시작 위치를 지정하였습니다.
SEARCH 함수는 @를 반환하기 때문에 +1을 하였습니다.
추출 문자수 부분을 보겠습니다.
SEARCH(“.”, A1)-SEARCH(“@”, A1)-1
SEARCH(“.”,A1)에서 .의 위치를 찾아냅니다.
SEARCH(“@”,A1)에서는 @의 위치를 찾아냅니다.
.의 위치에서 @의 위치를 빼줍니다.
그리고 -1을 해주면 @부터 .사이의 문자 개수를 구할 수 있습니다.
뒷부분 잘라내기
=MID(문자열, SEARCH(검색 문자, 문자열)+1, SEARCH(검색 문자, 문자열)-SEARCH(검색 문자, 문자열)-1)
정리
문자열 잘라낼 때 사용하는 MID함수와 문자열을 검색하는 SEARCH함수를 사용하여 원하는 부분의 문자만 잘라내는 방법을 알아봤습니다.
여러 개의 함수를 활용하여 수식을 만들면 조금 더 편리하고 작업에 도움이 되는 수식들을 만들 수 있습니다.
댓글