How to split a single cell into separate rows copied down

[nick8888]

How do I split a single cell into separate rows copied down. My delimiter is “.”

For example: in one single cell I have the text:

There are times when okay does not feel right. You need to stand firm. Let it go. Be very strong. And, when you least expect it, everything will work out. Say hello to a new day.


I need the text cell split into separate down row cells using the delimiter "period" with the space removed text alignment Left . Like this:

There are times when okay does not feel right.
You need to stand firm.
Let it go.
Be very strong.
And, when you least expect it, everything will work out.
Say hello to a new day.

Can someone write me a macro or function?? I’m new to Excel.

Thanks, Nick nick2010@hotmail.com

 

[btadams]

here's a quick & dirty macro :

Sub SepSentences()
Dim cell As Range

    On Error Resume Next
    Application.ScreenUpdating = False
    Selection.TextToColumns Destination:=Range(Selection.Address), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlToRight)).Clear
    Application.ScreenUpdating = True
    For Each cell In Selection
        cell.Value = cell.Value & "."
    Next cell
End Sub

P.S. Welcome to the board. However, it's not a good idea to post your e-mail address on any message board.

 

[nick8888]

Sorry for the delay in getting back. Thank you so much for doing this marco. It works great. I don’t know if this is possible. Can the macro be written do that it inserts new rows instead of using the existing rows? Also, when there is a space behind the ‘period’ can this space be removed so the sentences are horizontal aligned left?

 

[jindon]

Hi
try

Sub test()
Dim r As Range, a(), n As Long
ReDim a(1 To Rows.Count,1 To 1)
For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
   If InStr(r.Value,".")>0 Then
      x = Split(r.Value,".")
      For Each e In x
         n = n + 1
         a(n,1) = Trim(e) & "."
      Next
   Else
      n = n + 1
      a(n,1) = r.Value
   End If
Next
Range("a1").Resize(n) = a
End Sub