Home » Cantonese » How To Make A Phrase List into Two Columns in Excel

Kieran on Twitter


~Chinese / 中文

~Japanese / 日本語

"Notes on Democracy" Arundhati Roy

~Korean / 한국어
《그렇습니까? 기린입니다》박민규
《소나기》 황순원

~Finished / 読了 / 已读
"Factory Girls" Lesley Chang
"Your Republic is Calling You" Kim Young-ha
"River Town" Peter Hessler
"Oracle Bones" Peter Hessler
"Country Driving" Peter Hessler
「火の鳥9」 手塚治虫
"Inside the Kingdom" Robert Lacey
"A Room of One's Own" Virginia Woolf
《倾城之恋 》张爱玲
「1973年のピンボール」 村上春樹
"One Foot In Eden" Ron Rash

How To Make A Phrase List into Two Columns in Excel

Sometimes when I want to import a list of phrases for a language into Anki, the formatting gets in the way.

For example, if the phrases are arranged in a column with the translation under each phrase, how do you separate the target language phrases into a separate column? It’s a little trickier than I anticipated, but this can be done with Excel.

I will show you how to select only even rows or select only odd rows in Microsoft Excel, which I will use to take the phrases from the Wikitravel Cantonese phrasebook (I’ve also done this with Korean slang) and separate the translations into a separate column, so you can import the whole thing into Anki as a tab-delimited UTF-8 txt file produced by Excel.

1. Copy and paste the data into Excel


2. Use the “=ISEVEN() or =ISODD() function, combined with a ROW() function that references any cell in that row.” (click here for a tutorial).


3. As per step 2, type =ISEVEN(ROW(A1)) into the cell B1. It will turn to FALSE.

4. Copy the function in cell B1 into all the cells in the column.


5. Turn on the filter by selecting any cell in the column, then clicking the Filter button under Sort & Filter in the Data tab. (Source: techrepublic.com)


6. Next, “Click the new column’s filter dropdown and choose False or True.” (The “dropdown” is the little down-facing arrow in a blue box that should appear in the column.) (Source: techrepublic.com)

CantoExcelTutorial67. Click the little boxes at the bottom of the popup window that say TRUE or FALSE to show only odd or even rows.

CantoExcelTutorial78. Copy and paste from there into another Excel file, and you’re set.

CantoExcelTutorial99. You can then use Save As to save the new Excel file as a UTF-16.

10. Open the .txt file in TextEdit, copy it, and save it again as a UTF-8 .txt file (the only kind Anki can read). Ready to import!

What do you think? Please let me know if these directions aren’t clear enough!


Your thoughts?

Kieran Maynard

Kieran Maynard

Writer, translator, researcher, traveler specializing in Japanese and Chinese literature.

View Full Profile →

%d bloggers like this: