[스프레드시트] 문자열 중에서 데이터에 해당하는 문자 추출하기 (RegExExtract)

데이터 수집을 하다보면 정제되지 않은 데이터가 많고, 이를 통계를 내기 위해서는 데이터를 분리해야하는 상황이 있습니다

 

예를들면 자동차 판매량 데이터를 보면, 제조사와 모델명이 따로 구분되지 않고 같이 적혀 있는 경우가 있습니다

이런 경우 단순히 공백을 기준으로 나누기도 어려운게, 두 단어 이상으로 된 이름도 있기 때문입니다

 

아래와 같이 데이터 중에는 단순한 공백 분리 같은 문자열 처리로 어려운 상황이지만

제조사나 모델이라는 정해진 데이터가 있는 경우 RegExExtract() 함수를 이용해 처리할 수 있습니다

 

데이터 문자열을 각각 따로 분류

데이터		제조사	모델명
Acura ZDX	→	Acura	ZDX
Alfa Romeo Tonale	→	Alfa Romeo	Tonale

 

 

우선 [제조사 목록] 데이터를 따로 만듭니다

이 데이터를 기반으로 문자열을 추출하게 됩니다

	A
1	제조사 목록
2	Acura
3	Alfa Romeo
4	Audi
5	BMW
6	...생략...

 

 

제조사 목록을 JOIN() 함수를 사용해 정규표현식 문자열로 만듭니다

정규표현식에서 "|" 표현은 OR 를 의미합니다

=JOIN("|", '제조사 목록'!$A$2:$A$10)

// 결과
// Acura|Alfa Romeo|Audi|BMW|...생략...

 

 

RegExExtract() 함수로 맞는 문자열을 추출합니다

=REGEXEXTRACT($A2, JOIN("|", '제조사 목록'!$A$2:$A$10))

// 결과
// Alfa Romeo

주의할 점
RegEx에서 "|" 사용시 앞에 있는 항목부터 검사하기 때문에 순서에 따라 검색되지 않는 경우가 있습니다

예를들어, 아래와 같이 뒤에 나오는 문자의 일부 문자가 앞에 있는 경우
Apple이 앞에 있어서 Apple만 추출됩니다
=RegExExtract("AppleMango", "Apple|AppleMango")
// 결과 : Apple

이런 문제를 피하려면 순서를 바꾸면 됩니다
=RegExExtract("AppleMango", "AppleMango|Apple")
// 결과 : AppleMango

 

 

결과

	A	B	C
1	모델	수식	결과
2	Acura ZDX	=REGEXEXTRACT($A2, JOIN("|", $A$2:$A$3))	Acura
3	Alfa Romeo Tonale	=REGEXEXTRACT($A3, JOIN("|", $A$2:$A$3))	Alfa Romeo

 

 

스프레드시트를 사용한다면 정규표현식은 꼭 배워두는 걸 추천합니다