How to splitt texts into words? (collecting word and compounds)

Hello,
i have wordlists which i would like to enhance through texts. For that purpose i need a macro which reads a text and splitts it into words and put the words into three columns. Lets assume the column where the words are supposed to be put is columns A,C and E. The column A should contain single words containing no spaces from Text, column C should contain compound words which have one space between them and in column E those compounds which have two spaces. Now i will put my existing wordlist in column G before i start splitting texts. The macro should put only those words from text into column A,C and E which dont already exist in columns A,B,C,D,E,F and G. In Column B i will place the corrected or checked words from column A. In column D i will type the corrections and checked versions of those compunds existing in column C. And the column F will contain the compound words which are corrected and checked from Column E.

Column A--> single words splitted from text through macro (containing no spaces)
Column B--> corrected and checked versions of words from column A through user
Column C--> compound words containing one space splitted from text through macro (for example:"etwas möchten" or "do something")
Column D-->corrected version of C through user
Column E-->compound words containing two space splitted from text through macro (for example:"sich uerberzeugen lassen" or "down in dumps")
Column F-->corrected E
Column G--> existing dictionary wordlist through user

Signes like ".", ":",";", "/" or quotation signe itself should be seen as seperator. And the words or compounds splitted from the text should not contain these signs.

The text will be put in another sheet in the same excel file. Lets call that sheet "text". The macro should search for words through whole sheet ignoring empty cells or columns.

None of the columns should be filtered or ordered. The columns shound not be deleted as well. That way the user wont have to correct the same word each time cus the corrected as well as the original words will be there.

A thread with similar purpose was made sent by AdamDay as well, where rylo has posted a good solution already.

similar thread

Thank you very much for each help.

Hello.

Could you attach a small sample workbook showing what you're starting with and what you want to end up with?

Hi Stephen,

thank you for your answer.

here is an example:

wali,
I am working on it and have adapted some code by Krishnakumar I found at Ozgrid to do the list of single words (delete the entries in col A and run the macro). Will come back to the rest when I have time if somebody else hasn't leapt in.

As far as I can see it doesn't delete anything but have a look at the attached which is a bit further along.

I understand the point about not deleting, and that is easily remedied. However, the latest attachment confuses me a little.

For example, why isn't "If" in column A and why are 'the' and 'marines' in column B? What's the rationale?

I will have a look at this but tbh I might take a little time. It's covering areas I'm not overly familiar with so it's useful to me but not necessarily quick for you!

Wali,

The attachment is where I've got to. It's been interesting this one, I got a bit carried away playing with some new toys.

Anyway, press the button to run the macro. I've tested it a little but best if you try it with proper data you can see if it does what you want; there are a couple of things on which I'm a little unclear which you may pick up anyway.

And I have to ask - hope you don't mind: how do you use this and what are you trying to achieve?

Hi Stephen,
thanks your very much. Well it looks now better. There are still some parts which dont work but i am sure with time we will find solution for that as well.


_________________________________________________________________

how do you use this and what are you trying to achieve?

I will give it a try to explain for what i need it and hope you have time and patience to read it through:

I have written a dictionary: Pashto-German. The total number of target pashto words is about 16000 and each of them is translated into German. Well the number of 16 000 target words is kinda poor for a real dictionary cus normaly dictionaries have much much more words. So now i am always on search for new pashto words. It means i need those Pashto words which i dont already have. Now the question is where to get them from? Well there are hundreds auf internet websites in pashto lanugage ( news, calture, ...... ) where i could take the texts from and process them through your macro.

I will put the whole list of my existing pashto words from my dictionary in Column G. And each day when i get time i will go to Pashto internet sites and copy nice texts from there. I will paste those texts into your macro and process them. Now the macro will search for those words in that text which donot already exist in column G. In column A the macro will put the single words, in column C the compounds with one space and in column E the compounds with 2 spaces.

In comparism to english and other languages the pashto language most the times have single words which have many spaces. For example the english word "fireworks" means "āteš bāzí" in pashto. And its not allowed to write the word without space. The same way there are words which have two or more spaces in them. Thats why i need the columns C and E.

Now logically the macro will put also some words (strings) in columns which are useless or misspelled and cant be used for a dictionary. Before i process the next text i will always first fish out the useful words from column A, C and column E. The usefull ones or the corrected ones from column A will be put by me in column B, from Column C to D and from E to column F. Now i wont delet those nonesence/misspelled words from original columns A,C and E as well.

