복잡한 데이터 분할 및 정리하기

가져온 데이터는 좀처럼 깔끔하지 않습니다. 실제로는 세 개의 열로 나누고 싶은 John,Smith,Sales가 하나의 셀에 들어 있을 수 있습니다.

Google 스프레드시트의 SPLIT 함수는 구분 기호에서 문자열을 나누고, 각 부분을 인접한 셀에 펼쳐 놓습니다.

이 마지막 단원에서는 방금 배운 정규식 기술을 SPLIT 및 CLEAN과 함께 사용하여 정리되지 않은 텍스트를 완전히 정돈해 보겠습니다.

SPLIT(text, delimiter)는 구분 기호를 발견할 때마다 텍스트를 나누고, 각 부분을 오른쪽에 있는 별도의 셀에 출력합니다.

아래 수식은 쉼표를 기준으로 A2를 나눕니다. John,Smith,Sales는 John, Smith, Sales라는 세 개의 셀로 펼쳐집니다.

기본적으로 구분 기호 문자열의 각 문자는 별도의 구분자로 처리됩니다. 따라서 SPLIT(A2, ",;")는 쉼표와 세미콜론을 기준으로 나눕니다.

아래 수식은 쉼표 또는 공백을 기준으로 나누므로, 행마다 구분 기호가 일정하지 않을 때 유용합니다.

SPLIT에는 선택적 인수가 있습니다. 세 번째 인수는 각 구분 기호 문자를 따로 나눌지 결정하고, 네 번째 인수는 빈 결과를 제거할지 결정합니다.

기본적으로 빈 부분은 삭제되므로 열 위치가 밀릴 수 있습니다. 아래 수식은 마지막 인수로 FALSE를 전달하여 빈 부분을 유지하고, A,,C의 열 정렬을 보존합니다.

PDF나 웹 페이지에서 붙여넣은 텍스트에는 수식과 조회를 방해하는 보이지 않는 제어 문자가 포함되는 경우가 많습니다.

CLEAN 함수는 문자열에서 대부분의 인쇄할 수 없는 문자를 제거합니다. 아래 수식은 이러한 이상 문자를 제거한 A2를 반환하므로, 같아 보이지만 일치하지 않던 값도 이제 제대로 작동합니다.

CLEAN은 제어 문자를 처리하지만 여분의 공백은 처리하지 않습니다. 앞뒤 공백과 반복되는 공백을 제거하는 TRIM과 함께 사용한 다음 나누세요.

아래 수식은 하나의 표현식에서 제어 문자를 정리하고, 공백을 다듬고, 결과를 쉼표 기준으로 나눕니다. 안쪽부터 읽어 보세요.

A열에 불필요한 공백이 포함된 Smith , John 과 같은 지저분한 항목이 있다고 가정해 보세요. 정리된 이름과 성을 별도의 셀에 넣으려고 합니다.

아래 수식은 공백을 다듬은 다음 쉼표를 기준으로 나눕니다. 각 부분도 정리하려면 나눈 뒤 개별 결과를 TRIM으로 감쌀 수 있지만, 대개 먼저 정리하는 것으로 충분합니다.

SPLIT에는 문자 그대로의 구분 기호가 필요합니다. 구분 기호가 다양하면 먼저 REGEXREPLACE로 통일한 다음 표준 구분 기호를 기준으로 나누세요.

아래 수식은 공백, 쉼표 또는 세미콜론이 연속된 부분을 파이프 하나로 바꾼 다음 파이프를 기준으로 나눕니다. 이렇게 하면 입력 형식이 매우 불규칙해도 깔끔하게 처리할 수 있습니다.

때로는 셀의 일부만 가져온 다음 그것을 나누고 싶을 수 있습니다. Tags: red, blue, green에서 색상만 별도의 셀에 넣으려고 하는 경우입니다.

아래 수식은 먼저 콜론 뒤의 모든 내용을 추출한 다음 쉼표를 기준으로 나눕니다. 정규식과 나누기 도구를 결합하면 진정한 강력함을 발휘합니다.

대부분의 정리되지 않은 가져오기 데이터에는 다음 순서가 잘 맞습니다.

• 제어 문자를 제거하려면 CLEAN
• 공백을 바로잡으려면 TRIM
• 구분 기호를 통일하거나 불필요한 내용을 제거하려면 REGEXREPLACE
• 열로 나누려면 SPLIT

아래 수식은 하나의 셀에 네 단계를 차례로 모두 적용합니다.

SPLIT은 기본적으로 부분들을 열 방향으로 펼칩니다. 때로는 필터링하거나 개수를 셀 수 있도록, 행마다 하나의 값이 들어 있는 세로 목록이 필요합니다.

SPLIT을 TRANSPOSE로 감싸 펼쳐진 행을 열로 바꾸세요. 아래 수식은 A2를 쉼표 기준으로 나눈 뒤 결과를 세로로 쌓으므로, 쉼표로 구분된 목록을 깔끔한 조회 열로 바꾸기에 적합합니다.

나누기와 정리 방법을 얼마나 이해했는지 확인해 보세요.

Google 스프레드시트에서 나누기와 정리 도구를 결합하여 이 과정을 마쳤습니다.

• SPLIT(text, delimiter)는 셀을 나누어 여러 열로 펼칩니다
• 추가 인수로 여러 문자로 된 구분 기호와 빈 부분을 제어할 수 있습니다
• CLEAN은 보이지 않는 제어 문자를 제거하고, TRIM은 공백을 바로잡습니다
• 나누기 전에 REGEXREPLACE를 사용하여 지저분한 구분 기호를 통일하세요
• CLEAN, TRIM, REGEXREPLACE, SPLIT을 연결하면 반복해서 사용할 수 있는 정리 작업을 만들 수 있습니다

이제 일치, 추출, 대체, 나누기, 정리를 모두 수행할 수 있는 완전한 정규식 도구 모음을 갖추었습니다.