# A to Z of Excel Functions: the CONVERT Function

16 October 2017

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CONVERT function. *

** **

**The CONVERT function**

Are you a CONVERT? This function converts a number from one measurement system to another. For example, **CONVERT** can translate a table of distances in miles to a table of distances in kilometers.

The **CONVERT **function employs the following syntax to operate:

**CONVERT(number, from_unit, to_unit)**

The **CONVERT** function has the following arguments:

**number:**this is the value in**from_units**to convert**from_unit:**this represents the units for**number****to_unit:**this**ONVERT**accepts the following text values (in quotation marks) for**from_unit**and**to_unit**.

We often encounter financial models and spreadsheets falling over due to the significant prevalence of hard code. One common reason is due to analysts converting data from one measure or unit to another (*e.g.* from kilograms to parsecs, or something like that!).

Some modellers will go the extra mile (pun intended) by having a reference section where conversion factors are stored and referenced, *e.g.*

The problem with this approach is that figures may be transposed, typed over or stated the wrong way round (*e.g*. a common conversion error we found was tonnes in a ton). These numbers are constant – and there is another way.

If you are not familiar with this function already, let us introduce you to the CONVERT function:

**=CONVERT(number,from_unit,to_unit)**

Since Excel 2007 and the advent of Formula AutoComplete, this function has really come into its own. For example, to generate / confirm the first conversion factor, simply use 1,000 for the number and locate “meters” *(sic)*

Then, after selecting the appropriate ‘from’ unit, the list for the third argument will automatically filter for meaningful conversion units (e.g. “kg” will not appear):

Simple!

The only criticism we have is that the units do not appear to be in any logical order (e.g. alphabetical), but, unlike U2, you can usually find what you’re looking for.

Here’s the full list of “disciples” (*i.e. *“converts” – get it?):

It should be further noted that:

- if the input data types are incorrect,
**CONVERT**returns the*#VALUE!*error value - if the unit does not exist,
**CONVERT**returns the*#N/A*error value - if the unit does not support a binary prefix,
**CONVERT**returns the*#N/A*error value - if the units are in different groups,
**CONVERT**returns the*#N/A*error value - unit names and prefixes are case-sensitive.

Please see my example below:

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.*

*A full page of the function articles can be found here. *