문자열 중 원문자 지우기(1) - Unicode, Substitute

1. 문제

문자열 중에서 원문자만 지우려고 합니다.

 

 

자세히 살펴보면 원문자가 문자열의 맨 뒷부분에만 있고, 

원문자가 하나뿐만 아니라 3개까지 있습니다.

 

2. 원문자의 코드 값 알아내기

B3셀에서 ①만 복사한 후 W1셀에 붙여 넣고

코드 값을 알아내기 위해 X1셀에 =code(w1)이라고 입력하면 43239가 구해지고,

Y1셀에 =unicode(w1)이라고 입력하면 9312가 구해집니다.

 

Code 함수는 윈도우에서는 ANSI 코드값을 반환하고,

 

Unicode에 대한 설명을 나무위키에서 찾아보면 

"유니코드(Unicode)는 전 세계의 모든 문자를 다루도록 설계된 표준 문자 전산 처리 방식이다. 유니코드 컨소시엄(Unicode Consortium)에서 제정, 관리한다."라고 되어 있습니다.

 

따라서, Code 값 또는 Unicode 값을 이용해서 원문자인지 여부를 알 수 있습니다.

 

엑셀에서 원문자 1 부분에 다른 원문자를 넣기 위해 X1셀의 수식 입력줄에서 fx 아이콘을 누른 후 Text 부분에 ㅇ을 입력한 후 한자 키를 누르고, Tab 키를 누르면 아래와 같이 원문자가 1부터 15까지만 표시됩니다.

 

15를 클릭하면 Text에 ⑮ 원문자 15가 들어가는데, 코드 값이 보이지 않지만 확인 버튼을 누르면

 

43253이 구해집니다.

 

그런데 입력된 데이터를 보면 15보다 큰 16, 18 등이 있습니다.

 

이번에는 원문자를 입력하지 않고,

43253에 5를 더한 숫자 43258에 대한 문자를 Char 함수를 이용해 구해보겠습니다.

W2셀에 43258이라고 입력한 다음 X2셀에 =char(w2)라고 하면 원문자 20이 아니라 3/4가 구해집니다.

 

그러나 Unichar를 이용해 9331에 대한 문자를 구해보면 원문자 20이 구해집니다.

 

이를 통해 Unicode가 더 많은 문자를 표시할 수 있다는 것을 알 수 있고,

엑셀은 ANSI 코드를 이용해 문자를 표시하고 있다는 것도 알 수 있습니다.

 

3. 해법 1

Unicode가 9312에서 9331 사이인 경우는 원문자이므로

Substitite 함수를 이용해 원문자라면 공백으로 바꾸면 됩니다.

 

가. 맨 오른쪽 원문자 지우기

B3셀의 원문자를 지워보겠습니다.

 

맨 오른쪽에 있으므로 Right 함수를 이용해서 문자 하나를 가져와서

원문자라면 Substitute 함수를 이용해서 원문자를 공백으로 대체하면 되고,

아니라면 문자열을 그대로 유지하면 됩니다.

 

따라서, 수식은

=IF(AND(UNICODE(RIGHT(B3,1))>=9312, UNICODE(RIGHT(B3,1))<=9331), SUBSTITUTE(B3, RIGHT(B3,1), ""), B3)

가 됩니다.

 

 

그런데 문제는 원문자가 1개뿐만 아니라 3개까지 있다는 것입니다.

 

나. 오른쪽에서 두 번째 원문자 지우기

오른쪽에서 두 번째 문자는 위에서 맨 오른쪽 원문자를 제거했기 때문에

다시 맨 오른쪽 원문자를 공백으로 대체하면 됩니다.

 

따라서,

=IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3)

에서 B3를

첫 번째 수식인

IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3)

로 대체하면 됩니다.

 

B3를 위 수식으로 복사해서 붙여 넣는 것은 B3가 너무 많아서 위치 찾기가 어려우므로

홈 탭 - '찾기 및 선택'을 누른 후 '바꾸기'(단축키 Ctrl + H)를 눌러서 

 

