[ 응용 ] 엑셀 숫자와 문자가 섞여 있을 때 숫자 계산하기, 숫자만 추출하기

가끔 엑셀에 데이터가 이상하게 입력되어 있는 경우가 있습니다.

아래 그림이 비슷한 상황에 대한 예시입니다.

 

이런 데이터가 수도 없이 많을 경우

데이터를 정제하기도 어려울뿐더러,

정제가 안되니 계산도 어렵습니다.

 

이런 데이터 중에서 숫자만 추출할 수 있는 함수식입니다.

 

반응형

 문자와 숫자로 이루어진 텍스트에서 숫자만 추출하는 함수식 

 

 

첫 번째 함수식입니다.

=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1) * 1), MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1), ""))

 

TEXTJOIN 함수가 엑셀 어떤 버전부터 지원하는지 정확하지는 않으나

대략 2016 버전 이후부터인 것으로 알고 있습니다.

그 이후 버전에서는 사용이 가능할 것입니다.

 

두 번째 함수식입니다.

=TEXTJOIN("", TRUE, FILTER(MID(B2, SEQUENCE(LEN(B2)), 1), ISNUMBER(--MID(B2, SEQUENCE(LEN(B2)), 1))))

 

SEQUENCE 함수와 FILTER 함수는

2021 버전 이후부터 지원하는 함수로 알고 있습니다.

 

주의하셔야 할 점은

이 함수는 배열을 사용하므로 

함수식을 입력하신 다음 CTRL + SHIFT +ENTER를 누르셔야 합니다.

 

이 두 수식을 이해하기 위해서는

배열 수식도 알아야 하고

특히 첫 번째 수식의 경우

INDIRECT 함수의 참조 기능을 이해해야 합니다.

 

두 번째 수식의 경우 --라는 것도 사용이 되었습니다.

 

이 함수식을 사용하면 다음과 같이 숫자만 추출할 수 있습니다.

 

728x90

 마치며

 

이 함수식은 엑셀을 조금은 깊이 알아야 이해할 수 있는 수식이며,

필요할 때마다 만들어 쓰기도 쉽지는 않은 수식입니다.

 

개인적으로는 이런 수식들을 이해하거나,

필요할 때 만들어 사용하는 것보다는

복사해 놓고 필요할 때 쓰거나,

차라리 VBA로 사용자 지정함수를 만들어서 사용하는 것이

오히려 낫다고 생각합니다.

 

하지만 이 수식을 이해하면

다른 상황에서 더 유용하게 사용할 수 있기는 합니다.

 

이 내용에 대해 작성을 해보려 했으나,

막상 글로 작성해 보니 글로 설명하는 것이 어렵네요..

아직 완전히 이해한 것이 아닌가 봅니다.

 

다만 조만간 VBA로 이와 같은 기능을 하는 

사용자 지정함수를 만드는 방법을 올려보도록 하겠습니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기