Divide text after dot

[Zetori]

Hi Gurus

I have huge chunk of text in one cell and within the text every sentence is ending with dot. Is there function to divide each sentence to separate rows. In this case I can’t use Text to Columns.

Text cell:
This is text: numerical value = 1 whatever N/A.Different text here: something: 1233 - No.Something new here: CCBBGG. Separate line: Text=N/A 1111.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Result:<o></o>
Row1: This is text: numerical value = 1 whatever N/A<o></o>
Row2: Different text here: something: 1233 - No<o></o>
Row3: Something new here: CCBBGG<o></o>
Row4: Separate line: Text=N/A 1111<o></o>

Thank you!<o></o>

 

[Sektor]

Source cell is B1.

[COLOR="Blue"]Sub[/COLOR] Divide()

    [COLOR="Blue"]Dim[/COLOR] arr [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], rng [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]Set[/COLOR] rng = Range("B1")
    
    arr = Split(rng, ".")
    
    [COLOR="Blue"]For[/COLOR] i = 0 [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        Cells(i + 1, "A") = Trim(arr(i))
    [COLOR="Blue"]Next[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Zetori]

That was fast! Thank you very much! Worked like a dream.

 

[Sektor]

Not at all!