엑셀 텍스트만 제거 - egsel tegseuteuman jegeo

[엑셀]수식으로 처음/마지막 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

이름 같은 경우 성과 이름을 구분하기 위해 공백을 넣어서 띄우는 경우가 많습니다. 또는 인터넷에서 자료를 복사하는 경우 불필요한 데이터가 들어가서 정리할 필요가 종종 발생합니다. 이 때 사용하는 엑셀 함수가 SUBSTITUTE 나 TRIM 입니다. SUBSTITUTE 은 텍스트 문자열에서 특정 문자를 바꾸고 싶을 때 사용하는 함수로 일치하는 모든 문자를 교체합니다. 만약 특정 위치의 텍스트를 교체하고자 한다면 REPLACE 함수를 사용해야 합니다. REPLACE 사용법은 아래 포스팅을 참고하시기 바랍니다.

▼ 샘플 데이터는 "-" 가 들어가 있는 전화번호입니다. SUBSTITUTE 함수를 사용해서 "-" 를 삭제하고 숫자만 남겨보겠습니다.

▼ SUBSTITUTE 구문에 대한 설명은 아래와 같습니다. 첫 번째 인수에 작업할 텍스트를 입력합니다. 두 번째에는 첫 번째에 입력한 텍스트에서 찾아야 하는 문자를 입력합니다. 세 번째는 찾은 문자 대신에 들어갈 값을 입력합니다. 값 없이 "" 입력하면 빈 값이 들어갑니다. 마지막 인수인 instance_num 에는 전체 텍스트에서 Old_text 값을 검색했을 때 몇 번째 값부터 바꿀지 지정합니다.

SUBSTITUTE(text, old_text, new_text, [instance_num])
l  text : 필수 요소. 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트
l  old_text : 필수 요소. 바꿀 텍스트
l  new_text : 필수 요소. old_text를 대신할 텍스트
l  instance_num 선택 요소. text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다.

▼ 공백을 제거하기 위해 SUBSTITUTE 함수를 적용한 결과입니다. 전화번호에는 "-" 가 모두 제거되고 숫자만 남았습니다. 

◎ SUBSTITUTE 이용해서 공백 제거하기

▼ 다음은 이름 사이에 있는 공백을 제거해 보겠습니다. Old_text 에는 " " 쌍따움표 안에 공백으로 띄우고 새로 교체할 텍스트인 New_text 에는 공백을 없앤 쌍따움표를 "" 입력합니다. 

▼ 공백을 제거하기 위해 SUBSTITUTE 함수를 적용한 결과입니다. 공백이 하나 이상이라도 모두 제거가 되었습니다. 

▼ 이번에는 마지막 인수인 Instance_num 을 사용해서 값을 구해 보겠습니다. 지금까지 4가지 인수 중에 마지막 Instance_num 은 사용하지 않았습니다. 이것은 문자를 교체하는 차례를 지정하는 옵션으로 숫자 "2" 를 입력하면 교체할 문자의 2번째부터 바꾸라는 의미를 가지고 있습니다. 예를 들어 보겠습니다. 전화번호에서 2번째에 나오는 하이픈을 제거하기 위해 Instance_num  값으로 2를 입력할 것입니다. 

▼ 결과는 다음과 같습니다. 전화번호에서 2번째 "-" 하이픈부터 제거했습니다.  

◎ 아스키값(ASCII) 값 이용해서 변경하기

▼ SUBSTITUTE 에서 인수에 텍스트임을 알리기 위해 "" 쌍따움표로 감쌌습니다. 인수로 들어가는 값을 텍스트로 인식시키는 또 다른 방법은 아스키값을 사용하는 것입니다. "-" 의 아스키값 10진수는 45 입니다. 다른 것은 필요 없고 10진수 값만 알면 CHAR() 함수와 같이 사용해서 동일한 문자로 인식시킬 수 있습니다. 

▼ 수식은 다음과 같습니다. 두 번째 인수에 "-" 대신 CHAR(45) 를 입력해서 수식을 완성했습니다. 

▼ 결과는 다음과 같습니다. "-" 입력해서 완성한 수식의 결과값과 같다는 것을 알 수 있습니다.