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
Last edited by rehana402003; 07-14-2020 at 09:34 AM.
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
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Dear Marvin,
Thanks for the formula.
But it does not work for other cells.
See the attached sample please.
Can it work for any data in Column A?
I'm a huge fan of PowerQuery.
M Code:
Please Login or Register to view this content.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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))&"."
Dear Ali
Thanks for the code. But I got the formula to work from Andy Pope.
Dear Andy
Thanks for the modified formula.
Works great
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks