Counting extracted single words from list.

[Alex11]

I am attempting to count words extracted from unstructured data. To understand my current method please consider my formulas as indicated below:

="^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","^"),".","^"),"?","^"),"!","^"),";","^"),":","^"),"""","^"),"'","^"),"(","^"),")","^"),",","^"),CHAR(13),"^"),CHAR(10),"^"),CHAR(160),"^")," ","^"),"[","^"),"]","^"),"/","^"),"'","^"),"–","^")&"^"

I use the output from above to obtain unique words by using text to data using ^ as a delimiter and sorting them 1 per row in a single column and removing duplicates.

I then use the following formula to obtain a count

=COUNTIF(Master!$C$2:$C$2335,"*^"&A2&"^*")

Please consider this methodology and indicate how I may improve or make it more efficient.

Thank you

 

[JvdV]

You could make this much more efficient depending on your version of Excel. Nesting substitutes is old-fashioned for those using newest versions of Excel.

It would be a bit easier to help if you have some sample data to work with including expected results.

 

[vtito_site]

What is unstructured data? Is it a string? Or is it a text file? Or is it something else?
What is a word? Is it a string separated by spaces? Or is it a string separated by '^' ? Or something else?
Do you need to count any one specific word? Or do you need to count for each word in a line or in a file the number of occurrences in this line or text file?
What should be the output? One number for one word? Or two columns, in one of which there will be words, and in the other their number?

 

[Fluff]

@Alex11 what version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Alex11]

Latin phrases temp.xlsx
	A	B	C	D
1	Latin	Translation	Latin	Translation
2	(oremus) pro invicem	(Let us pray), one for the other; let us pray for each other	^^oremus^^pro^invicem^	^^Let^us^pray^^^one^for^the^other^^let^us^pray^for^each^other^
3	["...", ...] dixit	["...", ...] said	^^^^^^^^^^^^^^dixit^	^^^^^^^^^^^^^^said^
4	[Dominica] in albis [depositis]	[Sunday in Setting Aside the] White Garments	^^Dominica^^in^albis^^depositis^^	^^Sunday^in^Setting^Aside^the^^White^Garments^
5	[in] veritate et caritate	in truth and charity	^^in^^veritate^et^caritate^	^in^truth^and^charity^
6	a bene placito	from one well pleased	^a^bene^placito^	^from^one^well^pleased^
7	a caelo usque ad centrum	from the sky to the center	^a^caelo^usque^ad^centrum^	^from^the^sky^to^the^center^
8	a capite ad calcem	from head to heel	^a^capite^ad^calcem^	^from^head^to^heel^
9	a contrario	from the opposite	^a^contrario^	^from^the^opposite^
10	a Deucalione	from or since Deucalion	^a^Deucalione^	^from^or^since^Deucalion^
11	a falsis principiis proficisci	to set forth from false principles	^a^falsis^principiis^proficisci^	^to^set^forth^from^false^principles^
12	a fortiori	from the stronger	^a^fortiori^	^from^the^stronger^
13	a maiore ad minus	from the greater to the smaller	^a^maiore^ad^minus^	^from^the^greater^to^the^smaller^
14	a mari usque ad mare	from sea to sea	^a^mari^usque^ad^mare^	^from^sea^to^sea^
15	a minore ad maius	from the smaller to the greater	^a^minore^ad^maius^	^from^the^smaller^to^the^greater^
Master

Cell Formulas
Range	Formula
C2:D15	C2	="^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","^"),".","^"),"?","^"),"!","^"),";","^"),":","^"),"""","^"),"'","^"),"(","^"),")","^"),",","^"),CHAR(13),"^"),CHAR(10),"^"),CHAR(160),"^")," ","^"),"[","^"),"]","^"),"/","^"),"'","^"),"–","^")&"^"

I have Office 365

 

[Alex11]

Latin phrases temp.xlsx
	A	B	C	D
