엑셀 셀에서 영어만 추출하기 :: 엑셀 함수 공식

셀에서 영어만 추출하는 함수 공식의 동작원리와 사용법을 알아봅니다.

홈페이지 » 엑셀 셀에서 영어만 추출하기 :: 엑셀 함수 공식

엑셀 셀에서 영어 추출 공식

엑셀 셀에서 영어 추출 목차 바로가기
함수 공식
=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. 1부터 셀의 글자수 만큼 증가하는 연속된 순번을 만듭니다.
    =ROW(INDIRECT("1:"&LEN("가AB나")))
    =ROW(INDIRECT("1:"&4))
    =ROW("1:4")
    ={1,2,3,4}
  2. MID 함수로 셀 안에 입력된 문자를 한글자씩 분리합니다.
    =MID("가AB나",ROW(INDIRECT("1:"&LEN("가AB나"))),1)
    =MID("가AB나",{1,2,3,4},1)
    ={'가","A","B","나"}
  3. 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}
  4. 반환된 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}
  5. 마지막으로 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"
5 1 투표
게시글평점
guest
2 댓글
Inline Feedbacks
모든 댓글 보기
러스티
러스티 (@minsky13naver-com)
2021년 11월 16일 7:51 오후
게시글평점 :
     

천재적입니다...

월마리아
월마리아 (@gvvonchannaver-com)
2021년 11월 18일 10:09 오후
게시글평점 :
     

굿굿

Last edited 12 일 전 by 월마리아
2
0
여러분의 생각을 댓글로 남겨주세요.x