엑셀을 사용하다 보면 셀에 들어있는 텍스트에서 특정 부분만을 추출해야 할 때가 있습니다. 예를 들어, 이메일 주소에서 도메인을 빼고 ID만 추출하거나, 파일 경로에서 특정 파일 이름을 가져오는 등 불규칙한 정보에서 원하는 데이터를 가져와야 하는 경우가 있죠.
이때 엑셀에서 제공하는 몇 가지 함수를 활용하면 효율적으로 원하는 텍스트를 추출할 수 있습니다.
엑셀 불규칙 텍스트 추출 함수 모음
엑셀에서 텍스트를 추출하는 기본적인 함수는 LEFT 함수, RIGHT 함수, MID 함수 입니다. 이 함수들은 각각 텍스트의 왼쪽, 오른쪽, 또는 중간에서 지정한 길이만큼 문자를 추출합니다.
- LEFT 함수 : 텍스트의 왼쪽에서부터 지정한 개수만큼 문자를 추출합니다.
=LEFT(문자열, 추출 문자 개수)
예시 : =LEFT(“나와 너의 이름은?”, 2) → 결과 : “나와” - RIGHT 함수 : 텍스트의 오른쪽에서부터 지정한 개수만큼 문자를 추출합니다.
=RIGHT(문자열, 추출 문자 개수)
예시 : =RIGHT(“나와 너의 이름은?”, 4) → 결과 : “이름은?” - MID 함수 : 텍스트의 중간에서 시작하여 지정한 길이만큼 문자를 추출합니다.
=MID(문자열, 시작 위치, 추출 문자 개수)
예시 : =MID(“나와 너의 이름은?”, 4, 7) → 결과 : “너의 이름은?”
이 함수들은 텍스트의 특정 부분을 쉽게 추출할 수 있어 유용하게 사용할 수 있습니다. 하지만 추출할 위치나 길이가 고정되지 않은 경우에는 다른 함수와 조합하여 사용해야 합니다.
이 문제를 보완할 다른 함수는 FIND 함수와 LEN 함수 그리고 SUBSTITUTE 함수가 있습니다.
- FIND 함수 : 텍스트 내에서 특정 문자가 처음으로 나타나는 위치를 반환합니다.
=FIND(찾을 문자, 문자열, [시작 위치]
예시 : =FIND(” “, “나와 너의 이름은?”) → 결과 : “3” ——- 첫 번째 ” ” 띄어쓰기를 사용한 위치인 3번째 텍스트 자리를 나타냅니다.
만약 두 번째 띄어쓰기 위치부터 시작하고 싶다면
예시 : =FIND(” “, “나와 너의 이름은?”, 4) → 결과 : “6” —– 첫 번째 “띄어쓰기 위치와 두 번째 띄어쓰기 위치 사이의 4 ~ 6으로 입력해주면 다음 띄어쓰기 위치인 6을 반환합니다.
- LEN 함수 : 텍스트의 총 길이를 반환합니다.
=LEN(문자열)
예시 : =LEN(“나와 너의 이름은?”) → 결과 : “10” ——- 띄어쓰기 및 특수 문자를 포함한 텍스트 길이를 반환합니다.
- SUBSTITUTE 함수 : 문자열에서 특정 문자를 다른 문자로 바꾸는데 사용됩니다.
=SUBSTITUTE(문자열, 기존 문자, 새로운 문자, [바꿀 위치])
예시 : =SUBSTITUTE(“나와 너의 이름은?”, ” “, “”) ———– 모든 띄어쓰기를 지워줍니다.
첫 번째 띄어쓰기만 지우려면
예시 : =SUBSTITUTE(“나와 너의 이름은?”, ” “, “”, 1) ————– 바꿀 위치에 1을 쓰면 첫 번째 띄어쓰기만 지웁니다.
엑셀 불규칙 텍스트 추출 하는 법
위 함수를 조합하면 다양한 상황에서 텍스트 추출이 필요할 수 있습니다.
아래 컴퓨터 폴더에 있는 자료의 이름을 가져와야 하는 업무에 대한 예시가 있다고 합시다.
- C:\ Projects \ 23532 \ Documents \ 공부_문서_파일1
- C:\ Projects \ 435124125 \ Videos \ 발표_동영상_파일1
- C:\ Projects \ 267347 \ 보고서_문서_파일2
“\” 특정 문자를 가지고 있고 뒤에 있는 파일명만 필요한 경우 아래와 같은 식을 작성할 수 있습니다.
=RIGHT(A1, LEN(A1) – FIND(“@”, SUBSTITUTE(A1, “\”, “@”, LEN(A1) – LEN(SUBSTITUTE(A1, “\”, “”)))))
식이 조금 복잡합니다. 설명해드리겠습니다. 설명이 어렵다면 위 식을 따라 참고해서 필요한 문자만 변경해서 쓰시면 됩니다.
- LEN(A1)
셀 A1의 문자열 길이를 반환합니다. - SUBSTITUTE(A1, “\”, “”)
이 함수는 셀 A1의 문자열에서 모든 백슬래시 “\”를 제거하고 “”를 넣어 새로운 문자열을 반환합니다. - LEN(A1) – LEN(SUBSTITUTE(A1, “\”, “”))
2번 결과를 LEN 함수로 감싸서 백슬래시가 없는 문자열의 길이를 구합니다.
그 후 1번에서 빼주면 셀 A1의 문자열 내에 “\” 개수가 됩니다.
이 개수는 마지막 “\” 위치를 찾는데 사용할 수 있습니다. - SUBSTITUTE(A1, “\”, “@”, LEN(A1) – LEN(SUBSTITUTE(A1, “\”, “”))
3번에서 찾은 마지막 “\” 위치로 마지막 “\”를 “@”로 바꿔줍니다. - FIND(“@”, SUBSTITUTE(A1, “\”, “@”, LEN(A1) – LEN(SUBSTITUTE(A1, “\”, “”)))
FIND 함수는 바뀐 문자열에서 @ 위치를 찾습니다. (마지막 “\”가 있던 위치) - RIGHT(A1, LEN(A1) – FIND(“@”, SUBSTITUTE(A1, “\”, “@”, LEN(A1) – LEN(SUBSTITUTE(A1, “\”, “”)))))
RIGHT 함수는 문자열 A1의 오른쪽에서부터 특정 길이만큼 텍스트를 추출합니다.
문자열 전체 길이에서 마지막 “\”가 있는 위치를 뺀 값을 넣습니다.
이렇게 텍스트 추출 함수를 사용해서 복잡하고 불규칙한 텍스트를 추출할 수 있습니다.