Results 1 to 10 of 10

Split text into array using multiple delimiters

Threaded View

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2010
    Posts
    7

    Split text into array using multiple delimiters

    I'm writing a macro to spell check each word in a cell and simply change the color of misspelled words. The defualt delimiter is Chr(32), which is a space, but I'd like to also use Chr(10), line break, as our people often use soft breaks. Without the Chr(10) incorporated, if a misspelled word is at the beginning or end of a line, the correctly spelled word adjacent to it, if only separated by a line break, will also be highlighted. Here is my code:

    Sub Word_By_Word()
    
    Dim oRange As Excel.Range
    
    Set oRange = ActiveCell
    
    Application.ScreenUpdating = True
    
    Dim lCellHighlightColor As Long
    lCellHighlightColor = xlNone
    
    Dim lWordHighlightColor As Long
    lWordHighlightColor = vbRed
    
    On Error GoTo 0
    
    Dim oCell As Object
    Dim iLastRowProcessed As Integer
    iLastRowProcessed = 0
    
    For Each oCell In oRange
    
    If ((bColumnMarker = True) And _
    (iLastRowProcessed <> oCell.Row)) Then
    
    iLastRowProcessed = oCell.Row
    Cells(oCell.Row, iColumnToMark) = ""
    End If
    Rows(oCell.Row).Select
    
    Dim bResultCell As Boolean
    bResultCell = True
    
    If (Len(oCell.Text) < 256) Then
    bResultCell = Application.CheckSpelling(oCell.Text)
    End If
    
    Dim iTrackCharPos As Integer
    iTrackCharPos = 1
    
    Dim vWords As Variant
    vWords = Split(oCell.Text, Chr(32), -1, vbBinaryCompare)
    Dim i As Integer
    
    For i = LBound(vWords) To UBound(vWords)
    
    Dim iWordLen As Integer
    iWordLen = Len(vWords(i))
    
    Dim bResultWord As Boolean
    
    bResultWord = Application.CheckSpelling(Word:=vWords(i))
    
    If (bResultWord = False) Then
    
    If (iWordLen > 1) Then
    Dim iWL As Integer
    For iWL = iWordLen To 1 Step -1
    If (Not (Mid(vWords(i), iWL, 1) Like "[0-9A-Za-z]")) Then
    vWords(i) = Left(vWords(i), (iWL - 1))
    Else
    Exit For
    End If
    Next iWL
    If (Mid(vWords(i), iWL, 1) = "s") Then
    
    vWords(i) = Left(vWords(i), (iWL - 1))
    End If
    
    bResultWord = Application.CheckSpelling(Word:=vWords(i))
    End If
    End If
    
    If (bResultWord = True) Then
    
    If ((Len(vWords(i)) > 0) And (vWords(i) = UCase(vWords(i)))) Then
    
    Dim iHyphenPos As Integer
    iHyphenPos = InStr(1, vWords(i), "-")
    If (iHyphenPos > 0) Then
    
    Dim vHyphenates As Variant
    vHyphenates = Split(LCase(vWords(i)), "-", -1, vbBinaryCompare)
    Dim iH As Integer
    
    For iH = LBound(vHyphenates) To UBound(vHyphenates)
    bResultWord = Application.CheckSpelling(Word:=vHyphenates(iH))
    If (bResultWord = False) Then
    
    Exit For
    End If
    Next iH
    End If
    End If
    End If
    
    If (bResultWord = False) Then
    bResultCell = False
    
    oCell.Characters(iTrackCharPos, iWordLen).Font.Bold = False
    oCell.Characters(iTrackCharPos, iWordLen).Font.Color = lWordHighlightColor
    Else
    
    oCell.Characters(iTrackCharPos, iWordLen).Font.Bold = False
    oCell.Characters(iTrackCharPos, iWordLen).Font.Color = vbBlack
    End If
    
    iTrackCharPos = iTrackCharPos + iWordLen + 1
    
    Next i
    
    If (bResultCell = True) Then
    
    oCell.Interior.ColorIndex = xlColorIndexNone
    Else
    
    oCell.Interior.Color = lCellHighlightColor
    
    If (bColumnMarker = True) Then
    Cells(oCell.Row, iColumnToMark) = sMarkerText
    End If
    End If
    
    Next oCell
    
    End Sub
    I highlighted the split command in red, which is where I need to incorporate the space and line breaks as delimiters. Any help would be great!
    Last edited by TKFRMjarvis; 02-25-2014 at 03:58 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sort Text into Rows by Multiple Delimiters/Values
    By BoostThis in forum Excel General
    Replies: 6
    Last Post: 11-22-2013, 12:31 PM
  2. Use of multiple delimiters under Text To Columns
    By ryandonn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-20-2013, 03:05 PM
  3. Excel Split Single Cell Array into Multiple Cells
    By devinpitcher in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-07-2012, 07:13 AM
  4. Replies: 3
    Last Post: 08-06-2009, 04:27 AM
  5. Use of multiple-character delimiters under Text To Columns
    By longtalker in forum Excel General
    Replies: 3
    Last Post: 03-16-2009, 06:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1