Split sentences at fullstops

1. Split sentences at fullstops

Dear All,
I am using Excel 2013 and need a formula or a UDF to split long sentences at fullstops (period).

A sample data is attached herewith to show the problem in Col A and the expected result in Col B.

Any help would be greatly appreciated.

TIA
Rehana

2. Re: Split sentences at fullstops

Hi rehana,

Here is an answer in C1 and pull it down.
Formula:
`Please Login or Register  to view this content.`

See it in the attached: Split data at fullstops Answer.xlsx

3. Re: Split sentences at fullstops

Dear Marvin,
Thanks for the formula.
But it does not work for other cells.
Can it work for any data in Column A?

4. Re: Split sentences at fullstops

I'm a huge fan of PowerQuery.

M Code:

``Please Login or Register  to view this content.``

5. Re: Split sentences at fullstops

You would need to modify MarvinP's formula to reference the row were the text being split is.

B1: =TRIM(MID(SUBSTITUTE(\$A\$1,".",REPT(" ",1000)),(ROW()-ROW(\$A\$1))*1000+1,1000))&"."

B7: =TRIM(MID(SUBSTITUTE(\$A\$7,".",REPT(" ",1000)),(ROW()-ROW(\$A\$7))*1000+1,1000))&"."

B11: =TRIM(MID(SUBSTITUTE(\$A\$11,".",REPT(" ",1000)),(ROW()-ROW(\$A\$11))*1000+1,1000))&"."

6. Re: Split sentences at fullstops

Dear Ali
Thanks for the code. But I got the formula to work from Andy Pope.

7. Re: Split sentences at fullstops

Dear Andy
Thanks for the modified formula.
Works great

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1