셀에서 모든 구두점(쉼표, 따옴표, 작은따옴표)을 제거하려면 어떻게 해야 하나요?

Excel에서 데이터를 다룰 때, 셀에 쉼표, 큰따옴표, 마침표, 물음표, 콜론 등 다양한 구두점이 포함된 상황을 마주할 수 있습니다. 이러한 구두점들은 데이터 분석, 정렬, 텍스트 추출 또는 서식 지정에 방해가 될 수 있습니다. 예를 들어, 추가 계산을 위해 텍스트를 준비하거나, 깨끗한 데이터 세트를 내보내거나, 사용자 입력을 표준화해야 할 경우 모든 구두점을 제거하고 문자와 숫자만 남기고 싶을 수 있습니다.

이 문서에서는 Excel의 셀에서 다양한 종류의 구두점을 효과적으로 제거하는 몇 가지 실용적인 솔루션을 제공합니다. User Defined Function, 전용 Excel 추가 기능 또는 기본 제공 수식을 선호하든 관계없이 여러분의 시나리오에 적합한 솔루션을 찾을 수 있을 것입니다.

• VBA 또는 추가 기능 없이 Excel 수식으로 모든 구두점 제거하기
• Kutools for Excel로 셀에서 모든 구두점 제거하기
• 사용자 정의 함수로 셀에서 모든 구두점 제거하기

VBA 또는 추가 기능 없이 Excel 수식으로 모든 구두점 제거하기

이 솔루션은 매크로를 사용할 수 없거나 Excel 추가 기능에 접근할 수 없는 사용자에게 이상적입니다. 중첩된 Excel 수식을 활용하여 텍스트에서 모든 구두점을 포괄적으로 제거할 수 있습니다. 이 방법은 Excel의 기본 제공 함수에 의존하며, 대부분의 조직 보안 정책과 호환됩니다. 참고: 이 수식 기반 접근 방식은 강력하지만, 다양한 구두점과 매우 긴 문자열을 처리할 때 복잡해질 수 있으며, 제거할 구두점 목록을 직접 정의하려면 수식을 조정해야 합니다.

시나리오 예시: 제품 설명이나 이름에 쉼표, 마침표, 물음표, 다양한 따옴표가 포함된 데이터 시트를 받았으며, 이를 모두 제거하여 깨끗한 보고서를 작성하거나 다른 시스템으로 가져와야 하는 경우입니다.

