excel split text into 1 column

[dattmuffy]

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 

[#2]

@dattmuffy 

can you share the workbook?

[#3]

@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 

[#4]

@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.

[#5]

@dattmuffy 

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

dattmuffyExample.xlsx 

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

[#6]

@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

[#7]

@Yea_So will this work

[#8]

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

[#9]

@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.

[#10]

@Yea_So will it open

[#11]

@dattmuffy 

what are the important words?

a, is, of, are etc include them?

[#12]

yes every character

[#13]

@dattmuffy 

its a big project.

The goal is to isolate the unique words, letters, numbers etc. and get their count?

[#14]

yes

[#15 · Accepted solution]

@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

[#16]

to separate the numbers from the text use the ISNUMBER() function

[#17]

i didnt know that
thanks!

[#18]

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

[#19]

so i have successfully figured out the unique thing and that should keep me pretty busy for now thank you so much i have been very impressed with the amount of help from you on here!
i will chip away at this and ask for more help later thanks
clocking out

[#20]

@dattmuffy 

good just post your question on here when you get to the next step

cheers