엑셀 셀에서 영어 추출 공식
엑셀 셀에서 영어 추출 목차 바로가기
함수 공식
=TEXTJOIN("",,IFERROR(MID(셀,ISNUMBER((SEARCH(MID(셀,ROW(INDIRECT("1:"&LEN(셀))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ROW(INDIRECT("1:"&LEN(셀))),1),""))
인수 설명
| 인수 | 설명 |
| 셀 | 영어만 추출할 문장이 입력된 셀입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 셀에서 영어만 추출하기 함수 공식예제파일
호환성
운영체제 호환성 Windows 버전 엑셀 2016 이후 버전에서 사용 가능합니다. Mac 버전 Mac용 엑셀 2016 이후 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 셀에서 영어 추출 공식 알아보기
공식 설명
영어와 한글, 특수기호, 숫자 등 여러 문자가 동시에 입력된 셀에서 영어만 추출하는 공식입니다. TEXTJOIN 함수가 사용되므로 함수를 지원하는 엑셀 2016 이후 버전에서만 사용 가능합니다.
본 공식을 적절히 활용하면 셀에서 영어만 추출하는 것 외에도 ① 숫자만 추출하거나, ② 특정 문자만 추출하거나, ③ 특수기호를 제외한 값만 추출하는 등 다양한 형태로 활용할 수 있습니다.
1] 숫자만 추출하는 공식
=TEXTJOIN("",,IFERROR(MID(셀,ISNUMBER((SEARCH(MID(셀,ROW(INDIRECT("1:"&LEN(셀))),1),"0123456789")))*ROW(INDIRECT("1:"&LEN(셀))),1),""))2] 특정 문자만 추출하는 공식
=TEXTJOIN("",,IFERROR(MID(셀,ISNUMBER((SEARCH(MID(셀,ROW(INDIRECT("1:"&LEN(셀))),1),"추출할문자")))*ROW(INDIRECT("1:"&LEN(셀))),1),""))3] 특수기호를 제외한 값만 추출하는 공식
=TEXTJOIN("",,IFERROR(MID(셀,NOT(ISNUMBER((SEARCH(MID(셀,ROW(INDIRECT("1:"&LEN(셀))),1),"!@#$%^&*()_-+=[]}{';:`~\|/?.,<>"))))*ROW(INDIRECT("1:"&LEN(셀))),1),""))공식의 동작원리
- 1부터 셀의 글자수 만큼 증가하는 연속된 순번을 만듭니다.
=ROW(INDIRECT("1:"&LEN("가AB나")))
=ROW(INDIRECT("1:"&4))
=ROW("1:4")
={1,2,3,4} - MID 함수로 셀 안에 입력된 문자를 한글자씩 분리합니다.
=MID("가AB나",ROW(INDIRECT("1:"&LEN("가AB나"))),1)
=MID("가AB나",{1,2,3,4},1)
={'가","A","B","나"} - ISNUMBER & SEARCH 부분일치 함수로 각 문자가 추출하고자 하는 영어 목록에 포함되는지 확인합니다.
=ISNUMBER((SEARCH(MID("가AB나",ROW(INDIRECT("1:"&LEN("가AB나"))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))
=ISNUMBER((SEARCH({'가","A","B","나"},"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))
=ISNUMBER({#VALUE!,27,28,#VALUE})
={FALSE,TRUE,TRUE,FALSE} - 반환된 TRUE, FALSE에 각 1부터 연속되는 순번을 곱하면 영어가 입력된 순번만 계산됩니다.
=ISNUMBER((SEARCH(MID("가AB나",ROW(INDIRECT("1:"&LEN("가AB나"))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("1:"&LEN("가AB나")))
={FALSE,TRUE,TRUE,FALSE}*{1,2,3,4}
={0,2,3,0} - 마지막으로 MID, IFERROR 함수를 사용해 문자열에서 영어만 추출한 후 TEXTJOIN 함수로 병합합니다.
=TEXTJOIN("",,IFERROR(MID("가AB나",ISNUMBER((SEARCH(MID("가AB나",ROW(INDIRECT("1:"&LEN("가AB나"))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ROW(INDIRECT("1:"&LEN("가AB나"))),1),""))
=TEXTJOIN("",,IFERROR({#VALUE,"A","B",#VALUE},""))
=TEXTJOIN("",,{"","A","B",""})
="AB"
- 1부터 셀의 글자수 만큼 증가하는 연속된 순번을 만듭니다.
댓글만으로는 원인 확인이 어렵고 실제 사용하신 값이나 예제파일을 함께 올려주셔야 답변 드릴 수 있을 듯 합니다.
홈페이지 Q&A 커뮤니티를 통해 다시 글을 올려주시겠어요?^^
감사합니다.
함수에 들어가는 인수의 개수를 초과해서 입력해서 그렇습니다.
게시글에 함께 올려드린 예제파일을 참고하셔서 수식을 다시 작성해보세요.
TEXTJOIN 함수는 엑셀 2019 이후 버전에서만 제공됩니다.
따라서 엑셀 2016 이전 버전을 사용중이실경우,
#NAME? 오류가 발생합니다. :) 감사합니다.
한글만 추출하는 작업은 함수만으로는 불가능하고 VBA 코드나 정규표현식을 사용해야만 가능합니다.
자세한 내용은 아래 Q&A 커뮤니티 글을 한번 확인해보시겠어요?
https://www.oppadu.com/question/?mod=document&uid=48717
감사합니다.