1. 대상 셀(B2 등)에 다음 수식을 입력하여 가장 일반적인 구두점 일부를 제거하세요(필요에 따라 문자 목록을 사용자 정의).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), ".", ""), "!", ""), "?", ""), ";", ""), ":", ""), """", "")

2. Enter 키를 눌러 수식을 적용하세요. 더 많은 행을 처리하려면, 수식을 추가 셀로 복사하기 위해 채우기 핸들을 아래로 드래그하세요.

추가 구두점을 제거해야 하는 경우, 각 레이어에서 원치 않는 문자를 대체하기 위해 추가 SUBSTITUTE 함수를 중첩하세요. 더 고급 시나리오 또는 구두점 목록이 긴 경우, 이 TEXTJOIN 배열 기반 수식을 사용할 수 있습니다 (Excel 2016 이상):

=TEXTJOIN("", TRUE, IF(ISNUMBER(FIND(MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1), "~!@#$%^&*()_-+={}[]:;""''<>,./?`\|")), "", MID(A2, ROW(INDIRECT("1:"&LEN(A2))),1)))

이 수식은 배열 수식으로 작동하며, ~!@#$%^&*()_-+={}[]:;""''<>,./?`\| 목록에 있는 모든 문자를 제거합니다. 이 수식을 사용하려면:

• B2(또는 대상 셀)에 입력하세요.
• Excel 365, 2019 또는 2016을 사용하는 경우, Enter만 누르세요. 이전 버전에서는 배열 수식 활성화를 확인하기 위해 Ctrl+Shift+Enter로 입력해야 할 수 있습니다.
• 필요에 따라 추가 행에 대해 아래로 복사하세요.

참고:

• 특정 요구 사항에 맞게 FIND() 함수 내의 구두점 목록을 업데이트하거나 확장하세요.
• 목록에서 따옴표를 사용할 때 주의하세요. 문자열 내에서 두 번씩 사용하세요(“”는 리터럴 “ 한 개를 의미).
• 앞뒤 공백은 유지됩니다. 필요하다면 TRIM()을 사용하여 추가 공백도 제거하세요.
• 배열 수식은 매우 큰 데이터 범위에서 성능에 영향을 미칠 수 있습니다.

문제 해결: 수식 출력이 오류인 경우, A2에 값이 있는지 확인하고 배열 수식이 올바르게 입력되었는지 확인하세요(Ctrl+Shift+Enter로 입력 후 중괄호 { }가 나타나는지 확인). 빈 결과는 해당 셀에 구두점만 있거나 빈 상태임을 의미합니다.

Kutools for Excel로 셀에서 모든 구두점 제거하기

텍스트를 자주 정리해야 하고 빠르고 그래픽적인 도구를 원한다면, Kutools for Excel의 Remove Characters 유틸리티가 선택한 범위에서 구두점을 효율적으로 제거하는 데 도움이 됩니다. 수식이나 코드와 달리 이 추가 기능은 사용자가 제거할 문자를 직접 지정할 수 있는 간단한 대화창을 제공하여 초보 및 고급 사용자 모두에게 유연성과 사용 편의성을 제공합니다.

1모든 구두점을 제거할 범위를 선택하고 클릭하세요. Kutools > Text > Remove Characters. 스크린샷 참조:

2. 열리는 Remove Characters 대화 상자에서 Custom 옵션만 선택하고 구두점을 입력하세요. ~!@#$%^&*()_-+={}[]:;""''<>,./?`\| 아래 상자에 입력한 후 Ok 버튼을 클릭하여 선택한 모든 셀에서 즉시 구두점 기호를 제거합니다. 아래 스크린샷 참조:

워크시트에서 결과를 즉시 검토하세요. 이 유틸리티는 특히 가져온 데이터를 정리하거나 분석을 위한 데이터 세트를 준비하거나 일관된 텍스트 서식 표준을 적용할 때 유용합니다. 선택한 셀에 직접 작동하므로 저장 전에 영향받은 데이터를 반드시 검토하세요.

데모: Kutools for Excel로 셀에서 모든 구두점 제거하기

사용자 정의 함수로 셀에서 모든 구두점 제거하기

이 방법은 사용자 정의 함수(UDF)를 소개합니다. 이 UDF를 통해 Excel의 셀에서 어떤 종류의 구두점도 제거할 수 있습니다. 이 작업을 자주 수행할 것으로 예상되며 워크북에서 매크로를 사용하는 것이 편한 경우, UDF는 매우 유용합니다. 이 방법은 거의 모든 구두점을 사용자 정의 가능한 방식으로 처리할 수 있으며, 함수를 공유하거나 재사용하려는 경우 특히 유용합니다.

다만, 이 솔루션은 매크로 활성화된 워크북이 필요하며, 매크로 정책이 엄격한 환경에서는 제한될 수 있습니다. 항상 새로운 코드를 실행하거나 추가하기 전에 작업을 저장하세요. 워크북을 공유하는 경우, 매크로 호환성을 고려해야 합니다.

1. Alt + F11 키를 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. Insert > Module을 클릭하고 새 모듈 창에 다음 코드를 복사하여 붙여넣으세요.

VBA: Excel 셀에서 구두점 제거하기

Function RemovePunctuation(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^A-Z0-9 ]"
.IgnoreCase = True
.Global = True
RemovePunctuation = .Replace(Txt, "")
End With
End Function

3. 코드를 입력한 후 Ctrl+S를 눌러 사용자 정의 함수를 저장하고 Visual Basic for Applications 창을 닫습니다. 함수가 다음에도 작동하도록 Macro-Enabled Workbook (*.xlsm) 형식으로 저장하세요.

4Excel로 돌아가서 결과를 표시할 빈 셀을 선택하고 수식을 입력하세요. =RemovePunctuation(A2) (A2는 정리하고자 하는 셀), 그리고 다른 셀에 적용하려면 Fill Handle을 필요한 만큼 아래로 드래그하세요.

수식을 적용하면 선택한 셀에서 모든 형태의 구두점이 제거되고 핵심 텍스트와 숫자만 남습니다. 아래와 같이:

팁: 매우 큰 데이터 세트의 경우, UDF가 다시 계산하는 데 시간이 걸릴 수 있습니다. 필요한 경우 출력을 값으로 변환하기 위해 복사하여 값을 붙여넣으세요.

관련 기사:

Excel에서 문자열/숫자/셀에서 문자를 제거하려면 어떻게 하나요?

Excel에서 텍스트 문자열에서 숫자를 제거하려면 어떻게 하나요?