Tipp 0172 Text in Spalten verteilen
Autor/Einsender:
Datum:
  Angie
05.02.2006 (Update)
Entwicklungsumgebung:   Excel 2000
Mit der in Excel integrierten TextToColumns-Methode kann eine Spalte mit Zellen, die Text enthalten, analysiert und das Ergebnis in mehreren Spalten ausgegeben werden. Eine sehr ausführliche Beschreibung der TextToColumns-Methode kann der Excel-VB(A)-Hilfe entnommen werden.
 
Ausdruck.TextToColumns(Destination, DataType, TextQualifier,
      ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other,
      OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator)
 
Oftmals kommt es jedoch vor, dass integrierte Methoden nicht ausreichen und dann doch eine eigene Prozedur geschrieben werden muss. Folgendes Beispiel ist ein Lösungsansatz, mit dem Text in einer Spalte in mehrere Spalten verteilt wird, wobei hier u. a. auch angegeben werden kann, ob Spalten eingefügt werden sollen oder ggf. vorhandene Zellinhalte in den Spalten rechts von der zu splittenden Spalte überschrieben werden sollen.
 
Public Sub SplitTextToColumns(ByVal objWks As Worksheet, _
      ByVal nColToSplit As Integer, ByVal strDelim As String, _
      Optional fInsertCols As Boolean = True)

  Dim avarColData As Variant
  Dim avarSplit   As Variant
  Dim avarWksData As Variant

  Dim nLastRow    As Long
  Dim nRow        As Long

  Dim nColsCnt    As Integer
  Dim nItemsCnt   As Integer
  Dim nItem       As Long

  Dim strValue    As String

  On Error GoTo err_Handler

  If Application.WorksheetFunction.CountA( _
          objWks.Columns(nColToSplit).EntireColumn) > 0 Then

    With objWks
      If Len(.Cells(.Rows.Count, nColToSplit).Value) = 0 Then
        nLastRow = .Cells(.Rows.Count, nColToSplit).End(xlUp).Row
      Else
        nLastRow = .Rows.Count
      End If
      avarColData = .Range(.Cells(1, nColToSplit), _
            .Cells(nLastRow, nColToSplit)).Value
    End With

    nColsCnt = 0
    ReDim avarWksData(0 To nLastRow - 1, 0 To nColsCnt)

    For nRow = 1 To UBound(avarColData, 1)
      strValue = avarColData(nRow, 1)
      If Len(Trim$(strValue)) > 0 Then
        If InStr(1, strValue, strDelim) > 0 Then
          'Split-Ersatz für Excel 97 von Tom Ogilvy
          avarSplit = Evaluate("{""" & Application.Substitute( _
                strValue, strDelim, """,""") & """}")
          nItemsCnt = UBound(avarSplit)

          If nItemsCnt - 1 > nColsCnt Then
            nColsCnt = nItemsCnt - 1
            ReDim Preserve avarWksData( _
                  0 To nLastRow - 1, 0 To nColsCnt)
          End If

          For nItem = 1 To nItemsCnt
            avarWksData(nRow - 1, nItem - 1) = avarSplit(nItem)
          Next nItem

        Else
          avarWksData(nRow - 1, 0) = strValue
        End If
      End If
    Next nRow

    If nColsCnt > 0 Then
      With objWks
        If fInsertCols Then
          .Range(.Columns(nColToSplit + 1), _
                 .Columns(nColToSplit + nColsCnt) _
                ).Columns.Insert Shift:=xlToRight
        End If

        .Cells(1, nColToSplit).Resize(UBound(avarWksData, 1) + 1, _
              UBound(avarWksData, 2) + 1) = avarWksData

        .UsedRange.Columns.AutoFit
      End With

    Else
      MsgBox "Die Zellen in Spalte " & nColToSplit & _
             " enthalten nicht das angegebene Trennzeichen '" & _
             strDelim & "', es gab also nichts zu splitten!", _
             vbInformation, Title:="VB-fun-Demo"
    End If

  Else
    MsgBox "Die Spalte " & nColToSplit & " enthält keine Daten!", _
            vbInformation, Title:="VB-fun-Demo"
  End If

exit_Sub:
  On Error Resume Next
  Set objWks = Nothing
  On Error GoTo 0
  Exit Sub

err_Handler:
  MsgBox "Fehler-Nr. " & CStr(Err.Number) & vbCrLf & _
        Err.Description, vbCritical, "VB-fun-Demo"
  Resume exit_Sub
End Sub
 

Windows-Version
95
98/SE
ME
NT
2000
XP
Vista
Win 7
Excel-Version
95
97
2000
2002 (XP)
2003
2007
2010


Download  (19,3 kB) Downloads bisher: [ 1227 ]

Vorheriger Tipp Zum Seitenanfang Nächster Tipp

Startseite | Projekte | Tutorials | API-Referenz | VB-/VBA-Tipps | Komponenten | Bücherecke | VB/VBA-Forum | VB.Net-Forum | DirectX-Forum | Foren-Archiv | DirectX | VB.Net-Tipps | Chat | Spielplatz | Links | Suchen | Stichwortverzeichnis | Feedback | Impressum

Seite empfehlen Bug-Report
Letzte Aktualisierung: Sonntag, 7. August 2011