And the next time i will put another text into my macro. There will be alreday words existing in all columns from the last text. Now its important that the older words are not delted or reordered. And the macro should add only those words or compounds which are not available in whole list already. It means that the macro has to compare each entry with all columns before it adds it to column A,C or E. Only new and unique words or compounds should be added to the columns which do not already exist.

The use of not deleting the misspelled or nonesence words and compounds in columns A,C and E would be that i wont have to correct the same mistake of the original writer or of the macro again and again. For example the macro puts a word "attachmnt" in column A. I wont delet it thoug cus the next time there is again the same version of misspelling the macro wont add it cus it will already be existing in column A.

I hope that i could now make it clear why and what for i need the macro.

__________________________________________________________________

What is yet to be done:

1) The macro is unfortunately not comapring the words put into Columns with all entries in other columns. It means that it also adds words into columns A,C and E which already exit in column G.
2) Each time the macro is run it deletes all old entries from columns C and E. The macro is supposed not to do that. The older entries should either be deleted nor reordered.
3) The macro shows runtime error 13
4) every entry made by the macro should be unique in whole sheet including all columns

I am uploading an example file. The red marked columns are those which contain controlled and checked words. The remaining three are those which contain words and compounds found by the macro and which are not controlled. Now the macro is supposed not to delet any entry in any column. Nor should it reorder them. Only new entries should be made only they dont exist.

I hope that i am not doing unfair to you by this problem. I really need it!

Thank you very much buddy

Wali - thanks for the explanation. I understand, sort of! I must admit I had to look up Pashto, but I had heard of Pashtun which I guess has a similar root. Do you think you've written the only Pashto-German dictionary in the world?!

The way I envisaged this working is that words would be entered in the text sheet and then you would run the macro and update the dictionary. If you run the macro without anything in text sheet then I guess it will error. Why would you do this? I should have incorporated some error-trapping and I can do that easily enough.

It has to delete what is already there in order to update with the new dictionary. But the words are reinstated, not permanently deleted. However, perhaps somebody will know of another way.

Also, I was regarding column A as the dictionary so it checks if words are in that column. How are cols A and G different? I think I can adapt the code to check against column G too.

Two steps forward, one step back...

Hello Stephen,
fortunately there are already few pashto dictionaries but not Pashto-German. Yes you are right: My Pashto-German is the first dictioanry at all having Pashto as target and German as source language.
By the way Pashto is the language of 45 Million Pashtun People living in Afghanistan, Pakistan and india. Pashto belongs to the indoeuropean langauge family and is one of the 2000 world languages faced with disapearing for ever. Each of 8 languages i speak is nice but Pashto is the sweetest one. I think that its one of the most beautiful languages in the world.

How are cols A and G different?

Well column G contain of my original wordlist. It contains wordlist put by the user and already checked and correct. And the column A consists of words suggested by the macro which are not checked and may contain many mistakes.

I think I can adapt the code to check against column G too.

Yeah! that would be great. But please not only for G but also for B,C,D,E and F. Because each new made entry should be unique through all columns A - G.

SHG has written a very nice code for a similar problem. I wished we could also use parts of his code. Here is the link:
Dictionary Object codes by ryLo and SHG

But unfortunately i cant use SHG's macro for pashto. May be we could put your and shg's code together and solve my problem with it.

Thank you very much for your help buddy

wali

Can you just attach a file showing before/after w/o zipped ?

Hi Jindon,
thank you very much for your interest. Here is a file:

wali3.xls

Please read the comments in first rows of the columns.

thanks

try

Please Login or Register  to view this content.

thanks Jindon,

unfortunately i cant load the macro. It shows me "runtime error 1004"

Did you try it on your sample workbook?

Probably Sheet names are incorrect...

OOps...
try this one

Please Login or Register  to view this content.

sorry but showing still the error

Jindon, i am sorry but its sill not running. What am i doing wrong. The sheet names are right. BUt it eventhough shows runtime error

OOOOOOOOps,
Range("a1").CurrentRegion

Please Login or Register  to view this content.

Jindon,
it runs but doesnt give the needed results. Please read the comments to the cells in worksheet "text".
wali4.xls

OK, wali

I need to go off line now, so I will look at it tomorrow...

wali

I forgot to tell you that I can only "see" your file, but I can not do anything else at all.
So I can not run the macro on your file.
Can you send me the file before/desired result and the file with the result of the code ?

Hi Jindon,

here is the file containing before and after worksheets.

wali5.xls

How about

Please Login or Register  to view this content.

