Forum Discussion

dattmuffy's avatar
dattmuffy
Copper Contributor
Aug 28, 2021
Solved

excel split text into 1 column

how do i select a range of many words and sentences and split the text into each and every word and then sort all of them into 1 column and then count the frequency that each word is used like a glos...
excel
  • Yea_So's avatar
    Yea_So
    Aug 28, 2021

    dattmuffy 

     

    do this for all your columns:

    When you're done

    Do a power query on each of the two columns in the image above, load as a connection

    when you have done all the queries on all letters, do an append query on all the queries so they'll all be in one column.  Keep in mind that excel has a limit of 1 million rows so you might have to break them up by alpha/numeric segments.

     

    cheers

     

Yea_So's avatar
Yea_So
Bronze Contributor
Aug 28, 2021

dattmuffy 

 

can you share the workbook?

dattmuffy's avatar
dattmuffy
Copper Contributor
Aug 28, 2021

Yea_So  yes! im sorry im new to this its my first post how would i do that ?

is there some user information i would need from you?

i believe ive already attached the file to the post also 

thanks 

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Aug 28, 2021

    dattmuffy 

     

    Here is an example of why you need to share your workbook:

    https://1drv.ms/x/s!AiUZUhiQtF3FguNrlX7MZigdiFbXYA?e=ibFH2o 

     

    If you look at the formula in column B

    =IF(D2<0,"",IFERROR(RIGHT([@[LOAD DESCRIPTION]],LEN([@[LOAD DESCRIPTION]])-(FIND(" ",[@[LOAD DESCRIPTION]],D2+5))),RIGHT([@[LOAD DESCRIPTION]],LEN([@[LOAD DESCRIPTION]])-(FIND(" ",[@[LOAD DESCRIPTION]],D2+2)))))&IF(ISNUMBER(OFFSET(ParseTable[@SUM],0,8)),LEFT([@[LOAD DESCRIPTION]],FIND(" ",[@[LOAD DESCRIPTION]])-1),IFERROR(LEFT([@[LOAD DESCRIPTION]],D2),""))

     it parses the text in column A

    the consistency of the formula is very dependent on the order and the consistency of the length of the sentences on each row and how many words to parse in each row

     

      • dattmuffy's avatar
        dattmuffy
        Copper Contributor
        Aug 28, 2021
        1st sheet is the pasted law
        the sheet a is column a from the text to columns function
        and so on and so on i have been dissecting it column by column at a time
  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Aug 28, 2021

    dattmuffy 

     

    If you give an image the only thing to do is to look at it. 

    If you're inquiring about one formula an image will do, however you're talking about separating words in a paragraph within sentences, not to mention if there are non visible system codes that can only be removed making assumptions that its there by creating a formula to replace that non visible system code with a visible one.

     

    The reason I am asking if you can share the workbook is many.

    1. how and where are the paragraphs (in rows? columns?)

    2. what is the maximum words per row?

    etc etc etc...

     

    The only way to find out in order to formulate an accurate assessment to suggest a solution is to see the actual problem.  Without being able to assess the actual problem your guess is as good as mine.

Resources