0

I have text data that I want to parse. The data is split into cells but each cell contains more that just one sentence. I want to isolate the sentences into individual cells to make parsing easier.

I used the formula below to count sentences in a given cell and it works well, so I'd assume ". " can be used to demarcate sentences.

=LEN(D2)-LEN(SUBSTITUTE(D2,".",""))

So now I would like to split the content of these cells containing text, and copy each sentences (separately, in their own cell) to a row on a different sheet. Each row would correspond to one text cell in the previous sheet.

See the illustration

Any idea of how I should proceed? Thank you!

  • 1) What exactly is your desired output? have each sentence in its own cell on the same line, or have them in new rows below the original? 2) Just a quick note that !, ? and ... can also end sentences. – cybernetic.nomad Apr 6 '18 at 16:34
  • each sentence in its own cell. all the sentences from the same (original) cell on the same row. The output on a sheet distinct from the input. – supagleech Apr 6 '18 at 17:32
  • 1
    Have you tried using Text to Columns? – cybernetic.nomad Apr 6 '18 at 17:43
  • I just did, thanks! The issue now is that there are in fact ? demarcating sentences in my data... I can use the Custom option of Text to Columns for sentences ending in . , but once the sentences are split it seems that I can't use Text to Columns a second time to split sentences ending in ? - I'm thinking of using tokens such as *. for . and **. for ? so I could split and then recover the specific symbols via the find and replace command. would there be a more straightforward option? – supagleech Apr 6 '18 at 21:40
  • Why would you not be able to run the Text to Columns operation a second time, in order to split on ? ?. Just select the whole output area of the first run and enter the question mark in the Custom option. – Tom Brunberg Apr 7 '18 at 7:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.