B3를 첫번째 수식으로 바꾸는 것이 좋습니다.

첫 번째 수식을 복사해서 바꿀 내용에 붙여 넣고, 찾을 내용에 b3를 입력하고, 모두 바꾸기 버튼을 누릅니다.

 

그러면 "5개 항목이 바뀌었습니다."라고 합니다.

 

 

확인 버튼을 누르고, 닫기 버튼을 누릅니다.

 

수식이 장난이 아니게 복잡하네요.

 

B3셀의 수식을 원문자가 2개 있는 T2셀을 대상으로 하기 위해

B8셀의 수식을 복사한 후 T7셀에 붙여 넣습니다.

그러면 오른쪽 원문자 2개가 제거돼서 '조개된장국'으로 맞게 출력됩니다.

 

그러나 U10셀의 경우는 원문자가 3개 있는 U5셀을 대상으로 하기 때문에 여전히 원문자가 하나 남아 있습니다.

 

다. 오른쪽에서 세 번째 원문자 지우기

B3를 '나'번의 완성된 수식으로 한번 더 대체해야 합니다.

 

다시 말해

=IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3)

에서

B3를

두 번째 수식인

IF(AND(UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))>=9312,UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))<=9331),SUBSTITUTE(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1),""),IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3)) 으로 바꿔야 하므로

 

B8셀의 수식이

=IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3)

이 되도록

Ctrl + Z(실행 취소) 키를 3번 누릅니다.

 

그런 다음 Ctrl + H키를 누르고,

찾을 내용에는 B3,

바꿀 내용에는 IF(AND(UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))>=9312,UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))<=9331),SUBSTITUTE(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1),""),IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3))

를 복사해서 붙여 넣고 모두 바꾸기를 하려고 했더니,

바꿀 내용에 들어갈 내용의 길이가 초과되는지 안됩니다.

 

그렇다면 Notepad++(Visual Studio Code와 같은 다른 에디터를 써도 되는데, 메모장은 안됩니다)에서 작업을 한 후 수식을 붙여 넣겠습니다.

 

Notepad++에서 새 파일을 연 후

Ctrl + H키를 입력하고,

찾을 내용에는 b3,

바꿀 내용에는 IF(AND(UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))>=9312,UNICODE(RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1))<=9331),SUBSTITUTE(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),RIGHT(IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3),1),""),IF(AND(UNICODE(RIGHT(B3,1))>=9312,UNICODE(RIGHT(B3,1))<=9331),SUBSTITUTE(B3,RIGHT(B3,1),""),B3))를 복사해서 붙여 넣습니다.

그리고, '모두 바꾸기' 버튼을 누르면

 

아래와 같이 바꾸기가 잘 됩니다.

 

닫기 버튼을 누른 다음 Ctrl + A키를 눌러 수식 전체를 선택한 다음

Ctrl + C키를 눌러 복사하고,

B8셀에 붙여 넣습니다.

 

그러면 아래와 같이 어마 무시한 수식이 완성됐습니다.

 

이제 B8셀의 수식을 U7셀부터 U11셀까지 붙여 넣으면

 

원문자가 1개인 경우나 3개인 경우 또는 없는 경우 등 모두 제대로 표시되는 것을 알 수 있습니다.

 

다시 U11셀의 채우기 핸들을 A열까지 끌면 모든 셀의 원문자가 제거되는데

 

값이 없을 경우 #VALUE! 에러가 발생하므로

iferror함수를 이용해 에러일 때의 값을 ""로 지정합니다.

 

A7셀을 클릭한 후 

= 다음에 iferror(를 입력하고,

 

수식 맨 뒤로 이동한 다음 ,"")를 입력한 후

엔터키를 누릅니다.

 

 

그리고, A7셀의 수식을 A11셀까지 끈 다음

A11셀의 채우기 핸들을 U11셀까지 끌면 #VALUE! 값도 없어져서 완성입니다.

 

그렇지만, 너무 복잡합니다.

다음에는 좀 간단한 방법을 설명하겠습니다.

원문자삭제문의(완성).xlsx

0.01MB