Excel에서 텍스트로 작업할 때 가장 시간이 많이 걸리고 실망스러운 작업 중 하나는 파싱 – 영숫자 "죽"을 구성 요소로 구문 분석하고 필요한 조각을 추출합니다. 예를 들어:
- 주소에서 우편번호 추출(우편번호가 항상 맨 앞에 있으면 좋지만 그렇지 않으면 어떻게 되나요?)
- 은행 명세서의 지불 설명에서 송장의 번호와 날짜 찾기
- 거래 상대방 목록에 있는 회사의 잡다한 설명에서 TIN 추출
- 설명 등에서 자동차 번호 또는 품목 번호를 검색하십시오.
일반적으로 이러한 경우 XNUMX분 동안 수동으로 텍스트를 골똘히 고르고 나면 이 프로세스를 자동화할 생각이 떠오르기 시작합니다(특히 데이터가 많은 경우). 다양한 수준의 복잡성 효율성을 가진 여러 솔루션이 있습니다.
- 내장 Excel 텍스트 함수 잘라 붙이기 텍스트를 검색하려면: 레브심브 (왼쪽), 권리 (오른쪽), PSTR (중), STEPIT (사슬 같이 잇다) 그리고 그 유사체, 콤바인 (공동문), 정확한 (정확한) 등. 이 방법은 텍스트에 명확한 논리가 있는 경우에 좋습니다(예: 색인이 항상 주소의 시작 부분에 있음). 그렇지 않으면 수식이 훨씬 더 복잡해지고 때로는 배열 수식이 발생하여 큰 테이블에서 속도가 크게 느려집니다.
- 사용 텍스트 유사성 연산자처럼 사용자 지정 매크로 함수로 래핑된 Visual Basic에서. 이렇게 하면 와일드카드 문자(*, #,? 등)를 사용하여 보다 유연한 검색을 구현할 수 있습니다. 불행히도 이 도구는 텍스트에서 원하는 부분 문자열을 추출할 수 없으며 포함되어 있는지만 확인하십시오.
위의 내용 외에도 전문 프로그래머, 웹 개발자 및 기타 기술자의 좁은 범위에서 매우 잘 알려진 또 다른 접근 방식이 있습니다. 정규식 (정규 표현식 = RegExp = "regexps" = "regulars"). 간단히 말해서, RegExp는 특수 문자와 규칙을 사용하여 텍스트에서 필요한 부분 문자열을 검색하거나 추출하거나 다른 텍스트로 바꾸는 언어입니다.. 정규식은 텍스트 작업의 다른 모든 방법을 훨씬 능가하는 매우 강력하고 아름다운 도구입니다. 많은 프로그래밍 언어(C#, PHP, Perl, JavaScript…)와 텍스트 편집기(Word, Notepad++…)는 정규식을 지원합니다.
Microsoft Excel에는 불행히도 기본적으로 RegExp 지원이 없지만 VBA로 쉽게 수정할 수 있습니다. 탭에서 Visual Basic Editor를 엽니다. 개발자 (개발자) 또는 키보드 단축키 다른+F11. 그런 다음 메뉴를 통해 새 모듈을 삽입하십시오. 삽입 – 모듈 거기에 다음 매크로 함수의 텍스트를 복사합니다.
Public 함수 RegExpExtract(텍스트를 문자열로, 패턴을 문자열로, 선택적 항목을 정수로 = 1) 오류 시 문자열로 GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (텍스트) 그런 다음 설정 일치 = regex.Execute(텍스트) RegExpExtract = matching.Item(Item - 1) 종료 기능 End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function 이제 Visual Basic Editor를 닫고 Excel로 돌아가서 새로운 기능을 사용해 볼 수 있습니다. 구문은 다음과 같습니다.
=RegExpExtract( Txt ; 패턴 ; 항목 )
어디에
- TXT – 검사하고 필요한 부분 문자열을 추출하려는 텍스트가 있는 셀
- 무늬 – 부분 문자열 검색을 위한 마스크(패턴)
- 항목 – 추출할 부분 문자열의 시퀀스 번호(여러 개 있는 경우)(지정하지 않은 경우 첫 번째 항목이 표시됨)
물론 여기서 가장 흥미로운 점은 RegExp의 "언어로 된" 특수 문자의 템플릿 문자열인 Pattern입니다. 이 템플릿은 우리가 찾고자 하는 대상을 정확히 지정합니다. 시작하기 위한 가장 기본적인 사항은 다음과 같습니다.
| 무늬 | 상품 설명 |
| . | 가장 간단한 것은 점입니다. 지정된 위치에 있는 패턴의 모든 문자와 일치합니다. |
| s | 공백처럼 보이는 모든 문자(공백, 탭 또는 줄 바꿈). |
| S | 이전 패턴의 반변형(anti-variant), 즉 공백이 아닌 문자. |
| d | 임의의 숫자 |
| D | 이전 것의 반변이(anti-variant), 즉 모든 NOT 숫자 |
| w | 모든 라틴 문자(AZ), 숫자 또는 밑줄 |
| W | 이전 것의 반변형(anti-variant), 즉 라틴어가 아니며 숫자나 밑줄이 아닙니다. |
| [문자] | 대괄호 안에 텍스트의 지정된 위치에 허용되는 하나 이상의 문자를 지정할 수 있습니다. 예를 들어 미술 다음 단어 중 하나와 일치합니다. 테이블 or 의자. 또한 문자를 열거할 수 없지만 하이픈으로 구분된 범위로 설정합니다. [ABDCDEF] 쓰다 [AF]. 또는 대신 [4567] 소개 [-4 7]. 예를 들어 모든 키릴 문자를 지정하려면 템플릿을 사용할 수 있습니다. [아야야야야요요]. |
| [^문자] | 여는 대괄호 뒤에 "뚜껑" 기호를 추가하면 ^, 그러면 세트는 반대 의미를 얻습니다. 텍스트의 지정된 위치에서 나열된 문자를 제외한 모든 문자가 허용됩니다. 예, 템플릿 [^ЖМ]우트 찾을 것이다 경로 or 물질 or 잊다, 하지만 무서운 or Mut예를 들어. |
| | | 부울 연산자 OR (OR) 지정된 기준을 확인합니다. 예를 들어 (에서목|s짝수|송장) 지정된 단어에 대한 텍스트를 검색합니다. 일반적으로 옵션 세트는 괄호로 묶입니다. |
| ^ | 라인의 시작 |
| $ | 줄 끝 |
| b | 단어의 끝 |
예를 들어 XNUMX자리 우편 번호 또는 XNUMX자리 제품 코드와 같이 특정 수의 문자를 찾고 있는 경우 구조에 나선다. 수량 자 or 수량 자 검색할 문자 수를 지정하는 특수 표현식입니다. 수량자는 그 앞에 오는 문자에 적용됩니다.
| 퀀터 | 상품 설명 |
| ? | XNUMX 또는 XNUMX회 발생. 예를 들어 .? 하나의 문자 또는 부재를 의미합니다. |
| + | 하나 이상의 항목. 예를 들어 d+ 임의의 자릿수(즉, 0에서 무한대 사이의 임의의 숫자)를 의미합니다. |
| * | XNUMX개 이상의 발생, 즉 임의의 수량. 그래서 s* 임의의 수의 공백 또는 공백 없음을 의미합니다. |
| {번호} or {number1,number2} | 엄격하게 정의된 발생 횟수를 지정해야 하는 경우 중괄호로 지정됩니다. 예를 들어 ㄹ{6} 엄격하게 XNUMX자리 숫자를 의미하며 패턴은 초{2,5} - XNUMX~XNUMX칸 |
이제 가장 흥미로운 부분으로 넘어가겠습니다. 생성된 기능의 적용 분석과 실제 생활에서 패턴에 대해 배운 내용입니다.
텍스트에서 숫자 추출
먼저 간단한 경우를 분석해 보겠습니다. 영숫자 죽에서 첫 번째 숫자를 추출해야 합니다. 예를 들어 가격 목록에서 무정전 전원 공급 장치의 전원을 추출해야 합니다.
정규식 뒤에 있는 논리는 간단합니다. d 모든 숫자를 의미하고 수량자는 + 숫자가 하나 이상이어야 한다고 말합니다. 함수 앞의 이중 빼기는 추출된 문자를 텍스트로서의 숫자에서 전체 숫자로 "즉시" 변환하는 데 필요합니다.
우편 번호
언뜻 보기에 모든 것이 간단합니다. 정확히 XNUMX자리 숫자를 연속으로 찾고 있습니다. 우리는 특수 문자를 사용합니다 d 숫자 및 수량자용 6{} 문자 수:
그러나 줄의 인덱스 왼쪽에 일련의 다른 큰 숫자 집합(전화번호, TIN, 은행 계좌 등)이 있는 경우 상황이 가능합니다. 그러면 정규 시즌에서 처음 6개를 꺼낼 것입니다. 숫자, 즉 올바르게 작동하지 않습니다.
이런 일이 발생하지 않도록 하려면 정규식 가장자리 주위에 수정자를 추가해야 합니다. b 단어의 끝을 의미합니다. 이렇게 하면 필요한 조각(색인)이 다른 조각(전화 번호)의 일부가 아니라 별도의 단어여야 한다는 것을 Excel에서 명확하게 알 수 있습니다.
연락처
텍스트에서 전화번호를 찾는 문제는 하이픈이 있거나 없는, 공백을 통해, 대괄호에 지역 코드가 있거나 없는 등 번호를 쓰는 옵션이 너무 많다는 것입니다. 따라서 제 생각에는 먼저 여러 중첩 함수를 사용하여 소스 텍스트에서 이러한 모든 문자를 제거합니다. 대용품 (대리자)하나의 전체로 함께 붙은 다음 기본 규칙으로 ㄹ{11} 연속으로 11자리 숫자를 뽑습니다.
ITN
TIN(우리 나라의 경우)은 10자리(법인의 경우) 또는 12자리(개인의 경우)일 수 있기 때문에 여기에서는 조금 더 복잡합니다. 특별히 흠을 찾지 못한다면 일반 ㄹ{10,12}, 그러나 엄밀히 말하면 10에서 12까지의 모든 숫자를 뽑아낼 것입니다. 즉, 11자리를 잘못 입력했습니다. 논리적 OR 연산자로 연결된 두 패턴을 사용하는 것이 더 정확합니다. | (세로 막대):
쿼리에서 먼저 12비트 숫자를 찾은 다음 10비트 숫자만 찾습니다. 정규 표현식을 반대로 작성하면 모든 사람, 심지어 긴 12비트 TIN이라도 처음 10자만 뽑아낼 것입니다. 즉, 첫 번째 조건이 트리거된 후 더 이상 확인이 수행되지 않습니다.
이것은 연산자 간의 근본적인 차이점입니다. | 표준 Excel 논리 함수에서 OR (OR), 여기서 인수를 재정렬해도 결과가 변경되지 않습니다.
제품 SKU
많은 회사에서 상품과 서비스(상품, SAP 코드, SKU 등)에 고유 식별자가 할당됩니다. 표기법에 논리가 있으면 정규 표현식을 사용하여 모든 텍스트에서 쉽게 추출할 수 있습니다. 예를 들어, 우리 기사가 항상 대문자 XNUMX개, 하이픈 및 후속 XNUMX자리 숫자로 구성되어 있다는 것을 알고 있다면 다음과 같습니다.
템플릿 뒤에 있는 논리는 간단합니다. [아즈] – 라틴 알파벳의 모든 대문자를 의미합니다. 다음 수량자 3{} 정확히 XNUMX개의 그러한 문자가 있다는 것이 우리에게 중요하다고 말합니다. 하이픈 다음에 XNUMX자리를 기다리므로 끝에 추가합니다. ㄹ{3}
현금 금액
이전 단락과 유사한 방식으로 상품 설명에서 가격(비용, VAT ...)을 추출할 수도 있습니다. 예를 들어 화폐 금액이 하이픈으로 표시되는 경우:
무늬 d 수량자와 함께 + 하이픈까지의 숫자를 검색하고 ㄹ{2} 페니(XNUMX자리)를 찾습니다.
가격이 아니라 VAT를 추출해야 하는 경우 추출할 요소의 서수를 지정하는 RegExpExtract 함수의 세 번째 선택적 인수를 사용할 수 있습니다. 그리고 물론, 당신은 기능을 대체할 수 있습니다 대용품 (대리자) 결과에서 표준 소수점 구분 기호에 하이픈을 넣고 Excel이 발견된 VAT를 일반 숫자로 해석하도록 처음에 이중 빼기를 추가합니다.
자동차 번호판
특수 차량, 트레일러 및 기타 오토바이를 타지 않는 경우 "문자 – 숫자 2개 – 문자 3개 – 지역 코드" 원칙에 따라 표준 차량 번호가 구문 분석됩니다. 또한, 지역코드는 XNUMX자리 또는 XNUMX자리로 구성할 수 있으며, 라틴 알파벳과 모양이 유사한 것만 문자로 사용됩니다. 따라서 다음 정규 표현식은 텍스트에서 숫자를 추출하는 데 도움이 됩니다.
Time
HH:MM 형식으로 시간을 추출하려면 다음 정규식이 적합합니다.
결장 조각 후 [0-5]디, 알아보기 쉽기 때문에 00-59 범위의 숫자를 설정합니다. 괄호 안의 콜론 앞에 논리적 OR(파이프)로 구분된 두 가지 패턴이 작동합니다.
- [0-1]디 – 00-19 범위의 모든 숫자
- 2[0-3] – 20-23 범위의 모든 숫자
얻은 결과에 표준 Excel 기능을 추가로 적용할 수 있습니다. TIME (팀)프로그램에서 이해할 수 있고 추가 계산에 적합한 시간 형식으로 변환합니다.
비밀번호 확인
정확성을 위해 사용자가 발명한 암호 목록을 확인해야 한다고 가정합니다. 당사 규정에 따라 비밀번호는 영문자(소문자 또는 대문자)와 숫자만 포함할 수 있습니다. 공백, 밑줄 및 기타 구두점은 허용되지 않습니다.
다음과 같은 간단한 정규식을 사용하여 검사를 구성할 수 있습니다.
사실, 그러한 패턴으로 우리는 시작(^) 그리고 끝 ($) 우리 텍스트에는 대괄호로 묶인 집합의 문자만 있었습니다. 암호 길이(예: 최소 6자)도 확인해야 하는 경우 수량자 + 형식에서 "XNUMX 이상" 간격으로 대체될 수 있습니다. {삼,}:
주소에서 도시
주소 표시줄에서 도시를 가져와야 한다고 가정해 보겠습니다. 일반 프로그램이 "g"에서 텍스트를 추출하는 데 도움이 됩니다. 다음 쉼표로:
이 패턴을 자세히 살펴보겠습니다.
위의 텍스트를 읽었다면 정규 표현식의 일부 문자(마침표, 별표, 달러 기호 등)에 특별한 의미가 있다는 것을 이미 이해한 것입니다. 이러한 문자 자체를 찾아야 하는 경우 백슬래시(때로는 차폐). 따라서 단편 "g."를 검색할 때. 정규 표현식으로 작성해야 합니다. 씨. 우리가 플러스를 찾고 있다면 + 등
템플릿의 다음 두 문자인 점과 수량자 별표는 임의의 수의 문자, 즉 임의의 도시 이름을 나타냅니다.
"g"에서 텍스트를 찾고 있기 때문에 템플릿 끝에 쉼표가 있습니다. 쉼표로. 하지만 텍스트에는 여러 개의 쉼표가 있을 수 있습니다. 맞죠? 도시뿐만 아니라 거리, 집 등 뒤에서도 우리의 요청은 어느 곳에서 멈출까요? 그것이 물음표의 목적입니다. 그것 없이는 정규 표현식이 가능한 가장 긴 문자열을 뽑아낼 것입니다:
정규 표현식의 관점에서 이러한 패턴은 "탐욕적"입니다. 상황을 바로 잡기 위해 물음표가 필요합니다. 수량자를 "인색"하게 만들고 쿼리는 "g" 뒤의 첫 번째 카운터 쉼표까지만 텍스트를 사용합니다.
전체 경로의 파일 이름
또 다른 매우 일반적인 상황은 전체 경로에서 파일 이름을 추출하는 것입니다. 형식의 간단한 정규 표현식은 여기에 도움이 될 것입니다:
여기서 트릭은 검색이 실제로 반대 방향으로 발생한다는 것입니다. $, 그리고 오른쪽에서 첫 번째 백슬래시까지 그 앞에 있는 모든 것을 찾고 있습니다. 백슬래시는 이전 예의 점처럼 이스케이프됩니다.
PS
"끝을 향하여" 나는 위의 모든 것이 정규 표현식이 제공하는 모든 가능성의 작은 부분임을 분명히 하고 싶습니다. 특수 문자와 사용 규칙이 많이 있으며 전체 책이 이 주제에 대해 작성되었습니다(최소한 이 책을 시작하는 것이 좋습니다). 어떤 면에서 정규 표현식을 작성하는 것은 거의 예술입니다. 거의 항상, 발명된 정규식은 개선되거나 보완되어 더 우아하게 만들거나 더 넓은 범위의 입력 데이터로 작업할 수 있습니다.
다른 사람의 정규식을 분석 및 구문 분석하거나 자신의 정규식을 디버그하기 위해 몇 가지 편리한 온라인 서비스가 있습니다. 정규식101, RegExr 그리고 더
불행히도 VBA(예: 역 검색 또는 POSIX 클래스)에서 클래식 정규식의 모든 기능이 지원되지 않고 키릴 자모에서 작동할 수 있는 것은 아니지만 처음으로 사용자를 기쁘게 하는 데는 충분하다고 생각합니다.
주제를 처음 접하지 않고 공유할 내용이 있는 경우 아래 설명에 Excel에서 작업할 때 유용한 정규식을 남겨주세요. 하나의 마음도 좋지만 두 개의 부츠가 한 켤레!
- SUBSTITUTE 함수로 텍스트 교체 및 정리
- 텍스트에서 라틴 문자 검색 및 강조 표시
- 가장 가까운 유사 텍스트 검색(Ivanov = Ivonov = Ivanof 등)