VBA，在列中搜索特定字符，将字符串提取到该字符 [英] VBA, search in column for specific character, extract string upto that character

问题描述

在一个特定的列中，我想在单元格中搜索一个特定的字符...说（或/。一旦在单元格中找到这个字符，我想从字符串，直到找到这个字符，在与它相邻的单元格中。

例如，列中的几个值可能看起来像 -

 三星（印度）
三星/ Dhamal 
黑莓（chikna）
黑莓/ Kala Anda 
 iPhone  - egypt 
 iPhone 5 * yeda 
  

输出将看起来像 -

 三星
三星
黑莓
黑莓
 iPhone 
 iPhone 5 
  

注意：该特定列中的单元格值不是静态的，没有模式，可能包含其他特殊字符，不具有特定长度。

解决方案

此问题非常适合正则表达式。函数返回是给定字符串中简单正则表达式模式的第一个匹配之前的字符的位置。如果没有找到匹配项，该函数返回字符串的长度。该功能可以与LEFT功能组合，以提取匹配之前的文本。 （使用LEFT是必要的，因为为简单起见，此函数不实现子匹配。

以下公式将提取您的样本数据中的产品名称：

  = LEFT（A1，regexmatch（A1，\（| \ / -  | \ *））
  

打破匹配模式\\ \\（| \ / | - | \ *：

 \匹配一个空格，后跟一个左括号
 [反斜杠转义（，正则表达式中的一个特殊字符）
 
|表示一个替代模式，以匹配
 
\ /匹配一个正斜杠（/）
 
 - 匹配空格后跟破折号（ - ）
 
\ *匹配一个空格后跟一个星号（*）
  

要了解有关正则表达式的更多信息，请参阅此正式表达离线教程是网络上许多可用的教程之一。

为了使函数正常工作，您需要设置对Microsoft VBScript正则表达式的引用5.5。为此，请从VBA IDE中选择工具/引用，并检查该项目，这将很好地列在长的引用列表中。

 函数regexMatch（文本As String，rePattern As String）
'对SO帖子的响应16591260 
'从代码http://www.macrostash.com/2011/10/08/ 
'simple-regular-expression-tutorial-for-excel-vba /。 
 
 Dim regEx As New VBScript_RegExp_55.RegExp 
 Dim matches As Variant 
 
 regEx.pattern = rePattern 
 regEx.IgnoreCase = True'True to ignore case 
 regEx.Global = False'只返回第一个匹配
 
如果regEx.Test（text）然后
设置matches = regEx.Execute（text）
 regexMatch = match（0）.FirstIndex 
 Else 
 regexMatch = Len（text）
 End If 
 
结束功能
  pre> 
 
 
以下子例程将字符串提取应用于指定数据列中的每个单元格，并将新字符串写入指定的结果列。虽然可以在数据列中调用每个单元格的函数，但这会在每次调用该函数时产生编译正则表达式（适用于所有单元格）的开销。为了避免这种开销，子程序将匹配函数分成两部分，循环中的模式定义通过数据单元格，循环中的模式执行。
  Sub SubRegexMatch（）
'对SO帖子的响应16591260 
'从活动的指定源
'列中的每个数据单元的字符串内容中提取工作表在正则表达式的第一个
'匹配的左侧的字符，并将新字符串写入指定结果列中的相应
'行。 
'在参数部分中设置正则表达式，源列，结果列和第一个
'数据行
'正则表达式匹配代码已从http：//www.macrostash改编。 com / 2011/10/08 / 
'simple-regular-expression-tutorial-for-excel-vba / 
 
 Dim regEx As New VBScript_RegExp_55.RegExp，_ 
 matches As变量，_ 
 regexMatch As Long'位置的字符*刚刚*匹配
 Dim srcCol As String，_ 
 resCol As String 
 Dim srcRng As Range，_ 
 resRng As Range 
 Dim firstRow As Long，_ 
 lastRow As Long 
 Dim srcArr As Variant，_ 
 resArr（）As String 
 Dim i As Long 
 
'参数
 regEx.Pattern =\（| \ / |  -  | \ *'要匹配的正则表达式
 regEx.IgnoreCase = True 
 regEx .Global = False'只返回第一个m atch发现
 srcCol =A'源数据列
 resCol =B'结果列
 firstRow = 2'设置为第一行数据
 
带ActiveSheet 
 lastRow = .Cells（Cells.Rows.Count，srcCol）.End（xlUp）.Row 
设置srcRng = .Range（srcCol& firstRow& ：& srcCol& lastRow）
设置resRng = .Range（resCol& firstRow&：& resCol& lastRow）
 srcArr = srcRng 
 ReDim resArr（1 To lastRow  -  firstRow + 1） 
对于i = 1到srcRng.Rows.Count 
如果regEx.Test（srcArr（i，1））然后
设置matches = regEx.Execute（srcArr（i，1））
 regexMatch = matches（0）.FirstIndex 
 Else 
 regexMatch = Len（srcArr（i，1））'如果没有匹配，返回原始字符串长度
如果
 resArr（i）= Left（srcArr（i，1），regexMatch）
 Next i 
 resRng = WorksheetFunction.Transpose（resArr）'将结果分配给工作表
结束
结束Sub 
  
 
In a specific column, I want to search for a specific character in cells...say "(" or "/". Once this character is found in a cell, I want to extract the part from the beginning of the string upto the point that this character is found, in the cell adjacent to it.

E.g. a few values in the column could look like -
Samsung (india)
Samsung/Dhamal
Blackberry (chikna)
Blackberry/Kala Anda
iPhone - egypt
iPhone 5 * yeda
The output will look like -
Samsung
Samsung
Blackberry
Blackberry
iPhone
iPhone 5
NOTE: The cell values in that specific column, are not static, have no pattern, may contain other special characters as well, are not of a specific length.
解决方案
This question is well suited for regular expressions. The following function returns the position of the character preceding the first match of a simple regex pattern in a given string. If no match is found, the function returns the length of the string. The function can be combined with the LEFT function to extract the text preceding the match. (The use of LEFT is necessary because, for the sake of simplicity, this function does not implement submatches.)

The following formula would extract the product names in your sample data:
  =LEFT(A1,regexmatch(A1," \(|\/| -| \*"))
Breaking down the match pattern " \(|\/| -| \*":
  " \("  matches a space followed by a left parenthesis 
         [the backslash escapes the "(", a special character in regular expressions] 

  "|"    signifies an alternative pattern to match

  "\/"   matches a forward slash (/)

  " -"   matches a space followed by a dash (-)

  " \*"  matches a space followed by an asterisk (*).
To learn more about regular expressions, see this regular expression tutorial, one of many available on the web.

In order for the function to work, you will need to set a reference to Microsoft VBScript Regular Expressions 5.5. To do this, select Tools/References from the VBA IDE and check this item, which will be well down the long list of references.
  Function regexMatch(text As String, rePattern As String)
      'Response to SO post 16591260
      'Adapted from code at http://www.macrostash.com/2011/10/08/
      '    simple-regular-expression-tutorial-for-excel-vba/.

      Dim regEx As New VBScript_RegExp_55.RegExp
      Dim matches As Variant

      regEx.pattern = rePattern
      regEx.IgnoreCase = True 'True to ignore case
      regEx.Global = False    'Return just the first match

      If regEx.Test(text) Then
         Set matches = regEx.Execute(text)
         regexMatch = matches(0).FirstIndex
      Else
         regexMatch = Len(text)
      End If

  End Function 
The following subroutine applies the string extraction to each cell in a specified data column and writes the new string to a specified result column. Although it would be possible to just call the function for each cell in the data column, this would incur the overhead of compiling the regular expression (which applies to all cells) each time the function was called. To avoid this overhead,  the subroutine splits the match function in to two parts, with the pattern definition outside the loop through the data cells, and the pattern execution inside the loop.
  Sub SubRegexMatch()
      'Response to SO post 16591260
      'Extracts from string content of each data cell in a specified source
      '   column of the active worksheet the characters to the left of the first
      '   match of a regular expression, and writes the new string to corresponding
      '   rows in a specified result column.
      'Set the regular expression, source column, result column, and first
      '   data row in the "parameters" section
      'Regex match code was adapted from http://www.macrostash.com/2011/10/08/
      '   simple-regular-expression-tutorial-for-excel-vba/

      Dim regEx As New VBScript_RegExp_55.RegExp, _
          matches As Variant, _
          regexMatch As Long     'position of character *just before* match
      Dim srcCol As String, _
          resCol As String
      Dim srcRng As Range, _
          resRng As Range
      Dim firstRow As Long, _
          lastRow As Long
      Dim srcArr As Variant, _
          resArr() As String
      Dim i As Long

      'parameters
      regEx.Pattern = " \(|\/| -| \*"    'regular expression to be matched
      regEx.IgnoreCase = True
      regEx.Global = False               'return only the first match found
      srcCol = "A"                       'source data column
      resCol = "B"                       'result column
      firstRow = 2                       'set to first row with data

      With ActiveSheet
          lastRow = .Cells(Cells.Rows.Count, srcCol).End(xlUp).Row
          Set srcRng = .Range(srcCol & firstRow & ":" & srcCol & lastRow)
          Set resRng = .Range(resCol & firstRow & ":" & resCol & lastRow)
          srcArr = srcRng
          ReDim resArr(1 To lastRow - firstRow + 1)
          For i = 1 To srcRng.Rows.Count
              If regEx.Test(srcArr(i, 1)) Then
                  Set matches = regEx.Execute(srcArr(i, 1))
                  regexMatch = matches(0).FirstIndex
              Else
                  regexMatch = Len(srcArr(i, 1)) 'return length of original string if no match
              End If
              resArr(i) = Left(srcArr(i, 1), regexMatch)
          Next i
          resRng = WorksheetFunction.Transpose(resArr) 'assign result to worksheet
      End With
  End Sub


                        
这篇关于VBA，在列中搜索特定字符，将字符串提取到该字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！