CLEAN 함수 – 인쇄할 수 없는 문자 제거

엑셀 CLEAN 함수는 텍스트에 포함된 인쇄할 수 없는 문자를 제거합니다.

엑셀을 사용하다 보면 다른 사람이 만든 엑셀자료를 가져와서 쓸 때가 있는데 가끔 텍스트에 알 수 없는 문자(동그라미, 음표, 값은 있으나 보이지 않는 문자 등)가 포함되어 있는 경우가 있습니다. 이런 문자가 포함되어 있으면 수식 오류가 발생할 수 있습니다. 이 때는 CLEAN 함수를 이용해서 제거해 주면 됩니다.

단, CLEAN 함수는 아스키 코드의 0부터 31까지의 인쇄할 수 없는 문자(아래 표 참조)만 제거해줍니다. 아스키 코드 이후에 만들어진 유니코드에 포함된 인쇄할 수 없는 문자는 CLEAN 함수로 제거할 수 없으므로 SUBSTITUTE 함수로 유니코드 값을 빈값으로 대체하는 방법으로 해결해야 합니다.

 

구문(Syntax)

 

사용 예

1) 줄바꿈 제거하기

줄바꿈은 아스키 코드 10에 해당하므로 CLEAN 함수로 제거할 수 있습니다.

=CLEAN(B5) => '아름다운우리강산'
'아름다운'과 '우리강산' 사이에 포함된 줄바꿈 제거

=CLEAN(B6) => '아름다운우리강산'
'아름다운'과 '우리강산' 사이에 줄바꿈이 2번 포함되어 있음, 갯수에 관계없이 제거됨

 

2) 인쇄할 수 없는 문자 제거하기

※ 텍스트 끝에 공백이 3칸 포함되어 있습니다. 텍스트의 시작과 끝 위치를 확인하기 위해 수식에 '['와 ']'를 텍스트의 앞뒤에 붙였습니다.

="[" & CLEAN(B11) & "]" => '[24인치FHD IPS 모니터    ]'
'24인치'와 'FHD IPS 모니터' 사이에 포함된 인쇄할 수 없는 문자 제거, 공백은 제거되지 않음

="[" & TRIM(CLEAN(B12)) & "]" => '[24인치FHD IPS 모니터]'
CLEAN 함수로 인쇄할 수 없는 문자를 제거하고 TRIM 함수로 공백을 제거

 

참고

아스키테이블(0부터 31까지 인쇄할 수 없는 문자)

Decimal	Char	설명	한국어 설명
0	NUL	Null Character	널 문자
1	SOH	Start of Header	헤더 시작
2	STX	Start of Text	본문 시작, 헤더 종료
3	ETX	End of Text	본문 종료
4	EOT	End of Transmission	전송 종료, 데이터 링크 초기화
5	ENQ	Enquiry	응답 요구
6	ACK	Acknowledgment	긍정응답
7	BEL	Bell	경고음
8	BS	Backspace	백스페이스
9	HT	Horizontal Tab	수평 탭
10	LF	Line feed	개행
11	VT	Vertical Tab	수직 탭
12	FF	Form feed	다음 페이지
13	CR	Carriage return	복귀
14	SO	Shift Out	확장문자 시작
15	SI	Shift In	확장문자 종료
16	DLE	Data Link Escape	전송 제어 확장
17	DC1	Device Control 1	장치 제어 1
18	DC2	Device Control 2	장치 제어 2
19	DC3	Device Control 3	장치 제어 3
20	DC4	Device Control 4	장치 제어 4
21	NAK	Negative Acknowledgement	부정응답
22	SYN	Synchronous idle	동기
23	ETB	End of Transmission Block	전송블록 종료
24	CAN	Cancel	무시
25	EM	End of Medium	매체 종료
26	SUB	Substitute	치환
27	ESC	Escape	제어기능 추가
28	FS	File Separator	파일경계 할당
29	GS	Group Separator	레코드 그룹경계 할당
30	RS	Record Separator	레코드 경계 할당
31	US	Unit Separator	장치 경계 할당

출처 : https://ko.wikipedia.org/wiki/ASCII

 

[ 엑셀 텍스트 함수 ]

• TEXTAFTER 함수 – 구분기호 뒤의 텍스트 잘라내기
• TEXTBEFORE 함수 – 구분기호 앞의 텍스트 잘라내기
• TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기
• 엑셀에서 텍스트(문자열) 다루기
• IF 함수에서 와일드카드가 안될 때
• 텍스트에서 마지막 단어 잘라내기
• TRIM 함수 - 공백 제거하기
• CLEAN 함수 – 인쇄할 수 없는 문자 제거
• LEFT 함수 – 왼쪽부터 텍스트 자르기
• MID 함수 – 중간부터 텍스트 자르기
• RIGHT 함수 – 오른쪽부터 텍스트 자르기
• UPPER 함수 - 텍스트를 대문자로 바꾸기
• LOWER 함수 - 텍스트를 소문자로 바꾸기
• PROPER 함수 - 첫 글자를 대문자로 바꾸기
• 엑셀에서 조건을 만족하는 텍스트 합치기(병합)
• SUBSTITUTE 함수로 위치에 관계없이 텍스트 바꾸기
• REPLACE 함수로 텍스트 바꾸기
• NUMBERVALUE 함수 - 지역 설정에 영향받지 않고 숫자로 변환
• T 함수 - 텍스트가 입력된 셀만 반환하기
• UNICHAR 함수 - 코드값에 해당하는 유니코드문자 반환
• CHAR 함수 - 코드값에 해당하는 문자 반환
• UNICODE 함수 - 문자의 유니코드값 구하기
• CODE 함수 - 문자의 코드값 구하기
• FIXED 함수 - 숫자 반올림 후 텍스트로 변환
• VALUE 함수 - 텍스트를 숫자로 변환하기
• EXACT 함수 - 두 텍스트가 같은지 비교하기
• SEARCH 함수 – 대소문자 구분없이 텍스트 위치 찾기
• TEXT 함수 - 숫자,날짜를 텍스트로 표시
• REPT 함수 - 텍스트를 반복해서 표시
• CONCATENATE 함수 – 텍스트 합치기
• CONCAT 함수 – 텍스트 합치기
• TEXTJOIN 함수 – 기호로 구분하여 텍스트 합치기
• LEN 함수 – 텍스트 길이 구하기
• FIND 함수 – 텍스트의 위치 찾기