Aug 28 2021 12:00 PM
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
Aug 28 2021 01:37 PM
@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
Aug 28 2021 01:46 PM
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.
Aug 28 2021 01:59 PM
Here is an example of why you need to share your workbook:
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
Aug 28 2021 02:02 PM
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
Aug 28 2021 02:04 PM
@Yea_So will this work
Aug 28 2021 02:07 PM
Aug 28 2021 02:12 PM
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.
Aug 28 2021 02:25 PM
Aug 28 2021 02:28 PM
its a big project.
The goal is to isolate the unique words, letters, numbers etc. and get their count?
Aug 28 2021 03:13 PM
Solution
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
Aug 28 2021 03:29 PM
Aug 28 2021 05:00 PM
Aug 28 2021 06:33 PM
Aug 28 2021 06:35 PM
Aug 28 2021 03:13 PM
Solution
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