엑셀 셀 내용중 문장의 공백 없애기

엑셀 함수 - 문자열 내 공백 제거 함수

엑셀 함수 문자열 내 공백 제거 함수

 

TRIM, CLEAN 및 SUBSTITUTE 함수를 조합, 사용하여 불필요한 공백 문자 제거

 

 

때로는 공백 문자(유니코드 (유니코드: 유니코드 컨소시엄에서 개발한 문자 인코딩 표준입니다. 유니코드는 각 문자를 두 바이트 이상으로 나타내므로 전세계의 거의 모든 언어를 단일 문자 집합으로 표시할 수 있습니다.)

문자 집합 값 32 및 160) 또는 인쇄되지 않는 문자(유니코드 문자 집합 값 0-31, 127, 129, 141, 143, 144 및 157)가 텍스트 값의 앞이나 뒤에 포함되거나 중간에 여러 개 포함될 수 있습니다. 이러한 문자가 포함되어 있으면 정렬, 필터링 또는 검색에서 비정상적인 결과가 나올 수 있습니다. 예를 들어 사용자가 필요하지 않은 공백 문자를 실수로 입력하거나 텍스트에 인쇄되지 않는 문자가 포함될 수 있는 외부 소스에서 텍스트 데이터를 가져올 수 있습니다. 이러한 문자는 쉽게 발견되지 않으므로 비정상적인 결과가 나오더라도 원인을 찾기가 쉽지 않을 수 있습니다.

 

TRIM, CLEAN 및 SUBSTITUTE 함수를 조합하여 사용하면 이러한 불필요한 문자를 제거할 수 있습니다.

 

TRIM 함수는 텍스트에서 단어 사이의 공백 하나씩을 제외한 모든 공백을 제거합니다.

 

CLEAN 함수는 텍스트에서 인쇄되지 않는 문자를 모두 제거합니다.

 

두 함수는 모두 ANSI 문자 집합 (ANSI 문자 집합: 키보드를 사용하여 최대 256자(0~255)를 나타낼 수 있는 Microsoft Windows의 8비트 문자 집합입니다. ASCII 문자 집합은 ANSI 집합의 하위 집합입니다.)의 일부인 7비트 ASCII에 맞게 작동하도록 설계되었습니다. 7비트 ASCII의 처음 128개 값(0에서 127까지)은 유니코드 문자 집합의 처음 128개 값과 같은 문자를 나타냅니다.

 

TRIM 함수는 텍스트에서 7비트 ASCII 공백 문자(값 32)를 잘라내도록 설계되었습니다.

유니코드 문자 집합에는 십진수 값이 160인 줄 바꿈하지 않는 공백 문자가 추가로 포함되어 있습니다.

이 문자는 일반적으로 웹 페이지에서 HTML 엔터티인  로 사용됩니다.

 

TRIM 함수 단독으로는 줄 바꿈하지 않는 공백 문자를 제거할 수 없습니다.

 

CLEAN 함수는 텍스트에서 7비트 ASCII 코드 중 인쇄되지 않는 처음 32개 문자(값 0-31)를 제거하도록 설계되었습니다.

 

유니코드 문자 집합에는 인쇄되지 않는 문자(값 127, 129, 141, 143, 144 및 157)가 추가로 포함되어 있습니다.

CLEAN 함수 단독으로는 이러한 추가 문자를 제거할 수 없습니다.

 

이 작업을 수행하려면 SUBSTITUTE 함수를 사용하여 값이 더 높은 유니코드 문자를 7비트 ASCII 문자로 대체한 후 TRIM 및 CLEAN 함수를 사용해야 합니다.

데이터 예제

BD 122

="XY"&CHAR(7)&"453"

BD    122

MN987

수식   =TRIM(A2)               

설명   문자열 "BD 122 "에서 뒤쪽 공백을 제거합니다 

결과   (BD 122)

수식   =CLEAN(A3)              

설명   ="XY"&CHAR(7)&"453"으로 만든 문자열 값에서 인쇄되지 않는 BEL 문자(ASCII 값 7)를 제거합니다

결과   (XY453)

수식   =TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32))) 

설명   

SUBSTITUTE 함수를 사용하여 줄 바꿈하지 않는 공백 문자(유니코드 값 160)를 각각 공백 문자(ASCII 값 32)로 바꾼 후 문자열 " BD   122"에서 앞쪽 공백과 중간에 포함된 여러 공백을 제거합니다(BD 112).

수식   =CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7)))

설명

SUBSTITUTE 함수를 사용하여 인쇄되지 않는 DEL 문자(ASCII 값 127)를 BEL 문자(ASCII 값 7)로 바꾼 후 문자열 "MN987"에서 BEL 문자를 제거합니다(MN987).

 

 

[substitute 함수 부연 설명]

 

substitute는 원래 문자열 치환 함수임

 

하지만 문자열을 비교하기 위해 문자열 중간에 있는 공백을 제거하기에 간단히 활용이 가능한 함수

 

SUBSTITUTE

    ▶ 설명

          지정한 문자열 내의 특정 문자열을 치환한다. 이미 입력되어 있는 문자열 중

          일부만을 치환할 때 사용하면 편리하다.

    ▶ 형식

          SUBSTITUTE(text, old_text, new_text, instance_num)

         text(문자열) : 검색 대상이 되는 문자열을 지정한다.

         old_text(검색 문자열) : 치환할 문자열을 지정한다.

          nex_text(치환 문자열) : 검색 문자열을 대신하게 될 문자열을 지정한다.

         instance_num(치환대상) : [문자열] 인수에 [검색 문자열]이 여러 개인 경우

                                  해당하는 검색 문자열이 몇 번째 문자열인지를 수치로

                                  지정한다.

 

ex1) 예를 들어 503979-01-111111

    이렇게 된 엑셀 내용을 5039790111111 이렇게 바꾸고자 할때

    =SUBSTITUTE(A1,"-","")

    => "-"를 ""(공백)으로 치환

 

ex2) 예를 들어 홍 길 동

    이렇게 된 엑셀 내용을 홍길동 이렇게 바꾸고자 할 때

    =SUBSTITUTE(A1,CHAR(32),"")

    => A1셀의 "공백문자(CHAR(32))"를 ""(공백)으로 치환

 

ex3) 예를 들어 김   철   수

       이렇게 된 엑셀 내용을 김철수 이렇게 바꾸고자 할 때 

       =SUBSTITUTE(A1,"   ","")

       =">" A1셀의 "인쇄되지 않는 문자"를 ""(공백)으로 치환

           ** 인쇄되지 않는 문자가 어떤 코드 값인지 모를 때에는 해당 공백을 그냥 복사하여 붙여 넣기 한다.

[출처] 엑셀 함수 - 문자열 내 공백 제거 함수|작성자 펭귄

[출처] 엑셀 함수 - 문자열 내 공백 제거 함수|작성자 펭귄