nothing happens when i run the macro.

Then you don't have any word(s) in question... I guess...

i tried everything but nothing happens. the names of sheets are ok. And there new words in text sheet. But no new entries are made.

Originally Posted by wali

i tried everything but nothing happens. the names of sheets are ok. And there new words in text sheet. But no new entries are made.

That's odd,

I only changed the bold part in the code, so it should work since it was working somehow.

sorry for this late answer. I was trying to find out what was wrong with my text. I found following things:

Your macro recognise only those cells of column A in "text" worksheet which have already the form: no space, one space or two spaces. As soon as there is a cell which contains of a text string larger than two spaces the macro ignores it. Now in worksheet text complet texts will be pasted may be having dozens of lines in one cell. Therefore the macro itself has to splitt the cell content into: single, one space and two space entries and put them in worksheet "words" if they dont already exist.

When i put the follwoing three entries in column A in "text" sheet:
Jindon--->this is correctly put into column A in "words" sheet
Jindon is--->this is correctly put into column C in "words" sheet
Jindon is ok--->this is correctly put into column E in "words" sheet

but as soon as there is a line in column A in "text" sheet like:
Jindon is very ok--->macro ignores it and cant splitt it

Now the macro is supposed to split this "Jindon is very ok" into Jindon, is, very, ok and put it to column A and Jindon is, is very, very ok for column C and Jindon is very,is very ok into column E.

It was not functioning as i wrote my last anwer cus i had put a complete sentences into the cells of column A in "text" sheet.

The macro is supposed to read the cells in "text" and splitt the words into named three catagories and put them into "words" worksheet if they dont already exist there.

Now the macro is supposed to split this "Jindon is very ok" into Jindon, is, very, ok and put it to column A and Jindon is, is very, very ok for column C and Jindon is very,is very ok into column E.

?
Does this logic apply to the cell with 4 or more words ?

Yes, if a cell contains more than four words then they should be splitted. Well i will copy complete texts from websites and paste them into the "text" worksheet. The text will most the times devide itself into many cells and columns of "text" worksheet. And the macro must be able to deal with it.

Even if a cell contains two words they must be splitted:
Jindon is---> Jindon, is for column A and Jindon is for column C

One more thing. If there is a signe like "." or ";" or "(" or ")" then the macro should not combine the words before the signe with those following the signe. For example there is following string in " text" sheet.

Please Login or Register  to view this content.

Well the upper sentence must be splitted into single words for column A and into two words for column C.

Column C -->Compounds made, made from, from content, conten in,*, And only, only if, if they.....

* the word "text" starts with singn """ and therefore it should not be combined with the word before it "in". The same behaviour must be done with words for column E.

It means that new combination must be started if there is one of the signes: " , . , ) , ( or ,

wali

Can you just give me a sample data with puctuations of before/after ?_

Hi Jindon,

here is a small example:

wali5.xls

thanks

Hi Jindon,
does the last file answer your question?

wali

Sheet "text" A1 is the string that should be break into single, double, triple words?

yes! tex sheet is a string and is supposed to be splitted into one, two and three words.

Thanks

try

Please Login or Register  to view this content.

Hi Jindon,
thank you very much. Unfortunately i cant make it run. There was one ")" missing in an if line and it was shown red. But as i added the singn ")" it was black again.

Please Login or Register  to view this content.

I cant make it run yet. It shows the error "sub or function not defined"

It doesnt show any error now. But also not splitting the text.
No words are added to the columns in "words" sheet.=> not working

...
Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Jindon, i am realy sorry! but not working yet. It doesnt add words to the columns.

OK
Can you return with the message?

Please Login or Register  to view this content.

Jindon,
when run, the macro shows me the message "before" and "afeter". The difference is that in "after" it shows the strings without the signs.
It shows error if only the first row has a string. It works only if at least one row below the first row in "text" contains a string. => yes it shows the messagebox

But still the same problem. Its not adding words to the columns.=> not adding words yet :-(

OK
How about?

Please Login or Register  to view this content.

ok it works but only for two and three words column. No words are added to the A column. Can you plz fix that as well. thanks

Thanks alot. It works. It adds words to all three columns.

But there is one problem remaining. It adds duplicates which should not be.
If words already exist there they are not added. But if one word is more times there in the string in "text" worksheet its added multiple times to the columns in "words". => if one word is added once then it should not be added again!!

everthing is working well. thanks a million times! i dont know how to thank you. Thanks alot for your patience. thanks a million times!!!!!!