vba 문자열 파싱 함수

2020년 2월 26일 의견 작성하기 
'######################################################################
'특정 문자열 사이의 문자열을 추출하는 함수
'book에서 n번째 등장하는 text1을 찾고, 그 뒤로부터 m번째 등장하는 text2 사이의 텍스트를 반환하는 함수
'book:찾을곳 text1:좌측문자열,text2:우측문자열, n:text1순서, m:text2순서
'######################################################################
Function TextFind(book, text1, text2, Optional n = 1, Optional m = 1)
    Dim temp1: temp1 = 1
    Dim temp2: temp2 = 1
    Dim i
    Dim j
    For i = 0 To n - 1
        temp1 = InStr(temp1, book, text1) + Len(text1)
    Next
    temp2 = InStr(temp1, book, text2) + Len(text2)
    For j = 1 To m - 1
        temp2 = InStr(temp2, book, text2) + Len(text2)
    Next
    If InStr(1, book, text1) = 0 Or InStr(temp1, book, text2) = 0 Or TextCount(book, text1) < n Then
        TextFind = ""
    Else
        TextFind = Mid(book, temp1, temp2 - temp1 - Len(text2))
    End If
End Function

'######################################################################
'문자열에 특정 문자열이 몇개 있는지 반환하는 함수
'(book의 길이 - text를 찾아서 모두 공백으로 만든 길이) / text길이
'######################################################################
Function TextCount(book, text)
    TextCount = (Len(book) - Len(Replace(book, text, ""))) / Len(text)
End Function

원하는 문자열을 찾을때 만든 함수이다.

<div id="NM_RTK_VIEW_list_wrap" class="ah_list">
   <div class="ah_tab">
    <a href="#" role="tab" class="ah_tab_btn ah_tab_on NM_RTK_VIEW_list_tab" data-tab-index="0" data-clk="lve.tab1">1~10위</a><a href="#" role="tab" class="ah_tab_btn NM_RTK_VIEW_list_tab" data-tab-index="1" data-clk="lve.tab2">11~20위</a>
   </div>
   <ul class="ah_l NM_RTK_VIEW_list_content" style="display: block;">
    <li class="ah_item" data-rtk-rank="1">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%A7%84%EC%84%9C%EC%97%B0&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd1">
       <span class="ah_r">1</span>
       <span class="ah_k">진서연</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="2">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%A5%AC%EB%A7%8C%EC%A7%80&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd2">
       <span class="ah_r">2</span>
       <span class="ah_k">쥬만지</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="3">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%ED%95%9C%EC%83%81%EC%A7%84&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd3">
       <span class="ah_r">3</span>
       <span class="ah_k">한상진</span>
      </a>
    </li>
    <li class="ah_item  ah_on" data-rtk-rank="4">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%A0%95%EB%AF%BC%EA%B2%BD&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd4">
       <span class="ah_r">4</span>
       <span class="ah_k">정민경</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="5">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%A5%AC%EB%A7%8C%EC%A7%80%20%EC%83%88%EB%A1%9C%EC%9A%B4%EC%84%B8%EA%B3%84&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd5">
       <span class="ah_r">5</span>
       <span class="ah_k">쥬만지 새로운세계</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="6">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%B0%95%EB%AA%85%EC%88%98&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd6">
       <span class="ah_r">6</span>
       <span class="ah_k">박명수</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="7">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%AF%B8%EC%8A%A4%20%ED%8E%98%EB%A0%88%EA%B7%B8%EB%A6%B0%EA%B3%BC%20%EC%9D%B4%EC%83%81%ED%95%9C%20%EC%95%84%EC%9D%B4%EB%93%A4%EC%9D%98%20%EC%A7%91&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd7">
       <span class="ah_r">7</span>
       <span class="ah_k">미스 페레그린과 이상한 아이들의 집</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="8">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%98%81%ED%99%94%20%ED%95%98%EB%A3%A8&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd8">
       <span class="ah_r">8</span>
       <span class="ah_k">영화 하루</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="9">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EA%B9%80%EC%A0%95%EA%B7%A0%20%EC%A0%95%EB%AF%BC%EA%B2%BD&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd9">
       <span class="ah_r">9</span>
       <span class="ah_k">김정균 정민경</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="10">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%B6%90%EB%B6%90%ED%8C%8C%EC%9B%8C&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd10">
       <span class="ah_r">10</span>
       <span class="ah_k">붐붐파워</span>
      </a>
    </li>
   </ul>
   <ul class="ah_l NM_RTK_VIEW_list_content" style="display: none;">
    <li class="ah_item" data-rtk-rank="11">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%B0%95%ED%95%98%EC%96%80&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd11">
       <span class="ah_r">11</span>
       <span class="ah_k">박하얀</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="12">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%82%AD%EB%A7%8C%EB%8B%A5%ED%84%B0%20%EA%B9%80%EC%82%AC%EB%B6%803&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd12">
       <span class="ah_r">12</span>
       <span class="ah_k">낭만닥터 김사부3</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="13">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%9A%B0%EC%B2%B4%EA%B5%AD%EB%AA%B0&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd13">
       <span class="ah_r">13</span>
       <span class="ah_k">우체국몰</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="14">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%86%8D%ED%98%91%ED%95%98%EB%82%98%EB%A1%9C%EB%A7%88%ED%8A%B8&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd14">
       <span class="ah_r">14</span>
       <span class="ah_k">농협하나로마트</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="15">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EC%9A%B0%EC%A0%95%EC%82%AC%EC%97%85%EB%B3%B8%EB%B6%80&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd15">
       <span class="ah_r">15</span>
       <span class="ah_k">우정사업본부</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="16">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EB%85%B8%EC%9B%90%EA%B5%AC%EC%B2%AD&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd16">
       <span class="ah_r">16</span>
       <span class="ah_k">노원구청</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="17">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%ED%95%98%EB%A3%A8&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd17">
       <span class="ah_r">17</span>
       <span class="ah_k">하루</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="18">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EA%B1%B8%EB%A6%AC%EB%B2%84%EC%9D%B8%EC%87%84&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd18">
       <span class="ah_r">18</span>
       <span class="ah_k">걸리버인쇄</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="19">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EA%B4%91%EB%AA%85%EC%8B%9C%20%ED%99%95%EC%A7%84%EC%9E%90&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd19">
       <span class="ah_r">19</span>
       <span class="ah_k">광명시 확진자</span>
      </a>
    </li>
    <li class="ah_item" data-rtk-rank="20">
      <a href="https://search.naver.com/search.naver?where=nexearch&query=%EA%B4%91%EB%AA%85%EC%8B%9C%EC%B2%AD&sm=top_lve.ag30sgr0mamsimenpspm&ie=utf8" class="ah_a" data-clk="lve.kwd20">
       <span class="ah_r">20</span>
       <span class="ah_k">광명시청</span>
      </a>
    </li>
   </ul>
 </div>

위와 같은 문자열 popular_keyword가 있을때 span.ah_k에 text만 추출하려면

TextFind(popular_keyword, "<span class=" & Chr(34) & "ah_k" & Chr(34) & ">", "</span>")

이렇게만 해주면 된다.

테스트를 위해 네이버 실시간검색어를 가져왔다
TextFind함수_테스트.xlsm
네이버 인기검색어 파싱 결과

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다