Excel 에서 알파벳과 숫자가 혼합된 문자열에서 텍스트 추출만 추출하는 방법은？
실제 업무 환경에서 셀에 문자와 숫자가 혼합된 소위 알파뉴머릭(alphanumeric) 문자열이 포함된 경우가 많습니다. 예를 들어 "Order2058", 「User_15A」, "ID1234B"와 같은 데이터는 보고서 내보내기, 데이터베이스 출력물 또는 표준화된 코드에서 흔히 볼 수 있습니다. 추가 분석이나 보고를 위해 숫자 요소를 모두 제외하고 텍스트 부분만 분리해야 할 때도 있습니다. 이 문서에서는 Excel 에서 알파뉴머릭 문자열에서 문자만 효율적으로 추출하는 여러 가지 실용적인 방법과 일반적인 사용 시나리오， 주의 사항 및 문제 해결 팁을 안내합니다。
➤ Excel 에서 정의된 함수를 사용하여 알파벳과 숫자가 혼합된 문자열에서 텍스트 추출만 추출하기
➤ Kutools for Excel 을 사용하여 알파벳과 숫자가 혼합된 문자열에서 텍스트 추출만 추출하기
➤ Excel 수식을 사용하여 알파벳과 숫자가 혼합된 문자열에서 텍스트 추출만 추출하기
Excel 에서 사용자 정의 함수(UDF)를 사용해 알파뉴머릭 문자열에서 텍스트 추출만 추출하기
한눈에 보기
- 장점：대규모 데이터셋에서 동적으로 추출 가능； 복잡한 워크시트 수식보다 간단함； 모든 워크북에서 재사용 가능。
- 제한 사항：매크로를 활성화하고
.xlsm형식으로 저장해야 하며， 사용자 정의 함수는 다시 계산되어 매우 큰 범위에서 성능에 영향을 줄 수 있습니다。
VBA 사용에 익숙하다면 사용자 정의 함수(UDF)를 만들어 알파뉴머릭 문자열에서 직접 문자만 추출할 수 있습니다。 이는 기본 제공 함수가 부족하거나 지나치게 복잡해질 때 유용합니다。
수행 단계 개요：
1. 추출 수식을 사용할 셀을 선택한 후Alt + F11를 눌러Microsoft Visual Basic for Applications편집기를 엽니다。
2. VBA 편집기에서삽입>모듈을 클릭하여 새 모듈을 생성합니다。 다음 코드를 모듈에 붙여넣으세요。
VBA： 문자만 추출(ASCII A–Z)
Function TextOnly(pWorkRng As Range) As String
'Updatebyextendoffice
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
End If
Next
TextOnly = OutValue
End Function
3. 워크시트로 돌아가서 빈 셀에=TextOnly(A1)를 입력하세요(대상 셀에 따라)A1을 변경)。Enter를 누른 후 채우기 핸들을 드래그하여 열 전체에 적용합니다。 결과로 각 문자열에서 추출된 문자만 표시됩니다。
이 방법은 원본 데이터를 수정하지 않으며 결과는 새 셀/열에 나타나므로 원본 데이터가 그대로 유지됩니다。
문제 해결 및 팁
- 매크로 활성화 및 저장 형식：매크로가 활성화되어 있고 파일을 다음 형식으로 저장했는지 확인하세요。
.xlsm그래야 UDF 가 유지됩니다。 - 설계상 문자만 추출：이 함수는 숫자， 구두점 및 기호를 제거합니다。 공백을 유지하려면 두 번째 인수로
TRUE를 전달하세요(예：=TextOnly(A1, TRUE)). - 억양 부호/비 ASCII 문자：간단한 패턴
[A-Za-z]에는é, ü같은 문자가 포함되지 않습니다。 더 넓은 알파벳 범위가 필요하면 정규식 버전을 사용하여 문자 범위를 확장하세요(예：[A-Za-zÀ-ÖØ-öø-ÿ]) 또는 텍스트를 먼저 정규화하세요。 - 빈 셀/오류 셀：UDF 는 빈 셀이나 오류 값을 빈 문자열로 반환합니다。
- 성능：UDF 는 워크시트와 함께 다시 계산됩니다。 매우 큰 범위에서는 추출 후 결과를 값으로 변환하는 것을 고려하세요。
알파뉴머릭 문자열에서 텍스트 추출만 추출하기(Kutools for Excel 사용)
코드 없이 더 빠른 솔루션이 필요하다면Kutools for Excel을 사용해 셀에서 원치 않는 숫자 및 비문자 문자를 편리하게 제거할 수 있습니다。 이 방법은 텍스트 정리를 자주 수행하거나 대량 데이터를 처리하거나 VBA 작성을 피하고 싶은 사용자에게 이상적입니다。
한눈에 보기
- 기능 설명：숫자 및 기타 비문자 문자를 제거하여 문자만 남깁니다。
- 적합한 용도：수식이나 매크로 없이 일회성 또는 일괄 정리 작업。
Kutools for Excel은특정 문자 삭제기능을 제공하여 선택한 셀에서 비문자 콘텐츠를 효율적으로 제거할 수 있습니다。
Kutools for Excel은 업무를 간소화하는300가지 이상의 유용한 Excel 도구를 포함합니다。30일 동안 제한 없이 무료로 사용해 보세요。지금 다운로드
문자만 추출하는 방법은 다음과 같습니다：
1. 처리할 알파뉴머릭 문자열이 포함된 셀 범위를 선택하세요。
2. Kutools>텍스트>특정 문자 삭제로 이동하세요。 대화 상자가 나타납니다。 스크린샷 참조：
3. 특정 문자 삭제대화 상자에서 문자만 유지하는 가장 간단한 방법은비알파벳(때로는)비알파라고 표시됨)을 선택하는 것입니다。 그러면 A–Z 문자를 제외한 모든 것이 제거됩니다。 이 옵션이 없는 버전이라면숫자와인쇄 불가 문자를 선택하고(선택 사항)사용자 지정을 사용해 유지하지 않을 기호를 제거하세요。 스크린샷 참조：
4. 확인을 클릭하면 Kutools 가 선택 영역을 처리하여 모든 비문자 콘텐츠를 제거하고 선택한 셀에 텍스트 부분만 남깁니다。
문제 해결 및 팁
- 기존 셀 변경：이 작업은 선택한 셀을 덮어씁니다。 원본을 보존해야 한다면 먼저 새 위치에 복사하세요。
- 특정 문자 유지하기：공백이나 기호(예： 하이픈)를 유지하려면비알파벳옵션을 피하고 대신숫자,인쇄 불가 문자및사용자 지정을 조합하여 정밀하게 제어하세요。
Kutools 방식은 일회성 정리 작업에 가장 적합하며 소규모 및 대규모 데이터셋 모두에 효과적입니다. 동적(자동 업데이트) 결과가 필요하다면 수식이나 VBA UDF 를 대신 사용하는 것을 고려하세요。
Excel 수식을 사용해 알파뉴머릭 문자열에서 텍스트 추출만 추출하기
VBA 나 애드인 없이 해결책을 원하는 사용자의 경우 기본 제공 Excel 수식을 사용해 텍스트 추출 문자를 추출할 수 있습니다。 이 솔루션은 추출 결과를 동적으로 유지해야 하거나 매크로나 타사 도구를 사용할 수 없는 환경에서 특히 적합합니다。
다음과 같이 배열 수식을 사용할 수 있습니다：
1.결과를 표시할 빈 셀(예: B1)을 선택하고 다음 수식을 입력하세요(문자 A–Z 만 유지)：
=TEXTJOIN("",
TRUE,
IF(
(CODE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))>=65)*
(CODE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))<=90) +
(CODE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))>=97)*
(CODE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))<=122),
MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1),
""
)
)2.수식을 입력한 후에는Ctrl+Shift+Enter를 누르세요。 단， Excel 2019이하 버전을 사용 중인 경우입니다(이렇게 하면 배열 수식으로 변환됩니다)。 Microsoft 365용 Excel 또는 Excel 2021이상 버전을 사용 중이라면 그냥Enter키만 누르면 됩니다。 해당 버전부터는 동적 배열 수식을 기본적으로 지원하기 때문입니다。
A1 A1을 알파벳과 숫자가 포함된 문자열이 있는 셀의 참조로 바꾸세요。 이 수식은 각 문자열에서 알파벳만 추출하여 반환합니다。 더 많은 행에 수식을 적용하려면 B1 셀의 채우기 핸들을 아래로 끌어당기세요。
매개변수 설명：
- MID및ROW(INDIRECT(...))는 대상 셀의 각 문자를 열거합니다。
- CODE는 ASCII 범위65–90(A–Z) 및97–122(a–z)를 확인하여 문자만 유지합니다。
- TEXTJOIN은 유지된 문자들을 하나의 문자열로 연결합니다。
- 중간 길이 텍스트(셀당)1,000자 미만)에 가장 적합하며， 매우 긴 텍스트는 성능을 저하시킬 수 있습니다。
이 방법은 원본 데이터를 그대로 유지하며 추가 도구나 매크로 설정이 필요하지 않습니다。 오류가 발생하면 셀 참조와 배열 수식 입력 방식을 다시 확인하세요。
문제 해결 및 요약 제안
- 오류(예：
#VALUE!)가 표시되면 Excel 버전과 배열 입력(Ctrl+Shift+Enter2019 이전 버전용)을 확인하세요。 - 추출 후 결과에서 예상치 못한 공백이나 문자가 없는지 확인하고 필요 시 조정하세요。 워크플로우에 따라 Kutools， VBA 또는 수식 중 적절한 방법을 선택하세요。
관련 문서：
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약