SOLVED

Extracting sentences from a cell

Copper Contributor

Hi.  I have a cell full of sentences.  I need to extract each sentence into new adjacent cells.  Thank you. 

12 Replies
best response confirmed by hmx876 (Copper Contributor)
Solution

@hmx876

Does each sentence end in a full stop (point)? If so, you could use Data > Text to Columns, with . as delimiter.

HansVogelaar_1-1693062977338.png

If you have Microsoft 365, you can use a formula:

 

=TRIM(TEXTSPLIT(A1, "."))

@hmx876 

It depends on what do you mean under sentence (how they are separated within the text) and on which Excel version/platform you are.

@Hans Vogelaar 

Maybe, but not exactly. For example

"I spent 10.25. Okay for now"

i need help

@Sergei Baklan 

How about

 

=TRIM(TEXTSPLIT(A1, ". "))

@muhammadwaseem 

Why don't you start new discussion here https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral and explain what is your question.

I asked can you check

@Hans Vogelaar 

Yes, with space it's much better.

@Hans Vogelaar 

 

This is excellent!  Thank you. Would you happen to know how to make them populate adjacent rows as opposed to columns???

Hi Sergei, they are separated by a period; I copied and pasted from Word to an Excel cell and I need the sentences to be separated in adjacent cells in a column (not a row). The advice from @hans above is excellent and appreciated, but I now need to know how to organize them into adjacent cells in a column.

@hmx876 

The TEXTSPLIT formula would be

 

=TRIM(TEXTSPLIT(A1, , ". "))

 

(Note the extra comma)

 

For older versions of Excel, there is no really easy method, as far as I know. You could use Text to Columns, then copy the result and paste/transpose it, or use the TRANSPOSE function...

 

HansVogelaar_0-1693072025102.png

Thank you. 🙏🏼
1 best response

Accepted Solutions
best response confirmed by hmx876 (Copper Contributor)
Solution

@hmx876

Does each sentence end in a full stop (point)? If so, you could use Data > Text to Columns, with . as delimiter.

HansVogelaar_1-1693062977338.png

If you have Microsoft 365, you can use a formula:

 

=TRIM(TEXTSPLIT(A1, "."))

View solution in original post