공백의 코드 값이 여러 가지입니다.

이 글에서 텍스트로 된 수식의 값을 구하는 사용자 정의 함수를 만들었는데,
네이버 카페에서 글을 읽다 보니 텍스트 수식을 값으로 바꿔달라는 글이 있어
이 파일에 적용해 보니 안됩니다.
 
원래는 엑셀 통합문서(*.xlsx)인데
위 사용자 정의 함수를 복사해서 붙여 넣은 후
매크로 사용 통합문서(*.xlsm)으로 바꿨습니다.
 

공백대체.xlsm

0.02MB

 
 
 
그리고, B1셀에 =calc_text(A1)라고 입력하고 엔터키를 누르니
#VALUE! 에러가 발생합니다.

 
왜 그런가 하고 A1을 마우스로 선택하고 F9키를 누르니 뒤에 공백이 하나 있습니다.

 
1. 공백 없애기
가. 실패 1
=calc_text(SUBSTITUTE(A1," ",""))라고
SUBSTITUTE함수를 이용해서 공백 한 칸을 공백이 없는 것을 대체하는 수식을 추가해도
여전히 #VALUE! 에러가 발생합니다.

 
SUBSTITUTE(A1," ","")를 마우스로 끌어서 범위를 잡고

 
F9키를 누르니 뒷부분의 공백이 제거되지 않았습니다.

 
나. 불완전한 성공 1
이번에는 mid함수를 이용해 작성해 보겠습니다.
마지막에 있는 문자를 공백이 없는 것으로 대체하는 것입니다.
=calc_text(SUBSTITUTE(A1,MID(A1,7,1),""))라고 입력하니 여전히 에러가 발생하는데

 
SUBSTITUTE(A1,MID(A1,7,1),"")을 범위로 잡고 F9를 누르니 공백이 잘 제거되었습니다.

 
그렇다면 사용자 정의 함수가 문제입니다.
 
개발도구 - Visual Basic을 누른 후
에디터에서 함수 실행문에 중단점을 설정하고,

엑셀로 돌아가기 아이콘을 누른 후
B1셀의 수식 입력줄에 커서를 넣고, 엔터키를 눌러도 중단점에서 멈추지 않습니다.
 
그래서 코드를 살펴보니 text_Rng가 범위로 설정되어 있는데, SUBSTITUTE 함수를 사용해서 문자로 바뀌어서 변수 형식이 범위가 아니라서 그렇습니다. 따라서, Range를 Variant(가변형)으로 바꿉니다.

Function calc_text(text_Rng As Range)
    calc_text = Evaluate(text_Rng.Value)
End Function

 
이제 수식 입력줄에 커서를 넣고 엔터키를 누르면 중단점에 멈추는데, text_Rng의 값이 문자열 "2*20*1"로 넘어왔습니다.

 
F8키를 눌러 계속 실행하면 사용자 정의 함수가 실행을 멈추는데,
엑셀로 돌아가서 보면 여전히 #VALUE!로 표시됩니다.
 
그렇라면 조건을 달아서 범위라면(isObject)  text_Rng.Value로 하고,
아니라면(문자열이라면, 특별한 함수가 없습니다) text_Rng로 바꿉니다.
 
그러면 코드는 아래와 같습니다.

Function calc_text(text_Rng As Variant)
    If IsObject(text_Rng) Then
        calc_text = Evaluate(text_Rng.Value)
    Else
        calc_text = Evaluate(text_Rng)
    End If
End Function

 
이제 다시 B1셀의 수식 다시 실행하면
중단점에 멈추고 F5키를 눌러 끝까지 실행합니다.
그리고, F9키를 눌러 중단점을 해제하고,
엑셀로 돌아가면 40이 표시됩니다.
 
이제 B1의 채우기 핸들을 더블 클릭하면 맨 아래까지 수식이 복사되는데,
7번째부터 1칸이 공백이 아닌 10행과 15행은 에러가 발생합니다.

 
다. 실패 2
공백이 있는 위치를 찾아야 하므로,
C1셀에 =len(a1)이라고 입력하고 채우기 핸들을 더블 클릭한 다음 살펴보니
10행과 15행이 겉으로 보면 길이가 같아 보이는데, 길이가 7과 8로 다릅니다.
10행은 길이가 7이어서 공백이 없고, 15행은 8행이 공백이라서 에러가 난 것입니다.

따라서, 
=calc_text(IF(MID(A1,LEN(A1),1))=" ",SUBSTITUTE(A1,MID(A1,LEN(A1),1),""),A1))
라고 수식을 작성해서 길이로부터 한 자가 공백이면 공백을 없애도록 해도
MID(A1,LEN(A1),1))=" "가 이상하게 False라서 A1값을 반환하니 에러가 발생합니다.

 
라. 실패 3
따라서, 7번째 문자의 코드 값을 알아내서 대체하도록 수식을 바꿔보겠습니다.
=CODE(MID(A1,7,1))라고 입력하면 A1셀의 7번째부터 1글자의 코드값을 구할 수 있는데 63입니다.

 
따라서, 아래와 같이 B1셀의 수식을 변경하면 
=calc_text(SUBSTITUTE(A1,CHAR(63),"")) 코드값 63에 대한 문자를 Char함수를 이용해 구하므로 돼야 하는데 똑같이 #VALUE! 에러가 발생합니다.

 
일반적인 공백의 코드 값은 32입니다.

 
마, 성공 2
주전산기를 이용해서 만든 데이터의 경우 이런 문제가 많이 발생하는데,
Code가 아니라 Unicode 함수를 사용하고, Char대신 Unichar 함수를 사용하면 됩니다.
 
먼저 E1셀의 수식을 =UNICODE(MID(A1,7,1))로 바꿔서 7번째 공백의 유니코드 값을 구하면 63이 아니라 160입니다.

 
다시 B1셀의 수식을 =calc_text(SUBSTITUTE(A1,UNICHAR(160),""))라고 unichar함수를 이용해 유니코드 값 160에 해당하는 문자를 구한 후 공백을 제거하면 문제없이 수식 2*20*1의 결괏값 40이 값이 구해집니다.

 
B1셀의 채우기 핸들을 더블 클릭해도 모두 경우에 맞는 값이 구해집니다.

 
 

 
2. 수식이 범위인 경우
위에서는 Substitute함수를 이용해서 문자로 바꿨기 때문에
evaluate(text_Rng)가 적용되지만
 
범위인 경우에도 잘 되는지 살펴보려면
A2셀은 텍스트로 된 수식에 공백이 없으므로
D2셀에 =calc_text(A2)라고 하면
범위(isObject)이기 때문에
evalucate(text_Rng.Value)가 적용되어 1*30*1의 값 30이 정상적으로 구해집니다.

공백대체(완성).xlsm

0.02MB