Over 6300 Digital Flash Cards in less than an hour...
This simple project is a result of being frustrated with Memrise and Quizlet. I don't want the drudgery of spending a great deal of time and energy creating flashcards or similar materials. I want it to exist already, allowing me the time to practice and interface with the material. Since I have collected these words and phrases already, I wanted a way to convert them to a similar study material with as little effort on my part, as possible.
1) Opened MS Excel, set text for Calibri 18 (your choice may differ) 2) Went tohttp://duolinguist.wordpress.com/2014/07/15/manipulating-word-lists-with-parentheses/and copied the list into Excel starting at cell A1 3) Placed the formula "=SUBSTITUTE(MID(A1,FIND("(",A1)+1,255),")","")" in cell B1 4) Placed the formula "=LEFT(A1,FIND(" – ",A1,1)-1)" in cell C1 5) Placed the formula "=LEFT(E1,FIND(" (",E1,1)-1)" in cell D1 6) Placed the formula "=MID(A1,FIND(" – ",A1)+2,LEN(A1))" in cell E1 7) Highlighted cells B1 to E1 down through the final row, and chose "Fill-Down". 8) Now that all of my cells are filled properly, I highlighted all of the rows and columns and set up as follows: Row Height - 130, Column Width - 40, Vertically Centered Text, Horizontally Centered Text, Wrap Text On. 9) On the bottom, increased to 400% Zoom, so that a single cell (example C1) fills the screen fully. Your size may vary due to the size of your screen. 10) Using the up/down/left right arrows on your keyboard, allows you to choose how you wish to move from cell to cell, to simulate flashcards.
I start with column C and go right to see what the English is, then back to the French and try to say it properly, then left to see how I should say it. As you can see, instead of one simple flashcard move, you can use horizontal cells to drill deeper into the word. You can start with the French word, move right to get the pronunciation, move right again to get the English translation, again to see an example sentence, right again to see false cognates/related words/memory aid, etc.
One of the columns may contain a sorting category, like "Verbs", "Kitchen", "Garage", "Supermarket" or other sublist, so that you can data-sort using that column, quickly creating flashcards related to a particular area of study.
Once you start using the spreadsheet, you will realize that you can simply copy the sheet within the file, and size it for another computer screen. I use three computers of varying screen sizes, so I have four sheets. The fourth sheet is the list in normal size, that I continually add to as I read material and use other language study sources. To move the data, I simply alphabetize the data sheet (yes, to expand the sort), and copy to the other three sheets, starting at cell A1, and pasting as "text only" so that the formatting on each sheet remains unaffected.
When I'm focusing on listening skills, I'll start with the pronunciation column and see if I can identify the French, then the translation. If I miss, I can simply click the font color and change it to red, so that I know I have to focus on getting this one right.
Note: You may disagree with the pronunciation, and I find myself listening to audio all the time, and editing this column the most. In fact, you can use Find/Replace to change hundreds of cells with a common speech pattern, in a matter of seconds.
I've been thinking what I'd like to put in column F, and there is room with font size 18, for around three or four sentences. An example sentence, could go there, or an explanation, or a link to a website. Note: I do update the word list on the link, every now and then, so this list could go well beyond 3500 flash cards. Bookmark the page, and come back every few months.
Note: You may find that some of your columns aren't working correctly for new words, and the primary cause is that there are several versions of a dash. The formula is looking for one type, and another type was entered or copied into the first column. You can correct this problem by simply using "Find" and "Replace". Use the dash from the cell that isn't working, and replace it with one that does work, and make sure there is a space before and after..." - ". Replace All...done.
Note: http://french.about.com/library/express/blexdico-a.htm has a little over 2640 French expressions, and I formatted them as flashcards in less than an hour.
Then added, http://en.wiktionary.org/wiki/Appendix:French_idioms to make it to almost 2900. I wish I had done this six months ago.
On my blog, www.duolinguist.wordpress.com, I have the sentences I encountered for each lesson, tagged and categorized. I realize that by copying them into Excel, I could have made flash cards for each lesson, with notes in an adjacent cell.
Excel is a very good text editor for large edits, when you know the right formulas.
Note: What if you want to randomly study your new Excel Flash Cards, instead of the current alphabetic order? You actually only need to perform a few steps to create a randomized list, and how to do this is covered here:
Now, think of what you can do with the INDEXMATCH Function and your word columns:
Make a look-up translator, perhaps? Or better yet, why scroll through your French wordlist to see if a new word is actually a new word that you need to add? Just enter the word in the Index match and let Excel tell you.
Despite not getting any comments, I have seen the interest in completing this project by the number of hits on my blog. To make the French Expressions Flashcards easier, I posted a more detailed entry on my blog, with the preformatted expression list here:
Despite that I do not study French at this time, I want to keep this bookmarked, Good work and congratulations!
I'll definitely use this technique with my next language, which may be Spanish next year. Today my co-worker asked me what that expression was that had a certain word in it, and I just used the Find feature in Excel, to get the answer in seconds. Very handy.
Thanks, have a lingot. I set up a SS following your instructions. The only issue was the " - " wasn't recognized, but copying the space hyphen space from the word list and replacing it in the formula worked.
I've found that if I'm going to add words or expressions to the Excel spreadsheet, doing a find/replace so that the dash used by Excel is the one that replaces all others, helps solve that problem once and for all. I don't know why word programs have a different dash but they do. Imagine what you can do with this list: http://wortschatz.uni-leipzig.de/Papers/top10000fr.txt
It's 10,000 of the most common French words, in a single text column format. Copy, Paste, Data Sort A to Z and it starts looking like something you can work with. I'm going to see what I can do with this list. There is an interesting discussion on how to manipulate these types of lists here:
and there are other languages that have these mega-lists, found on the link above.
I know you can cut and paste into Quizlet. I have cut and paste my own documents into Quizlet. It is just too bad it took me over a year to realize that I wasted a lot of time retyping everything I had already typed or written down. Quizlet actually allows you to import sets from other people too, although I haven't actually done this. The only negative is that Quizlet has no advanced search engine, so sometimes it takes a long time to find a good set of flashcards.