Merge rows into one based on full stop

Question

I have a table in the following format.

---

all that is

---

well known for

---

let be apples.

---

abs kdjhkj kfhksh sh

---

kjsfhkshgkh dh.

---

I want the rows to merge based on the fullstop, whenever a full stop comes, a new row should be created untill next full stop occurs. example

---

all that is well known for let be apples.

---

abs kdjhkj kfhksh sh kjsfhkshgkh dh.

---

I see we can merge by n number of rows into one using inbuild tools. But I have a huge list, I cannot go around and do that for each set. Any solution, in code or through excel or libreoffice calc will be helpful. though I can try macro but not preferring that. Anyways if that is the only way to achieve it then why not.

Answer 1

I think there is no way to archive this with excel function. i try to create a code fulfill your needs:

Code:

Option Explicit

Sub test()

    Dim str As String
    Dim i As Long, LastRowA As Long, LastRowC As Long

    With ThisWorkbook.Worksheets("Sheet1")

        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To LastRowA

            If InStr(1, .Range("A" & i).Value, ".") > 0 Then

                If str = "" Then
                    str = .Range("A" & i).Value
                Else
                    str = str & " " & .Range("A" & i).Value
                End If

                LastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row

                If LastRowC = 1 And .Range("C1").Value = "" Then
                    .Range("C" & LastRowC).Value = str
                Else
                    .Range("C" & LastRowC + 1).Value = str
                End If

                str = ""

            Else

                If str = "" Then
                    str = .Range("A" & i).Value
                Else
                    str = str & " " & .Range("A" & i).Value
                End If

            End If

        Next i

    End With

End Sub

Results: