用于将"&&"，"&&-"，"&-"和数字等符号提取到不同列中的VBA代码

Question

我有一个表格，其中包含诸如" 5670＆amp;＆amp; 2"，" 1281＆amp;＆amp;＆amp; -3＆amp; -5＆amp; 7"，...等。在A列中。

请帮助我以以下方式提取VBA的输出：

对于例如5670＆amp;＆amp; 2 i要求A1单元包含5670，B1单元包含＆amp; amp; amp;，C1 Cell包含2。

对于例如1281＆amp;＆amp; -3＆amp; -5＆amp; 7，我要求A1单元包含1281，B1单元，B1单元包含＆amp;＆amp; - ，C1 Cell包含3，D1 Cell包含＆amp; - ，E1，E1，E1细胞包含5，F1单元包含＆amp; amp;和G1单元包含7。

pls在同一方面有帮助。

谢谢。，

Answer 1

在这里，我尝试编写代码以将数字与非数字分开。数字和非数字被复制到不同的列，例如Excel文本到列。代码有点疯狂，如果您需要，我将提供评论。作为输入ActiveSheet.usedrange.columns（1）。使用。

Option Explicit
Sub SeparateNumbers()
  Dim targetRange As Range
  Dim cellRange As Range
  Dim charIndex As Integer
  Dim oneChar As String
  Dim nextChar As String
  Dim start As Integer
  Dim copiedCharsCount As Integer
  Dim cellValue As String
  Dim columnIndex As Integer
  Set targetRange = ActiveSheet.UsedRange.Columns(1).Cells
  For Each cellRange In targetRange
    columnIndex = cellRange.Column
    start = 1
    copiedCharsCount = 0
    cellValue = cellRange.Value
    If (VBA.Strings.Len(cellValue) <= 1) Then GoTo nextCell
    For charIndex = 2 To Len(cellValue)
      oneChar = VBA.Strings.Mid(cellValue, charIndex - 1, 1)
      nextChar = VBA.Strings.Mid(cellValue, charIndex, 1)
      If VBA.IsNumeric(oneChar) And VBA.IsNumeric(nextChar) Then GoTo nextCharLabel
      If Not VBA.IsNumeric(oneChar) And Not VBA.IsNumeric(nextChar) Then GoTo nextCharLabel
      cellRange.Offset(0, columnIndex).Value = VBA.Strings.Mid(cellValue, start, charIndex - start)
      columnIndex = columnIndex + 1
      copiedCharsCount = copiedCharsCount + (charIndex - start)
      start = charIndex
nextCharLabel:
      If charIndex = Len(cellValue) Then
        cellRange.Offset(0, columnIndex).Value = VBA.Strings.Right(cellValue, charIndex - copiedCharsCount)
      End If
    Next charIndex
nextCell:
  Next cellRange
End Sub

Answer 2

这是另一个代码。作为侧产品，函数文本Splittonumbershother可以独立用作实现相同效果的公式。

为了防止在错误的表格或错误的列或带有废料覆盖的相邻列中意外触发宏，应由用户定义命名范围" start_point"。在同一列中的此范围以下，将处理所有数据，直到第一个空白行。

电子表格示例：http：//www.bumpclub.ee/~jyri_r/excel/extracting_symbols_into_into_columns.xls

选项显式

Sub ExtractSymbolsIntoColumns()
Dim rng As Range
Dim row_processed As Integer
Dim string_to_split As String
Dim columns_needed As Long
Dim counter As Long
row_processed = 1
 counter = 0
  Set rng = Range("Start_point")
    While rng.Offset(row_processed, 0).Value <> ""
      string_to_split = rng.Offset(row_processed, 0).Value
         columns_needed = TextSplitToNumbersAndOther(string_to_split)
          For counter = 1 To columns_needed
            rng.Offset(row_processed, counter).Value = _
              TextSplitToNumbersAndOther(string_to_split, counter)
          Next
         row_processed = row_processed + 1
      Wend
End Sub
Function TextSplitToNumbersAndOther(InputText As String, _
    Optional SplitPieceNumber As Long) As Variant
Dim piece_from_split(100)  As Variant
Dim char_from_input As String
Dim word_count As Long
Dim counter As Long
Dim char_type(100) As Variant
 InputText = Trim(InputText)
   If Not IsNull(InputText) Then
     word_count = 1
      piece_from_split(word_count) = ""
       For counter = 1 To Len(InputText)
         char_from_input = CharFromTextPosition(InputText, counter)
          char_type(counter) = CharTypeAsNumber(char_from_input)
            If counter = 1 Then
              piece_from_split(word_count) = char_from_input
            Else
              If (char_type(counter - 1) = char_type(counter)) Then
                 piece_from_split(word_count) = piece_from_split(word_count) & char_from_input
                   'Merge for the same type
              Else
                 word_count = word_count + 1
                   piece_from_split(word_count) = char_from_input

              End If
            End If
       Next
   End If
  If SplitPieceNumber = 0 Then
    TextSplitToNumbersAndOther = word_count
  Else
      If SplitPieceNumber > word_count Then
         TextSplitToNumbersAndOther = ""
      Else
        TextSplitToNumbersAndOther = piece_from_split(SplitPieceNumber)
      End If
  End If
End Function
Function CharTypeAsNumber(InputChar As String, Optional PositionInString As Long) As Long
   If PositionInString = 0 Then PositionInString = 1
     If Not IsNull(InputChar) Then
       InputChar = Mid(InputChar, PositionInString, 1)
        Select Case InputChar
          Case 0 To 9
            CharTypeAsNumber = 1
          Case "a" To "z"
            CharTypeAsNumber = 2
          Case "A" To "Z"
            CharTypeAsNumber = 3
          Case Else
            CharTypeAsNumber = 4
         End Select
     Else
           CharTypeAsNumber = 0
     End If
End Function
Function CharFromTextPosition(InputString As String, TextPosition As Long) As String
   CharFromTextPosition = Mid(InputString, TextPosition, 1)
End Function

Answer 3

您可以编写UDF（用户定义的功能）以实现目标。您的两个示例是按顺序（上升），以将Excel（a，b，c，d ...）中的相邻列过滤到相邻的列中

因此，从逻辑上假设是正确的，您永远不会有场景，您将不得不将字符串分解为非贴种列？例如1234进入A＆amp;＆amp;转到C，3转到D ...导致A，C，d。

AsumaTing 2：您的拆分串不会超过Excel提供的列。

您可能会尝试的步骤： 1.检查您的字符串不是空的 2.将其按数字以外的字符拆分 3.在每个非数字字符的开始和结尾，您可以继续进行下一个相邻的列。

搜索帮助：将字符串分为excel -vba