How to find formulas that require translation in Excel spreadsheets

November 06, 2012

What makes Microsoft Excel so powerful is its ability to handle formulas. Most formulas do not require translation as they produce a number. However, from time to time you will come across an Excel spreadsheet with text formulas, i.e. formulas that produce translatable text.

Unlike numeric formulas, text formulas may, for instance, take a cell value and add a word to its end, e.g.:

  A B C D E
1 Product Price Q-ty Total Total (text)
2 Bread 2 2 4 4 USD
3 Sour cream 3,5 2 7 7 USD
4 Milk 2 5 10 10 USD

In this example, the text in the last column is generated by taking the value from column D and adding " USD" to its end.

Another common example is a logical formula that inserts text based on a condition, e.g. "YES" if a value in another column is greater than 4, and "NO" if not.

To translate your spreadsheet fully, you will need to find all cells with such formulas and translate the formulas.

Finding cells with text formulas

Use the guidelines below to find such cells in different versions of Microsoft Excel:

  • In Excel 2007 and later:

    On the Home tab, click Find & Select button located in the Editing group, then select Go To Special... from the menu:

    Find & Select menu (Excel 2007 and later)

    You will see Go To Special dialogue. Select "Formulas" option and remove checkboxes against all items except "Text", then click OK:

    Go To Special dialogue (Excel 2007 and later)

  • In Excel 2003 and earlier:

    Click Edit -> Go To...

    In the Go To dialogue that opens, click Special... button:

    Go To dialogue (Excel 2003 and earlier)

    You will see Go To Special dialogue. Select "Formulas" option and remove checkboxes against all items except "Text", then click OK:

    Go To Special dialogue (Excel 2003 and earlier)

When you click OK, Excel will select all cells that contain text formulas in the current sheet. If no cells are found, you will see a message "No cells were found".

Before you begin editing formulas of the selected cells, you will need to mark these cells so you don't have to repeat the above procedure for each cell. The best way to do this is to change the background color of the selected cells by clicking the Fill Color button (Fill Color button) located on the Formatting toolbar (Excel 2003 and earlier) or in the Font group of the Home tab (Excel 2007 and later). If some of the cells in the sheet are already highlighted in a specific color, use a different color so you can restore the background colours when you are done.

Translating formulas

Editing the formulas should be pretty straightforward. First, select the cell and click inside the formula in the Formula bar:

Editing the formula

The text you will need to translate will be between double quotes. You will need to replace such text, taking great care not to overwrite the double quotes themselves or adjacent parts of the formula. If you make a mistake, click the red cross or press Escape button on the keyboard. When you are done, click the green tick icon.

Some examples of formulas are given below:

Original Formula Original Text Translated Formula Translated Text
=IF(D1>4;"YES";"NO") YES =IF(D1>4;"Ja";"Nein") Ja
=CONCATENATE(B3;" USD";D3) 3,5 USD =CONCATENATE(B3;" US dollar";D3) 3,5 US dollar
=(B2*C2) & " USD" 4 USD =(B2*C2) & " US dollar" 4 US dollar

Before editing the formulas, it is a good idea to back up your Excel spreadsheet.

If you have never edited Excel formulas before, you might find this article helpful:

For more help on editing formulas, use extensive help resources in Microsoft Excel.

Add comment

Security code Refresh