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 glossary so that i may become familiar with large documents very fast like a bible concordance? God bless you 

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

     

22 Replies

  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Aug 28, 2021
    to separate the numbers from the text use the ISNUMBER() function
      • Yea_So's avatar
        Yea_So
        Bronze Contributor
        Aug 29, 2021
        It will return a TRUE or FALSE i.e.
        ISNUMBER(A1)
        If cell A1 has a value "Sample" it will return a FALSE
        If cell A1 has a value 500 it will return a TRUE
  • Yea_So's avatar
    Yea_So
    Bronze Contributor
    Aug 28, 2021

    dattmuffy 

     

    or I can share an image of that workbook maybe you can figure out what the formula is doing in the image?  Let me know if you're still unconvinced that you need to share your workbook.

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

    dattmuffy 

     

    If you can figure out what the formula is doing in that spreadsheet and apply it into your workbook then there's your solution.

     

    cheers

    • 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

         

Resources