Tips for translating in Excel
Your client has asked you to translate directly in Excel. What’s more, they’ve set a strict limit of 50 characters per target cell.
Excel does not have an in-built character checker. And nobody has time to sit and count characters on the screen – so what to do?
One answer is to create simple character-counting cells or alerts to keep you aware of the limit while you work. Here’s how to set them up.
Setting up character counters in Excel
1. Select a cell to use as the character counter
2. Insert the formula =LEN(cell) and replace cell with the target cell
In this example, the target cell is A2 and the character counter is B2. So =LEN(A2) is the active formula in B2:
The target character count (including spaces and numbers) now shows next to the target cell.
To apply the same formula to consecutive cells – say, B2 to B5 – just highlight that range, put the root formula in B2 and press CTRL+Enter. The bracketed part of the formula will then update for each cell.
Setting up character alerts in Excel
You might prefer a more visual warning when you exceed the character count. This is where alert cells can be handy.
1. Select the cell in which you want the character alert to appear
2. Insert the formula =LEN(cell)<51 and replace cell with the target cell
In the example below, A2 is your target cell, which should contain no more than 50 characters. The alert cell is B2. So you need to insert =LEN(A2)<51 into column B2:
If the target text fits within the character limit, TRUE will show in column B. If not, it will say FALSE.
Bonus tip: How to compare column lengths
Now, what if you need to check the length of your target text against the source?
Easy. Just create an alert cell with this formula, making sure to replace target cell and source cell with the correct values:
=IF(LEN(target cell)>LEN(source cell),”Too long”,”OK”)
Got any Excel translation tips of your own? You’re welcome to share them with us on Twitter, leave a post on our Facebook page or drop us an email.
Productivity, Technical tips and tricks, Translation tips
character counter, Excel tips, productivity tips, tips for translators
