Duolingo is the most popular way to learn languages in the world. Best of all, it's 100% free!

https://www.duolingo.com/toussaintlou

Language Lists in Excel

toussaintlou
  • 17
  • 13
  • 13
  • 12
  • 7
  • 6
  • 6

I posted this on my blog, but I thought maybe many who are on this website, may also keep phrases lists in an Excel format, so let me share this on the discussion forum. I like to use Excel, and I am a very proficient user in my profession. Last time I looked, there were 351 Excel functions, and I use a really large portion of them. When I started collecting phrases from websites, I would naturally put them into Excel because I can manipulate them easily from there. As an example, I would start with copying and pasting the French – English phrases into column A of a spreadsheet. Pretty simple. Now I wanted to separate the French part into column B, and the English part into column C.

Since I have a dash (with a space on each side), question marks and exclamation marks dividing the data, I cannot use a single formula.

These are the formulas that I use. Since I have a header in cells, A1, B1 and C1 that say Phrase, French, and English respectively, I start my formulas in cell B2. The first compound sentence is in cell A2, so the formula I would place in cell B2 is:

=LEFT(A2,FIND("! ",A2,1)-0) for an exclamation point =LEFT(A2,FIND("? ",A2,1)-0) for a question mark =LEFT(A2,FIND(" – ",A2,1)-1) for a dash

Very simple. The next column, C2, where the English part will be, is a bit trickier, and there are two formulas that will work. The first is:

=MID(A2,FIND("! ",A2)+2,LEN(A2)) for an exclamation point, =MID(A2,FIND("? ",A2)+2,LEN(A2)) for a question mark, =MID(A2,FIND(" – ",A2)+2,LEN(A2)) for a dash,

And the other way is to use:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2,SEARCH(" ",A2,1)+1)) for a dash only

Once you have established the formulas, you can "fill down" or copy and paste them as needed into rows with exclamation points, question marks and dashes.

Why separate the phrases? Well, for me, with separate columns, I can insert blank columns, or hide columns, so that I can make either the French of English column visible, and test myself on a column of phrases.

As an example, take the list of phrases here:

http://duolinguist.wordpress.com/2014/05/06/150-french-sentences-400-verbs/

I've included a picture of the Excel formulas found in the cells.

Try to copy and paste the list into a column on a sheet in Excel, and reference the picture.

There are two ways you can go astray. The first is not paying attention to the cell being used by the formula, like when you use the above example for manipulating cell A2, and place the formula in cell B3 or C3 without changing the formula to show A3 instead of A2, and the other is that you think you have a dash, when you have a hyphen. In that case just copy what is in your sentence, into the formula.

Once you set it up the first time, you'll be able to manipulate the phrases in Excel, and be able to copy the data, unformatted, into Word or Powerpoint, make PDF's, etc.

You may even use the technique for other projects at school or be inspired to teach yourself more about Excel functions. They are very helpful in life, and if you are proficient in Excel, you will definitely be more attractive, and live longer.

http://office.microsoft.com/en-us/excel-help/excel-functions-by-category-HP005204211.aspx

Bon Chance.

4 years ago

6 Comments


https://www.duolingo.com/Fazac
Fazac
  • 24
  • 22
  • 18
  • 13
  • 11

This makes no sense to me at all. I can see why you would want the English phrase in a column and the French phrase in a column, so you can test yourself. But why go to all the trouble of typing both the French and English into one cell, then using formulas to split them into two separate cells. Why not just type them into two separate cells to start with?

4 years ago

https://www.duolingo.com/toussaintlou
toussaintlou
  • 17
  • 13
  • 13
  • 12
  • 7
  • 6
  • 6

Because often I do not type them. I copy and paste them from other websites. Like:

http://www.linguanaut.com/english_french.htm

If I come upon a huge list of words, complete with french accent marks, I can pull the list into my Excel spreadsheet within minutes and manipulate the words as needed.

Read this blog post:

http://mnemotechnics.org/forums/learning-the-1000-to-2000-most-common-words-in-languages-2451.html

This person would love to know what I just posted, and the collaborative, time consuming project that they see, can be completed in five minutes by someone using these formulas.

4 years ago

https://www.duolingo.com/toussaintlou
toussaintlou
  • 17
  • 13
  • 13
  • 12
  • 7
  • 6
  • 6

https://www.duolingo.com/comment/3655303

Nice list...wish I could just copy, paste and manipulate this list in Excel. Oui?

4 years ago

https://www.duolingo.com/HappyEvilSlosh
HappyEvilSlosh
  • 25
  • 25
  • 23
  • 22
  • 21
  • 13
  • 13
  • 11
  • 11
  • 11
  • 10
  • 10
  • 9
  • 7
  • 4
  • 3
  • 2
  • 2
  • 2
  • 10

You'll be even more attractive and live even longer if you learn perl. ;) In which case you can do it all at once in about the same number of characters. For a CSV formatted file use

<pre>s/(^(.*)(([!?])|\ -)\ (.*)$)/$1,$2$4,$5/; </pre>

on each line. With the added bonus of not having to sell your soul (or children to afford their software) to Micro$oft.

4 years ago

https://www.duolingo.com/MultiLinguAlex
MultiLinguAlex
  • 25
  • 25
  • 17
  • 13
  • 10
  • 6
  • 5
  • 5
  • 4
  • 2
  • 2
  • 2

Nice, but I did not understand it completely. Could you please upload such an excel sheet anywhere so that we can download it or could you post a screenshot with and without the formulas at least?

4 years ago

https://www.duolingo.com/toussaintlou
toussaintlou
  • 17
  • 13
  • 13
  • 12
  • 7
  • 6
  • 6

I edited it a bit, to be clearer, and linked to a word list on my blog, where I have also place a picture of the formulas as they appear in the cells.

4 years ago