숫자 데이터만 추려내기2

문자열과 수식이 뒤섞여 있는 자료가 있는데, 여기서 숫자 데이터만 추출해 낼 수

없겠는가 하는 질문을 가끔 받곤 합니다(잊을만 하면 한번씩 받습니다. 해서...

잊어 먹을래야 그럴 수 없다는 장점이 있습니다. ^^)

X0107 강좌에서 VBA를 사용하여 간단히 해결하는 방법을 알려드렸는데…

VBA는 복잡하니까 수식이 좀 길어지더라도 엑셀 함수로 해결하는 방법을

알려주세요!

라고 하시는 분들이 계십니다.

<표 1> 사용자 정의 함수 예제

함수만으로 숫자 추출하기

물론 엑셀의 함수들만을 조합해도 가능은 합니다!

숫자를 추출하기 위해 사용한 사용자 정의 함수는 이러했는데… 함수만 사용하면

이것보다 간단히 해결할 수 있을 것인지 확인해 보도록 하지요.

함수만 사용한 예제

배열 수식 살펴보기

보시는 것처럼 엑셀의 여러 가지 함수를 조합해도 가능은 합니다.

위의 빨간색 셀에는 이런 수식이 들어있습니다.

{=MID(B38,MATCH(TRUE,ISNUMBER(1*MID(B38,ROW(INDIRECT("1:"&MAX(LEN($B$38:$B$46)))),1)),0),

이 수식(배열 수식)을 보니 어떤 생각이 드십니까?

VBA로 해결할 문제를 함수를 사용해서 해결을 하고 보니 거기서 쾌감을 느끼실

분도 전혀 없지는 않겠습니다만... 저는 억장이 무너져 내리는군요. ^^;

이것은 Microsoft Online 도움말에 있는 것을 일부 수정한 것입니다.

이 수식은 크게 4개의 부분으로 나누어 살펴볼 수 있습니다.

'복잡한 수식은 항상 맨 안쪽부터, 토막을 내서 살펴본다!'… 는 규칙 아시지요?

대상 영역 중에서 가장 긴 문자열이 어느 셀인지를 파악한 다음,

데이터를 개별 문자 단위로 분해합니다.

각각의 문자열이 숫자인지 아닌지 파악합니다.

수식 입력줄에서 이 부분을 범위로 지정하고 <F9> 키를 눌러보면

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}

라고 표시되는데, 분리된 문자열이 숫자이면 True, 그렇지 않으면 False로

표현된 것입니다.

문자열 중에서 숫자가 시작되는 위치를 파악합니다.

문자열 중에서 숫자가 몇 개인지 파악합니다.

이상의 결과값을 Mid 함수를 사용하여 최종적으로 조합하면 우리가 원하는

결과값이 구해지는 것입니다.

숫자가 연속적이지 않은 경우

그런데 이 수식도 약간의 문제점을 가지고 있습니다. 즉, 다음과 같이 대상 문자에

숫자가 연속적이지 않을 경우에는 제대로 숫자를 걸러내지 못합니다.

숫자가 연속적이지 않은 경우

다른 해법 살펴보기

엑사모에서 '허허허'님이 다음과 같이 해법을 주셨더군요.

엑사모에서 제시한 해법

자, 어떤 방법이 더 간단합니까?

이참에 VBA를 배워야겠다는 생각이 팍(!) 드시지요? 그렇다면 오늘 강좌의 목적 달성!!

오늘은 여기까지…

마치며

문자열 속 숫자만 추출하는 여러 가지 방법을 비교해 보았습니다. 함수만으로 해결할 수도 있지만 수식이 복잡해질 수 있으므로, 원래 목적에 맞는 방법을 선택하는 것이 중요합니다.