엑셀 알파벳만 추출 - egsel alpabesman chuchul

이번 글에서는 엑셀 숫자만 추출하는 방법에 대해서 정리해 보도록 하겠습니다. 엑셀을 사용할 때는 많은 함수를 사용할 수 있는데요. 이런 함수를 어떻게 쓰느냐에 따라 시간을 얼마나 효율적으로 사용할 수 있는지가 달라지게 됩니다. 예를 들어 123호, 236호 등 숫자와 문자로 이루어진 셀이 있을 경우 여기서 숫자를 추출하려 할때는 직접 셀의 내용을 보고 숫자를 따로 적어 줄 수도 있지만, 셀의 길이가 100건만 되도 손으로 적어 주는 것은 굉장히 비효율적입니다. 셀에서 일부 텍스트만 추출해주는 함수를 사용하면 이 문제를 간단하게 해결 할 수 있습니다.

목차

• SUBSTITUTE 함수로 숫자만 추출하는 방법
• LEFT, RIGHT, MID 함수로 숫자만 추출하는 방법
• 복합 함수로 셀에서 추출하는 방법

SUBSTITUTE 함수로 숫자만 추출하는 방법

SUBSTITUTE 함수는 셀의 원본값에서 일부 글자를 떼어낸 나머지 값을 반환하는 합수입니다. 위의 예를 보시면 쉽게 알 수 있는데요. 원본값에서 제외할 텍스트를 함수에 적어주면 됩니다.

=SUBSTITUTE (원본셀, "제외할 텍스트", "")

LEFT, RIGHT, MID 함수로 숫자만 추출하는 방법

LEFT, RIGHT, MID 함수는 원본셀에서 일부 텍스트만 추출하는 함수입니다. 예를 들어 LEFT 함수의 경우 앞에서 몇 글자까지 추출할지, RIGHT 함수의 경우 뒤에서 몇 글자를 추출할지 설정해 주면 해당 값만 반환하게 됩니다.

=LEFT(원본셀,왼쪽에서 몇번째까지 추출할지)
=RIGHT(원본셀, 오른쪽에서 몇번째까지 추출할지)

MID함수는 원본셀에서 몇번째 글자부터 몇개를 추출할지 선택할 수 있습니다. 예를 들어 “책392페이지”라면 MID(“책392페이지”,2,3) 을 입력하면 2번째 글자부터 3개의 글자를 추출합니다. 결과값은 392가 됩니다. (캡쳐화면에는 함수식이 잘못 들어가 있습니다.)

FIND함수를 사용하여 복합적으로 사용할 수 있습니다. 예를들어 강의실302호 라는 값에서 숫자 302만 추출하고 싶다면, FIND(“호”,원본값)-3 하면 “호”라는 글자 3번째 앞 글자를 선택하여 그 글자부터 3번째 숫자까지 추출하라라는 함수를 아래 처럼 설정할 수 있습니다.

=MID(원본셀,FIND("찾을글자",원본셀)-찾을글자앞몇번째글자,추출할숫자자리수)

만약 원본값이 날짜 형식이라면 MID 함수나 LEFT, RIGHT 함수등 텍스트를 추출하는 함수로는 원하는 결과값이 나오지 않습니다. 이럴때는 간단하게 YEAR, MONTH, DAY 함수를 이용하여 년, 월, 일을 숫자로 추출할 수 있습니다.

복합 함수로 셀에서 추출하는 방법

만약 위와 같은 방법들로 해결이 안될만큼 다양하고 불규칙적으로 숫자가 섞여있는 셀에서 숫자만 추출하기 위해서는 함수를 복합적으로 사용하면 가능합니다.

MID, ISNUMBER, LARGE, SUMPRODUCT, ROW 함수를 이용하여 만들어진 서식인데요. 1에서 50까지는 원본셀의 값이 50글자가 넘지 않는다는 가정으로 만들어진 것이므로 실행속도를 위해 범위를 줄이거나 셀의 값에 맞춰 늘릴 수 있습니다.

=SUMPRODUCT(MID(0&원본셀,LARGE(ISNUMBER(--MID(원본셀, ROW($1:$50),1))ROW($1:$50),ROW($1:$50))+1,1) 10^(ROW($1:$50)-1))

이 함수는 MID 함수로 원본셀에서 숫자를 먼저 하나 하나 분리한 다음 그것이 숫자인지 아닌지를 ISNUMBER 함수를 통해 확인합니다. 그렇게 숫자들만 추출한 다음 그걸 자리에 맞게 10승씩 곱하여 합치는 방법으로 숫자를 추출하는 것인데요. 작동원리가 궁금하다면, 함수 하나 하나를 테스트하며 공부해보시는 것도 좋을 것 같습니다.

엑셀에서 원하는 텍스트를 추출할 일이 많은데

몇 개 정도야 수작업으로 하겠지만, 

데이터 양이 많으면 수작업으로 하다보면 손가락에 쥐가 나기 일쑤죠. 

이럴 때 유용한 함수로 LEFT, RIGHT, LEN, FIND 등이 있습니다. 

각 함수별로의 사용법에 대해서 알아보겠습니다. 

LEFT, FIND 함수 활용하기

① LEFT 함수 기본 사용법 : 문자열의 왼쪽을 기준으로 원하는 글자만큼 추출

 LEFT함수는 이름 그대로 문자열의 왼쪽부터

