엑셀 앞에서 몇번째 자르기? - egsel ap-eseo myeochbeonjjae jaleugi?

[엑셀]수식으로 처음/마지막 N 개의 문자 제거하기

오늘은 새롭게 배운것을 잊지 않기 위해 기록형으로 포스팅 합니다.

내가 하고 싶었던것!!

아래와 같이 도메인 앞에 쩜(.)이 붙어 있었는데 쩜만 지워고 싶은데... 노가다로 하자니 이건 아닌것 같아 찾아보니

아래와 같은 수식으로 간단하게 가능하네요.

수식으로 처음 N 개의 문자 제거

=RIGHT(A1,LEN(A1)-1)

위 빨간색 숫자 1을 2로 변경하면 앞에서 2개 문자를 제거 합니다.

수식으로 마지막 N 문자 제거

=LEFT(A1,LEN(A1)-1)

반대로 마지막에 붙은 것을 제거 하는 것은 위와 같습니다.

배움에 끝은 없나 봅니다.

내가 필요한 것은 한번 배워두면 오래 갑니다. 

이렇게 포스팅해두면 언제라도 찾아 볼수 있어서 좋습니다.

엑셀에서 특정문자 제거방법?

=substitute(A1,".co.kr","")

위와 같이 하면 .co.kr 을 제거 하게 됩니다.

.buza.co.kr로 예를 들어 보겠습니다.

아래와 같이 2번 수식을 넣어 주면 .buza.co.kr => buza 라는 단어만 남는 결과가 나옵니다.

1) 앞 1문자 제거 

=RIGHT(A1,LEN(A1)-1)

결과: buza.co.kr

2) 특정문자 제거

=substitute(A1,".co.kr","")

결과: buza

[Excel] 텍스트 자르기(LEFT, RIGHT, MID)

엑셀에서는 텍스트를 기준으로 문자열을 자를 수 있습니다.

사용방법은 좀 다르지만 DB 쿼리 사용방법과도 유사한 거 같습니다.

1. LEFT

 문자열의 왼쪽부터 텍스트를 잘라 냅니다.

LEFT("셀 or 문자", 잘라낼 글자 수) 

이렇게 사용하시면 됩니다.

그럼 예시를 살펴보겠습니다.

▼ 서울시강남구청담동을 =LEFT(B2,3)을 했을 시 왼쪽에서 3번째까지 잘라내니 서울시라는 결과가 나옵니다.

2. RIGHT

LEFT와 비슷하게 이번에는 오른쪽에서부터 텍스트를 잘라냅니다.

RIGHT("셀 or 문자", 잘라낼 글자 수) 

▼ 서울시강남구청담동을 =RIGHT(B2,3)을 했을 시 오른쪽에서 3번째까지 잘라내니 청담동 라는 결과가 나옵니다.

3. MID

문자의 중간부터 텍스트를 잘라냅니다. 단, 중간이 어디인지 모르기 때문에 몇 번째부터 잘라낼 것인지 적어주어야 합니다.

MID("셀 or 문자", 시작할 글자순서, 잘라낼 글자 수) 

▼ 서울시강남구청담동을 =MID(B2,4,6)에 대해서 이야기하자면 B2셀(서울시 강남구 청담동)에 대해서 4번째 글자(=강)부터 시작해서 4번째를 기준으로 6개의 글자를 잘라내야 하니 강남구 청담동이라는 결과가 나온 겁니다.

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

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

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

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

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

LEFT, FIND 함수 활용하기

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

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

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

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

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

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

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

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

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

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

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

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

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

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

예를 들어 FIND("@", "홍길동/")을

입력하면 "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 함수를 같이 써서 응용을 해보겠습니다. 

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

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

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

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

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

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

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

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

"홍길동/" 는 총 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 함수로 원하는 데이터 가져오기, 등급 부여하기