회사에서 데이터를 정리하다보면, 꼭 이런 난감한 상황을 마주하게 됩니다.
"전기포트32000, 고구마4800, 키친타월8900..."
이렇게 숫자와 문자가 뒤섞여 있으면 합계를 구하기가 번거롭고, 실수도 발생하기 쉽습니다.
이제 엑셀에 새롭게 추가된 REGEX(정규식) 함수를 활용하면, 문장 속 숫자 추출 + 합계 계산을 단 몇 초면 해결할 수 있습니다.
- 먼저 다음과 같이 숫자와 문자가 섞여있는 데이터에서 숫자만 추출해보겠습니다. 예제파일의 [예제1] 시트에서 숫자만 추출할 B1셀을 선택하고 REGEXEXTRACT 함수를 입력합니다.
전기포트32000, 고구마4800, 키친타월8900
숫자를 추출할 셀에 REGEXEXTRACT 함수를 입력합니다.
- 첫번째 인수는 숫자를 추출할 셀을 선택하고, 두번째와 세번째 인수는 각각 "\d+", 1 을 입력합니다. 여기서 "\d+"는 1자리 이상의 숫자를 추출하고, 1은 문장에서 패턴과 일치하는 모든 결과를 배열로 반환합니다. 정규식에서 \d 는 '숫자'를 의미하며, \D(대문자)로 입력하면 숫자가 아닌 데이터를 추출하는 것을 주의합니다.
=REGEXEXTRACT(A2,"\d+",1)
문장에서 숫자만 추출하는 정규식 함수를 작성합니다.
오빠두Tip : 정규식의 더 자세한 설명은 아래 기초 입문 영상 강의에서 알기 쉽게 정리했으니 아래 링크를 참고하세요!👇
- 함수를 실행하면 문장 속 숫자가 한 번에 추출됩니다. 다만 추출된 값은 '문자' 데이터이므로, 합계를 구하려면 '숫자' 데이터로 변환해야 합니다.
숫자가 잘 추출되었지만, 문자 형식으로 추출됩니다.
- 방금 작성한 REGEX 함수 뒤에 1을 곱한 후, 실행하면 값이 오른쪽으로 정렬되면서 숫자 데이터로 변환됩니다.
기존 함수에 1을 곱해 문자를 숫자로 변경합니다.
- 이제 REGEX 함수를 SUM 함수로 묶어서 실행한 후, 수식을 자동채우기하면 문자와 숫자가 섞여있는 데이터의 숫자 합계가 한 번에 계산됩니다.
SUM 함수로 묶어 숫자의 합계를 구한 후, 수식을 자동채우기합니다.
- 만약 숫자에 회계점이나 소수점이 있으면 "\d+" 패턴으로는 숫자가 잘못 추출되므로, 정규식 패턴을 변경합니다.
숫자에 회계점이나 소수점이 있을 경우, 정규식 패턴을 수정합니다.
- 기존 REGEXEXTRACT 함수의 정규식을 다음 패턴으로 수정합니다.
\d{1,3}(,\d{3})*(\.\d+)?
회계점이나 소수점을 포함한 숫자를 추출하는 정규식으로 수정합니다.
- 이제 함수를 실행하면 회계점(쉼표)와 소수점이 포함된 숫자가 올바르게 추출됩니다. 나머지는 이전과 동일한 과정으로 숫자의 합계를 구할 수 있습니다.
회계점과 소수점을 포함한 올바른 숫자가 추출됩니다.
- 단, REGEX(정규식) 함수는 현재 M365 버전에서만 제공됩니다. 따라서 엑셀 이전 버전 사용자는 웹 버전 엑셀에서 REGEX 함수를 사용합니다. Ctrl + Win + Alt + Shift 키를 동시에 눌러서 오피스 앱(또는 코파일럿)을 실행 후, 웹버전 엑셀을 열고 REGEX 함수를 입력해 PC와 동일하게 사용할 수 있습니다.
M365 이전 버전 사용자는 웹버전 엑셀에서 REGEX 함수를 동일하게 사용할 수 있습니다.