원하는 만큼의 글자 수를 추출합니다. 

   함수 사용 : LEFT(대상, 자릿수)

   활용 예시 : LEFT(A2, 3) → "홍길동" 추출

   동일한 함수를 적용하기 위해서 끝까지 마우스로 끌어줍니다. 

② 이번에는 LEFT함수와 FIND 함수를 같이 써서 응용을 해보겠습니다. 

예를 들어 "홍길동/[email protected]"라는 문자열에서

이름(홍길동)과 아이디(a1111)를 같이 추출해 보겠습니다. 

LEFT함수는 맨 왼쪽부터 순서대로 카운팅하여 끝 지점을 입력하는 방식입니다. 

즉 우리는 이름과 아이디가 끝나는 "@" 까지의 글자 수를 입력해 주는 것이죠. 

이럴 때 사용하는 함수로 FIND 함수가 있습니다. 

FIND 함수는 FIND(찾을 문자, 대상)을 입력하는데,

"대상" 안에서 "찾을 문자"의 글자 위치를 나타내 줍니다. 

예를 들어 FIND("@", "홍길동/[email protected]")을

입력하면 "10"이라는 값이 나타나는데

"10"은 왼쪽부터 "@"문자가 있는 글자 순서이죠. 

이러한 FIND 함수의 기능을 이용해서 LEFT 함수의 글자 수를 활용하는 것입니다. 

함수 사용 : LEFT(A2,FIND("@",A2)-1)

함수 결과 : 홍길동/a1111

여기서 -1 을 입력하는데 FIND("@",A2)는 @가 있는 위치 10을 나타내기 때문에

바로 앞글자까지만 추출하기 위해 -1을 넣어주는 것입니다. 

실제 엑셀에서는 아래와 같이 입력하면 됩니다. 

동일한 함수를 적용하기 위해서 끝까지 마우스로 끌어줍니다. 

반응형

RIGHT, LEN, FIND 함수 활용하기

① RIGHT 함수 기본 사용법 : 문자열의 오른쪽을 기준으로 원하는 글자만큼 추출

  RIGHT함수는 이름 그대로 문자열의 오른쪽부터 원하는 만큼의 글자 수를 추출합니다. 

   함수 사용 : RIGHT(대상, 자릿수)

   활용 예시 : RIGHT(A2, 3) → "net" 추출

   동일한 함수를 적용하기 위해서 끝까지 마우스로 끌어줍니다. 

② 이번에도 RIGHT함수와 LEN, FIND 함수를 같이 써서 응용을 해보겠습니다. 

예를 들어 "홍길동/[email protected]"라는 문자열에서 도메인(hanmail.net)을 추출해 보겠습니다. 

RIGHT함수는 맨 오른쪽부터 순서대로 카운팅 하여 끝 지점을 입력하는 방식입니다. 

즉 우리는 도메인이 시작되는 "@" 다음 자리 글자 수를 입력해 주는 것이죠. 

이럴 때도 FIND 함수를 사용합니다. 

FIND 함수는 FIND(찾을 문자, 대상)을 입력하는데, "대상" 안에서 "찾을 문자"의 글자 위치를 나타내 줍니다. 

예를 들어 FIND("@", "홍길동/[email protected]")을 입력하면 "10"이라는 값이 나타나는데

"10"은 왼쪽부터 "@"문자가 있는 글자 위치입니다. 

우리가 구해야 할 것은 전체 글자 수에서 10을 뺀 숫자인 11번째입니다. 

"홍길동/[email protected]" 는 총 21글자이며, @까지 10번째입니다. 

마지막 글자(21번째)에서 @까지는 21-10=11번째 글자입니다. 

총 글자 수는 LEN 이란 함수를 사용합니다. 

함수 사용 : LEN(대상) 

활용 예시 : LEN(A2) → 21 추출

이러한 LEN, FIND 함수의 기능을 이용해서 RIGHT 함수에 활용해야 합니다. 

함수 사용 : RIGHT(A2,LEN(A2)-FIND("@",A2))

함수 결과 : hanmail.net

실제 엑셀에서는 아래와 같이 입력하면 됩니다. 

동일한 함수를 적용하기 위해서 끝까지 마우스로 끌어줍니다. 

도움이 되는 다른 포스팅도 참조하세요^^

2022.03.25 - [보고서 작성기술/엑셀] - 엑셀 반올림, 올림, 버림 함수 한 번에 알아보기

2022.04.06 - [보고서 작성기술/엑셀] - 엑셀 랜덤 함수 : 엑셀 랜덤 추출, 순위 표시, 당첨자 뽑기

2021.08.05 - [보고서 작성기술/엑셀] - 엑셀 원하는 텍스트 추출(LEFT, RIGHT, LEN, FIND 함수)

2021.07.29 - [보고서 작성기술/엑셀] - 엑셀 셀 합치기(CONCATENATE 함수, 기호&)

2021.07.22 - [보고서 작성기술/엑셀] - 엑셀 COUNTIF 함수, COUNTIFS 함수로 원하는 값 개수 구하기

2021.07.16 - [보고서 작성기술/엑셀] - 엑셀 INDEX함수와 MATCH 함수로 원하는 값 가져오기

2021.07.14 - [보고서 작성기술/엑셀] - 엑셀 VLOOKUP 함수로 원하는 데이터 가져오기, 등급 부여하기

반응형

공유하기

게시글 관리

저작자표시 비영리 변경금지

• 카카오스토리
• 트위터
• 페이스북

'보고서 작성기술 > 엑셀' 카테고리의 다른 글