[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parse a cell with sentences

Posted on 2011-04-25
20
Medium Priority
?
516 Views
Last Modified: 2012-05-11
How can I parse a cell in column B by Sentences.
example in Cell B2 I have the the following:
1. This is a test.
2. This is the other test.

What I want is to have one cell with:
1. This is a test.

And in the cell below:
2. This is the other test.
0
Comment
Question by:cd_morris
  • 9
  • 5
  • 3
  • +2
20 Comments
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463740
If you sort on that cloumn, you should get what you want.
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463741
BTW: are the cell's numbered as in your example?
0
 
LVL 50
ID: 35463761
Hello,

if the two sentences are in one cell and they look like in your example, then there is probably a line feed character between the sentences, so the second sentence appears on a new line.

you could use a formula along these lines

=LEFT(B2,FIND(CHAR(10),B2)-1)  -- to get the first sentence
=MID(B2,FIND(CHAR(10),B2)+1,99) -- to get the second sentence

cheers, teylyn
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50
ID: 35463778
Another possibility is to use the Text to Columns dialog with the Delimited option. Tick Other and enter

Alt-0010

as the delimiter character and click Finish. You can then copy the resulting cells and paste special/transpose them to arrange them underneath each other.

With the text to columns option, you will manipulate the source data. With the formula option as above, the source data will remain unchanged, but the formula option does not work well for larger data sets.
0
 
LVL 4

Author Comment

by:cd_morris
ID: 35463786
jocasio123: Sorting will not split the sentences in to different cell.
teylyn: I have a long column to do this to so a vba solution would perhaps work best.

example.xlsx
0
 
LVL 50
ID: 35463794
What do you mean with "long column"? Many cells with two sentences? A cell with many sentences?

Please provide a data sample that is more representative of your issue.

cheers, teylyn
0
 
LVL 4

Author Comment

by:cd_morris
ID: 35463798
This code works but it splits after the first period:

Sub SplitBySentence()
    ' splits a paragraph into multiple rows by sentence

    Dim r       As Range
    Dim iR      As Long
    Dim nR      As Long

    Dim asSent() As String
    Dim nSent   As Integer
    Dim iSent   As Integer

    Dim sCAR    As String
    Dim sCDR    As String

    Dim iPrd    As Integer    ' start of ". " in string
    Dim iCln    As Integer    ' start of ": " in string

    If Selection.Areas.Count > 2 Then
        MsgBox "Cannot do this to a multi-area selection."
        Exit Sub
    ElseIf Selection.Columns.Count > 2 Then
        MsgBox "Cannot do this on a multi-column selection."
        Exit Sub
    End If

    ' doing this for multiple cells would louse up the selection _
      range unless you start from the bottom and worked upward _
      hence the (1,1) below. Fix later if worthwhile.

    Set r = Intersect(Selection, ActiveSheet.UsedRange)
    nR = r.Rows.Count

    r.Select
    For iR = nR To 1 Step -1
        With r(iR, 1)
            .Select
            If Not .HasFormula And Not IsNumeric(.Value) Then
   
                ' replace non-breaking space with space
                sCDR = Replace(.Value, Chr(160), " ")
                ' trim and add a trailing blank:
                sCDR = Application.Trim(sCDR) & " "
               
                If sCDR <> " " Then
                    .ClearContents
                    nSent = 0
       
                    ' parse into substrings ending in ". " or ": "
                    Do
                        iPrd = InStr(1, sCDR, ". ")
                        iCln = InStr(1, sCDR, ": ")
                        If iCln > 0 And iCln < iPrd Then iPrd = iCln
                        sCAR = Left(sCDR, iPrd)
       
                        If sCAR = "" And sCDR <> "" Then
                            sCAR = sCDR
                            sCDR = ""
                        End If
                        If sCAR = "" Then Exit Do
       
                        'Debug.Print "sCAR = " & sCAR
                        nSent = nSent + 1
                        ReDim Preserve asSent(1 To nSent)
                        asSent(nSent) = sCAR
       
                        sCDR = Mid(sCDR, iPrd + 2)    ' one past the space
                        'Debug.Print "sCDR = " & sCDR
       
                    Loop
       
                    .Value = asSent(1)
                    For iSent = nSent To 2 Step -1
                        .Offset(1).EntireRow.Insert
                        .Offset(1).Value = asSent(iSent)
                    Next
                End If
            End If
        End With
    Next
End Sub

0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463828
What separates the two in B2?  Is it a carriage return line feed?
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463842
Something like this would work, but you have to replace the vbCrLf with the delimiter you are using:

Sub SplitCells()
    Dim SeparateCell As Variant
    Dim iIndex As Integer
   
    SeparateCell = Split(Range("B2").Value, vbCrLf)
        For iIndex = LBound(SepCell) To UBound(SeparateCell)
    Cells(intIndex + 1, 3).Value = SeparateCell(iIndex)
    Next
End Sub

This would put the results in column 3 (C) and start at C1
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463843
BTW I had a typo in that:

Sub SplitCells()
    Dim SeparateCell As Variant
    Dim iIndex As Integer
   
    SeparateCell = Split(Range("B2").Value, vbCrLf)
        For iIndex = LBound(SeparateCell) To UBound(SeparateCell)
    Cells(iIndex + 1, 3).Value = SeparateCell(iIndex)
    Next
End Sub

0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463852
You could replace the vbCrLf with Chr(10)

This may work for your purpose.

Good luck!
0
 
LVL 4

Author Comment

by:cd_morris
ID: 35463945
This does not work!
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463965
Did you try:

Sub SplitCells()
    Dim SeparateCell As Variant
    Dim iIndex As Integer
   
    SeparateCell = Split(Range("B2").Value, Chr(10))
        For iIndex = LBound(SeparateCell) To UBound(SeparateCell)
    Cells(iIndex + 1, 3).Value = SeparateCell(iIndex)
    Next
End Sub

What happens when you run it?
0
 
LVL 4

Author Comment

by:cd_morris
ID: 35463968
The code I provided works but how do I get it to split after the second period (.)?
0
 
LVL 15

Expert Comment

by:Juan Ocasio
ID: 35463969
BTW I ran it against what you sent and it works perfectly.  I'm a bit perplexed :-\
0
 
LVL 4

Author Comment

by:cd_morris
ID: 35464067
Sorry, Wrong cell selected.  But how do get it to work for multiply cell in the B range?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35464214
I developed a solution using Regular Expressions.  For more on Regular Expressions, here's a very good overview by the author of the enhanced functions used in this solution, matthewspatrick:  http:/A_1336.html.

This appears to be a very simple solution, where we're looking for "[0-9]." which means a number followed by a period.  The application attached (and see code below) parses the subject string, by using the function RegExpFind command, searching for the n-th occurrance of a number followed by a period.  Once found, it delivers a mid() function on the original string from one instance to the next.  Also a Clean/Trim is performed basically to remove excess spaces and most hidden characters, but for sure the carriage return line feed, which we don't need upon parsing.

See code:

 
Sub BreakLinesDownReplacing()
Dim i As Integer, cPosition As Variant, iPos As Integer
Dim lastStart As Integer
Dim outCursor As Range
Dim stringParse As String

    stringParse = Range("B2").Value
    Set outCursor = Range("B2")
    
    lastStart = 1 'beginning of the string, for starters
    i = 2 'start with second instance position
    Do
        cPosition = RegExpFind(stringParse, "[1-9].", i, False, 1)
        If cPosition = "" Then
            iPos = 0
        Else
            iPos = cPosition
            outCursor.Value = Application.WorksheetFunction.Clean(Trim(Mid(stringParse, lastStart, iPos - lastStart)))
            lastStart = iPos
            Set outCursor = outCursor.Offset(1, 0) 'move on to the right
        End If
        i = i + 1
    Loop Until iPos = 0
    
    'Then, wrap-up, if any leftovers...
    outCursor.Value = Application.WorksheetFunction.Clean(Trim(Mid(stringParse, lastStart, Len(stringParse) - lastStart)))
    
End Sub

Open in new window


See attached, which provides two demo's.  The one at the top is a button you can push to parse anything in "B2" as requested, replacing that and successive rows down, for as many "sentences" (defined as number followed by period to the next number/period combination).  The one in the middle/bottom works by selecting first on a string to parse and pushing the button - with results pasted columns to the right (example, only).

If the carriage return linefeed is the only determining factor, the RegExp search string is much simpler, but the code is similar - this time looking for the line feed as the END of the sentence.  See second button below the first that only parses based on CRLF.  See code, below:


 
Sub BreakLinesDownReplacing_V2()
Dim i As Integer, cPosition As Variant, iPos As Integer
Dim lastStart As Integer
Dim outCursor As Range
Dim stringParse As String

    stringParse = Range("B2").Value
    Set outCursor = Range("B2")
    
    lastStart = 1 'beginning of the string, for starters
    i = 1 'start with second instance position
    Do
        cPosition = RegExpFind(stringParse, "\n", i, False, 1)
        If cPosition = "" Then
            iPos = 0
        Else
            iPos = cPosition
            outCursor.Value = Application.WorksheetFunction.Clean(Trim(Mid(stringParse, lastStart, iPos - lastStart)))
            lastStart = iPos
            Set outCursor = outCursor.Offset(1, 0) 'move on to the right
        End If
        i = i + 1
    Loop Until iPos = 0
    
    'Then, wrap-up, if any leftovers...
    outCursor.Value = Application.WorksheetFunction.Clean(Trim(Mid(stringParse, lastStart, Len(stringParse) - lastStart + 1)))
    
End Sub

Open in new window


See attached demo worksheet & enjoy!

Dave
Breaking-sentences-using-RegExp-.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35464220
>>only parses based on CRLF

should read, "only parses based on LF - linefeed"

Dave
0
 
LVL 15

Accepted Solution

by:
Juan Ocasio earned 2000 total points
ID: 35464262
Try this one.  It'll check every cell in the column and retrieve all data and then spitmit back out to column c



Sub SplitCells()
    Dim SeparateCell As Variant
    Dim eachItem() As String
    Dim iIndex As Integer
    Dim scalar As Integer
    Dim totalScalar As Integer
    Dim x As Integer
    totalScalar = 0
    scalar = 0
    For x = 1 To ActiveSheet.UsedRange.Rows.Count
        If Trim(Range("B" & x).Value) <> "" Then
            SeparateCell = Split(Range("B" & x).Value, Chr(10))
            totalScalar = totalScalar + (UBound(SeparateCell) + 1)
            ReDim Preserve eachItem(totalScalar)
            For iIndex = LBound(SeparateCell) To UBound(SeparateCell)
             eachItem(scalar) = SeparateCell(iIndex)
             scalar = scalar + 1
            Next

        End If
    Next x
    For iIndex = LBound(eachItem) To UBound(eachItem)
    Cells(iIndex + 1, 3).Value = eachItem(iIndex)
    Next
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 35464439
From the sample code posted by cd_morris, it appears that the delimiters are the period and colon. If so, then the following macro will parse the text in each cell in a column, adding rows as necessary.
Sub Splitter()
Dim cel As Range, rg As Range
Dim bFound As Boolean
Dim s As String
Dim i As Long, iStart As Long, j As Long, n As Long, nCol As Long, nn As Long
Dim v As Variant, vSplit As Variant
Application.ScreenUpdating = False
Set rg = Range("B2") 'First cell to be split
nCol = rg.Column
Set rg = Range(rg, Cells(Rows.Count, nCol).End(xlUp)) 'All the data in that column
iStart = rg.Row
n = iStart + rg.Rows.Count - 1 'Last row in data
rg.Replace ".", ".|"    'Use a pipe character as a delimiter. Put one after every period and colon.
rg.Replace ":", ":|"
For i = n To iStart Step -1
    Set vSplit = Nothing
    s = Cells(i, nCol).Value
    vSplit = Split(s & "|", "|")
    nn = UBound(vSplit)
    bFound = False
    For j = nn To 0 Step -1
        If Left(vSplit(j), 1) = vbLf Then vSplit(j) = Mid(vSplit(j), 2)
        If vSplit(j) <> "" Then
            Cells(i + 1, nCol).EntireRow.Insert
            Cells(i + 1, nCol).Value = vSplit(j)
            bFound = True
        End If
    Next
    If bFound Then Rows(i).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub

Open in new window


Brad
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question