엑셀 시트의 셀에는 다양한 데이터를 입력하게 됩니다.
문자 데이터, 숫자 데이터, 문자/숫자/기호 등이 혼합된 문자열 데이터 등 정말 다양한 종류의 셀 데이터를 접하게 됩니다.
이렇게 다양한 셀 데이터를 문자만 또는 숫자만 남도록 정리해야 한다면 어떻게 하면 될까요?
만약 엑셀 2021 이상 버전을 사용하고 있다면 TEXTJOIN 함수와 SEQUENCE 함수를 사용하여 간단하게 수식으로 처리를 할 수 있습니다.
엑셀 2021 이전 버전 사용자를 위해 엑셀 VBA를 활용해서 셀 데이터에서 숫자만 남기고 다른 문자를 모두 제거하거나 반대로 숫자만 모두 제거하는 방법에 대해 알아보고자 합니다.
이번 글은 엑셀 VBA 활용 – 셀에서 숫자가 아닌 문자를 제거 또는 숫자만 제거 하는 방법에 대한 설명입니다.
Excel VBA – 셀에서 숫자가 아닌 문자를 제거 또는 숫자만 제거 방법
1. 아래 화면과 같이 엑셀 시트의 셀에 문자와 숫자가 함께 섞여 있는 데이터가 있을 때, 숫자만 남기도 다른 문자는 모두 제거하는 VBA 프로그램을 설명해 보겠습니다.
2. 아래 화면은 Microsoft Visual Basic for Applications의 화면입니다.
- 엑셀 화면에서 VBA 화면을 여는 방법은 단축키 Alt + F11 사용하는 방법과 개발 도구 메뉴에서 Visual Basic을 선택하는 방법이 있습니다.
- VBA 사용이 익숙하지 않는 초급자라면 이전에 포스팅한 VBA 관련 글을 참고하시기 바랍니다.
3. 실제 VBA 소스 프로그램 입니다.
4. 소스 코드를 붙여 넣기 한 다음 매크로 실행을 클릭하면 바로 동작합니다.
Sub RemoveString() ‘ ‘ 셀 데이터에서 숫자만 남겨두고 나머지 문자는 모두 제거 VBA 예제 ‘ Dim Rng As Range Dim WorkRng As Range On Error Resume Next sTitle = “셀을 선택하세요.” Set WorkRng = Application.Selection Set WorkRng = Application.InputBox(“Range”, sTitle, WorkRng.Address, Type:=8) ‘선택한 셀을 차례대로 하나씩 처리함 For Each Rng In WorkRng ‘최종 결과를 받은 변수 sResult = “” ‘하나의 셀 데이터를 한문자씩 숫자인지 여부 비교 For i = 1 To Len(Rng.Value) sTemp = Mid(Rng.Value, i, 1) ‘숫자인지 여부 체크 If sTemp Like “[0-9]” Then sStr = sTemp Else sStr = “” ‘숫자가 아니면 해당 문자 없앰 End If sResult = sResult & sStr Next i Rng.Value = sResult Next End Sub |
5. VBA 매크로를 실행하면 아래 화면처럼 셀을 선택하는 대화 상자가 시트 화면에 나타나게 됩니다.
그럼 셀을 선택하고, 확인 버튼을 클릭하면 셀에 숫자만 남고 다른 문자는 모두 제거됩니다.
6. 아래 화면은 VBA 매크로 실행 결과 화면입니다.
왼쪽은 숫자만 남아 있는 화면이고, 오른쪽은 반대로 숫자만 모두 제거된 화면입니다.
7. 숫자만 제거하는 소스 코드는 아래와 같습니다.
‘숫자인지 여부 체크 If sTemp Like “[0-9]” Then sStr = “” ‘숫자면 제거 Else sStr = sTemp End If |
8. 만약 숫자만 남기고 다른 문자를 제거할 때 소수점을 숫자와 함께 남겨 놓고자 한다면 아래와 같이 같이 소스 코드를 작성하면 됩니다.
- 비교 구문에 [0-9] 대신 소수점을 포함해서 [0-9.]으로 하면 됩니다.
‘숫자인지 여부 체크 If sTemp Like “[0-9.]” Then sStr = sTemp Else sStr = “” ‘숫자도 아니고, 아니면 해당 문자 없앰 End If |
Excel VBA – 셀에서 숫자가 아닌 문자를 제거 응용하기
1. 셀에 일반적은 텍스트와 함께 날짜가 포함된 경우 날짜만 남기고 싶을 때 또는 날짜만 제거하고자 할 때
- 오늘은 2024.06.27 입니다. => Like “[0-9.]” => 2024.06.27
- 오늘은 2024-06-27 입니다. => Like “[0-9-]” => 2024-06-27
2. 셀에 일반적은 텍스트와 함께 전화번호가 포함된 경우 전화번호만 남기고 싶을 때 또는 전화번호만 제거하고자 할 때
- 홍길동의 전화번호는 010-1234-1234 입니다. => Like “[0-9-]” => 010-1234-1234
3. 셀 텍스트에서 영문자만 남기고 싶을 때 또는 영문자만 제거하고 싶을 때
- 오늘은 June월 27일 입니다. => Like “[a-zA-Z]” => June
4. 셀 텍스트에서 특정 기호만 남기고 싶을 때 또는 특정 기호만 제거하고 싶을 때
- 오늘은 ##월 $$일 입니다!!**. => Like “[@#$%^&]” => ##$$
마무리
엑셀에서 분석 자료를 만들거나 보고서를 작성하기 위해 셀 데이터를 정리하는 것은 정말 자주 하는 작업 중에 하나 입니다.
다양하게 수집 된 자료에서 숫자만 뽑아내거나, 문자만 뽑아내거나, 특정 기호만 뽑아 내는 작업을 해야 하는 경우 여기에서 소개한 VBA 프로그램을 참고하거나 약간 응용해서 사용하면 원하는 결과로 쉽게 정리가 가능하리라 생각됩니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.