Forum Discussion
Solved
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.
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, "."))
17 Replies
It depends on what do you mean under sentence (how they are separated within the text) and on which Excel version/platform you are.
- 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.
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, "."))
@HansVogelaar I have a similar situation and the formula does not seem to work and not sure there is one that could work. I have info in excel cells within a sheet that are separated by returns, sometimes 2 - 4 in each cell:
Example:
Description: the info goes hereOwner: John Doe
Objective: pull as much data as possibleHow can I extract the data from each cell into columns (Description, owner, etc.)?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
This is excellent! Thank you. Would you happen to know how to make them populate adjacent rows as opposed to columns???
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...