1	Latin	Translation	Latin	Translation
2	(oremus) pro invicem	(Let us pray), one for the other; let us pray for each other	^^oremus^^pro^invicem^	^^Let^us^pray^^^one^for^the^other^^let^us^pray^for^each^other^
3	["...", ...] dixit	["...", ...] said	^^^^^^^^^^^^^^dixit^	^^^^^^^^^^^^^^said^
4	[Dominica] in albis [depositis]	[Sunday in Setting Aside the] White Garments	^^Dominica^^in^albis^^depositis^^	^^Sunday^in^Setting^Aside^the^^White^Garments^
5	[in] veritate et caritate	in truth and charity	^^in^^veritate^et^caritate^	^in^truth^and^charity^
6	a bene placito	from one well pleased	^a^bene^placito^	^from^one^well^pleased^
7	a caelo usque ad centrum	from the sky to the center	^a^caelo^usque^ad^centrum^	^from^the^sky^to^the^center^
8	a capite ad calcem	from head to heel	^a^capite^ad^calcem^	^from^head^to^heel^
9	a contrario	from the opposite	^a^contrario^	^from^the^opposite^
10	a Deucalione	from or since Deucalion	^a^Deucalione^	^from^or^since^Deucalion^
11	a falsis principiis proficisci	to set forth from false principles	^a^falsis^principiis^proficisci^	^to^set^forth^from^false^principles^
12	a fortiori	from the stronger	^a^fortiori^	^from^the^stronger^
13	a maiore ad minus	from the greater to the smaller	^a^maiore^ad^minus^	^from^the^greater^to^the^smaller^
14	a mari usque ad mare	from sea to sea	^a^mari^usque^ad^mare^	^from^sea^to^sea^
15	a minore ad maius	from the smaller to the greater	^a^minore^ad^maius^	^from^the^smaller^to^the^greater^
Master

Cell Formulas
Range	Formula
C2:D15	C2	="^"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",","^"),".","^"),"?","^"),"!","^"),";","^"),":","^"),"""","^"),"'","^"),"(","^"),")","^"),",","^"),CHAR(13),"^"),CHAR(10),"^"),CHAR(160),"^")," ","^"),"[","^"),"]","^"),"/","^"),"'","^"),"–","^")&"^"

Latin phrases temp.xlsx
	A	B
1	Word	Count
2	in	204
3	non	168
4	et	150
5	est	117
6	ad	95
7	ex	69
8	pro	50
9	per	46
10	veritas	42
11	sine	39
12	quod	38
13	qui	33
14	sed	33
15	ut	33
16	de	32
17	omnia	31
18	semper	31
19	Dei	30
20	Deus	28
21	a	27
22	pax	26
Latin words

Cell Formulas
Range	Formula
B2:B22	B2	=COUNTIF(Master!$C$2:$C$2335,"*^"&A2&"^*")

 

[Alex11]

What is unstructured data? Is it a string? Or is it a text file? Or is it something else?
What is a word? Is it a string separated by spaces? Or is it a string separated by '^' ? Or something else?
Do you need to count any one specific word? Or do you need to count for each word in a line or in a file the number of occurrences in this line or text file?
What should be the output? One number for one word? Or two columns, in one of which there will be words, and in the other their number?

Unstructured data in this instance is a phrase or sentence within a cell.
A word is either the first character to the first space, the text enclosed by two spaces or the last space to the end of the cell (excluding punctuation).
I want to count every instance of a word in a cell and range.

two columns, in one of which there will be words, and in the other their number

 

[Fluff]

To start with you could use this on the Master sheet

="^"&TEXTJOIN("^",,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),","),CHAR(160),","),{",",".","?","!",";",":","""","'","(",")"," ","[","]","/","'","-"},CHAR(10)))&"^"

 

[Alex11]

To start with you could use this on the Master sheet

="^"&TEXTJOIN("^",,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),","),CHAR(160),","),{",",".","?","!",";",":","""","'","(",")"," ","[","]","/","'","-"},CHAR(10)))&"^"

Thank you

Is there a better way to split my "words" considering that I need to strip punctuation, non printable characters etc.
The other challenge is that I want to identify by means of countif an entire "word" not a portion thereof. That is the purpose of my ^ inclusions.
I am looking for a cleverer way to the differentiate the "app" from the "apple" if that makes sense?

 

[Fluff]

That's what the formula I suggested does, at almost half the length of yours.