엑셀에서 영어만 지우기 - egsel-eseo yeong-eoman jiugi

엑셀 셀에서 영어 추출 공식

엑셀 셀에서 영어 추출 목차 바로가기

1. 사용된 공식 요약
2. 예제파일 다운로드
3. 엑셀 셀에서 영어만 추출 공식 설명
4. 공식의 동작원리

함수 공식

=TEXTJOIN("",,IFERROR(MID(셀,ISNUMBER((SEARCH(MID(셀,ROW(INDIRECT("1:"&LEN(셀))),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))*ROW(INDIRECT("1:"&LEN(셀))),1),""))

인수 설명

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

• [엑셀공식] 셀에서 영어만 추출하기 함수 공식

호환성

사용된 기초 함수

엑셀 셀에서 영어 추출 공식 알아보기

공식 설명

영어와 한글, 특수기호, 숫자 등 여러 문자가 동시에 입력된 셀에서 영어만 추출하는 공식입니다. 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"