SOLVED

# Extracting sentences from a cell

Copper Contributor

# Extracting sentences from a cell

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

# Re: Extracting sentences from a cell

@hmx876

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

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

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

# Re: Extracting sentences from a cell

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

# Re: Extracting sentences from a cell

Maybe, but not exactly. For example

"I spent 10.25. Okay for now"

i need help

# Re: Extracting sentences from a cell

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

# Re: Extracting sentences from a cell

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

# Re: Extracting sentences from a cell

Yes, with space it's much better.

# Re: Extracting sentences from a cell

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

# Re: Extracting sentences from a cell

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.

# Re: Extracting sentences from a cell

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...

# Re: Extracting sentences from a cell

Thank you. 🙏🏼
1 best response

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

# Re: Extracting sentences from a cell

@hmx876

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

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

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