How to translate a formatted spreadsheet
One of my clients is multinational. Yes — I’m flexing.
She works in food. She is literally doing my dream job. But I love her because she gives me stuff I can use to trade for food, so in a way, we’re doing the same thing.
Alicia (shit — have I blown the NDA?) asked me how I could translate content from a formatted spreadsheet. With the minimum of fuss.
The trouble about Excel is there’s no easy way (here’s looking at you, VBA) to export a sheet as some kind of cryptic data file, containing all the formatting rules and styles in Markdown or whatever. There totally should be — get on it, Sundar/Satya.
Until then we need to figure out an alternative. The web isn’t replete with suggestions, based on my limited search abilities, and nor does AI come up with the goods.
It does, however, do the work. With a bit of tweaking and teasing.
I feel like I’m a recipe writer. A lot of bullshit about my life, and eventually, what you actually came for.
How you do it
See that sexy sheet? Yes, it has rudimentary formatting applied. So we’re going to show you how to retain that, and have this content translated. There is so much I could do with this sheet. I want to separate the ingredients into separate columns, and do something indecent with the Difficulty and Cooking time data so it’s a really useful file. But that is not the name of the game, here. We are in Robot, Translate! mode.
- So imagine this is the spreadsheet. You want to export this as a CSV file because right now, unless you’ve got a premium AI plan or using a dedicated app which will cost you at least $6 for this work, you’re gonna struggle uploading an XLSX file.
- CSV in fingertips, I typically open it up in Notepad (I honestly have no idea what you Mac bros do) and then Ctrl+A to select all, Ctrl+C to copy, and get ready for the next bit
- You wanna go to Claude because honestly, all the others just fuss and muss and we want a translation, not a conversation.
- I’m using this prompt: Can you take this data and translate it into English, retaining the separators so that I can import it into an Excel sheet as a CSV file. Here is the data:
At this point, I squirt in the CSV data by hitting Ctrl+P which means paste. Depending on the amount of data and (possibly) day of the week, Claude will either create a file or just show all the data in the prompt window. - You’ll then get a translated file in the output window on the right. Hit the disc icon (can you believe it’s 2024 and we’re still using a 3.5" floppy to rep the Save action?) and download it. To make life easier for Google I then rename that file with the suffix of .csv so there’s no ambiguity over what I want to do with it when it’s uploading.
- I then go to Google Drive, hit the + New button top right, and select File upload, choosing this .csv file.
7. Drive then asks you how you want to open that CSV. Choose Sheets.
8. Then to get the formatting from the non-translated version of this file, you simply:
- highlight all cells (including headings) in the table from the original file,
- hit Edit > Copy (Ctrl+C)
- highlight the table in your new file, and either use Ctrl+Alt+V or old-school hit Edit > Paste Special > Format only
This will restore the formatting to your beautifully translated spreadsheet.
9 (optional). Of course you can always double check the translation by copying all the data from your translated CSV file (generated at the end of step 4 on this guide) into, say, Perplexity, or Gemini, or ChatGPT, and asking for it to be checked while retaining the formatting and separators so the corrected version can overwrite the data in your actual CSV file. Bonus points if you ask this second LLM if it can also create a report of all the changes it’s made — although chances are, because it’s such a smarty pants, it will already have done this.
This was a wonderful epiphany for me. It’s a workaround. In about 3 weeks this hole will have been plugged and my work in ruins. But until then, I hope this shows you how AI can help somehow with anything you’re doing to stay productive and get hired/promoted.
