CONVERT Function
Whilst most of the world has adopted the metric measurement system, three countries officially prefer imperial units: USA, Liberia, and Myanmar. The UK, however, is caught between the two — and an imperial ‘hangover’ still exists in other Commonwealth nations such as Canada, India, South Africa, and Australia.
If you have a dataset containing imperial (or metric) data — out of necessity or convenience — you might want to convert it. Luckily, Excel has a versatile function for this purpose.
CONVERT converts a number from one measurement system to another.
𝗦𝘆𝗻𝘁𝗮𝘅
= 𝙲𝙾𝙽𝚅𝙴𝚁𝚃 ( 𝚗𝚞𝚖𝚋𝚎𝚛 , 𝚏𝚛𝚘𝚖_𝚞𝚗𝚒𝚝 , 𝚝𝚘_𝚞𝚗𝚒𝚝 )
• 𝚗𝚞𝚖𝚋𝚎𝚛 — the value to convert
• 𝚏𝚛𝚘𝚖_𝚞𝚗𝚒𝚝 — the current unit of 𝚗𝚞𝚖𝚋𝚎𝚛
• 𝚝𝚘_𝚞𝚗𝚒𝚝 — the unit to convert 𝚗𝚞𝚖𝚋𝚎𝚛 to
Both 𝚏𝚛𝚘𝚖_𝚞𝚗𝚒𝚝 and 𝚝𝚘_𝚞𝚗𝚒𝚝 require a measurement code enclosed in quotation marks.
𝗘𝘅𝗮𝗺𝗽𝗹𝗲
A table named tblPlayers holds baseball player data. The heights and weights are in imperial units but have been converted outside the table.
For height, the Height (in) column is referenced in CONVERT’s 𝚗𝚞𝚖𝚋𝚎𝚛 argument. The 𝚏𝚛𝚘𝚖_𝚞𝚗𝚒𝚝 is “in” for inches and the 𝚝𝚘_𝚞𝚗𝚒𝚝 is “cm” for centimetres.
Similarly, for weight, Weight (lbs) is the referenced column, the 𝚏𝚛𝚘𝚖_𝚞𝚗𝚒𝚝 is “lbm” for pound mass, and the 𝚝𝚘_𝚞𝚗𝚒𝚝 is “kg” for kilograms.
CONVERT is one of several functions that aren’t fully compatible with dynamic arrays. That’s why the double hyphen (–) preceding the table reference is necessary to spill the results. If omitted, a #VALUE! error occurs.
To guarantee whole numbers are returned, nest the CONVERT statement inside ROUND and round to zero decimal places.
