title: How to separate/filter English text from Chinese in Excel
body: <p>I am working on a project that includes multiples Excel files with cells containing English, Chinese, or both English and Chinese. </p>
  
  <p>I need to keep the rows that are completely in Chinese and put them first. Then, I need lines with both Chinese characters and English. And only then those that are in English only.</p>
  
  <p>I came across the following 3 functions that could help me mark the content accordingly, yet they do not seem to be working as expected, and I cannot figure out why.</p>
  
  <pre><code>Function ExtractChn(txt As String)
  Dim i As Integer
  Dim ChnTxt As String
  For i = 1 To Len(txt)
      If Asc(Mid(txt, i, 1)) &lt; 0 Then
          ChnTxt = ChnTxt &amp; Mid(txt, i, 1)
      End If
  Next i
  ExtractChn = ChnTxt
  End Function
  
  Function ExtractEng(txt As String)
  Dim i As Integer
  Dim EngTxt As String
  For i = 1 To Len(txt)
      If Asc(Mid(txt, i, 1)) &gt;= 0 Then
          EngTxt = EngTxt &amp; Mid(txt, i, 1)
      End If
  Next i
  ExtractEng = EngTxt
  End Function
  
  Function CheckTxt(txt)
  Dim i As Integer
  Dim Eng As Integer
  Dim Chn As Integer
  Chn = 0
  Eng = 0
  For i = 1 To Len(txt)
      If Asc(Mid(txt, i, 1)) &gt; 0 Then
          Eng = 1
      Else:
          Chn = 1
      End If
  Next i
  If Chn = 1 And Eng = 1 Then   'Contains Both Eng &amp; Chn
      CheckTxt = "BOTH"
  Else:
      If Chn = 1 And Eng = 0 Then    'Chn
          CheckTxt = "CHN"
      Else:
          If Chn = 0 And Eng = 1 Then   'Eng
              CheckTxt = "ENG"
          End If
      End If
  End If
  End Function
  </code></pre>
  
  <p>The person who created them even supplied a file demonstrating how the functions work. I am attaching the link to the file which has the arrangement as follows: </p>
  
  <pre><code>Text|English part of it|Chinese part of it|ExtractEng|ExtractChn|CheckTxt
  </code></pre>
  
  <p>According to the author's intentions, the <code>CheckTxt</code> result should display either <code>CH</code>, <code>ENG</code>, or <code>BOTH</code>. However, it is only displaying <code>ENG</code> at all times and I cannot figure why.</p>
  
  <p>Any ideas how to make it work? Unless there is an easier way to 'advance-filter' the content in Excel? Any help will be much appreciated.</p>
  
  <p><a href="https://a13d85c3-a-62cb3a1a-s-sites.googlegroups.com/site/exceltkk2/excel-tutorial/howtoseparateenglishandchineseword-vbafunctionasc/HowtoSeparateEnglishandChineseWord-VBAFunctionASC.xls?attachauth=ANoY7cqrghw6DmqtRoV2yy-vfRFZz66oEa1e3NNrR-_PdS8Fe61NRntZGQQMAbBuEwSw48p0iDNLd-9TEXSDR90HF2dTfb_MQjtVC0RgHab1cBX6npR4Ng7Yla9lF-OOz7GXQkgNCVZgSoG_RXe9muQEoIiE6SyRRgZ12IXiIChY9UqFhxF7f9DoET_98d_lo6rS_UAE6McZuaVBGVCtT72MT0xogEwFiERKWkjivUQM28_qHfbymrYqHEH5sl2qD7fTCSwSVpZjbRT4EkWepOpzp9tCG6JXgDhLP8xGo-u0Twdrg0ZPBORElaHiXy4cAQ8ARKxZq6dXKuchA3JrLNuBObiHc8lyvtj2xjrmMq-Uux6dwxEfW44%3D&amp;attredirects=0&amp;d=1" rel="nofollow" title="Test file">Test Excel file from the developer</a></p>