How to split a single cell into separate rows copied down

nick8888

New Member
Joined
Sep 22, 2006
Messages
2
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. :biggrin:

Thanks, Nick nick2010@hotmail.com
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
here's a quick & dirty macro :

Code:
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

New Member
Joined
Sep 22, 2006
Messages
2
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,652
Messages
5,660,138
Members
418,552
Latest member
Idiocracy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top