Introduction to the CONVERT Function

black computer compenent

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010. 

This post will inform you about what the CONVERT function is and how to use it. We will cover its formula syntax, applicable measurement systems for its usage, vital conditions to remember when applying it and also an example for you to play around with.

The CONVERT function is a mathematical function which transforms a number from one measurement system to another. For example, CONVERT can update a table of distances in miles to a table of distances in kilometres.

A dandelion converting to different colours.
Image by Monsterkoi from Pixabay.

Syntax of the CONVERT Function

CONVERT(number,from_unit,to_unit)

Number refers to the value in from_units to convert.

From_unit is simply the units for number.

To_unit is the result units. The CONVERT function adopts these listed text values (enclosed in quotation marks) for from_unit and to_unit.

Caterpillars in cocoons converting into butterflies. A symbol of the CONVERT function.
Image by 2331323 from Pixabay.

Measurement systems to use the CONVERT Function with

Weight and Mass

Weight and massFrom_unit or to_unit
Gram“g”
Slug“sg”
Pound mass (avoirdupois)“lbm”
U (atomic mass unit)“u”
Ounce mass (avoirdupois)“ozm”
Grain“grain”
U.S. (short) hundredweight“cwt” or “shweight”
Imperial hundredweight“uk_cwt” or “lcwt” (“hweight”)
Stone“stone”
Ton“ton”
Imperial ton“uk_ton” or “LTON” (“brton”)

Distance

DistanceFrom_unit or to_unit
Meter“m”
Statute mile“mi”
Nautical mile“Nmi”
Inch“in”
Foot“ft”
Yard“yd”
Angstrom“ang”
Ell“ell”
Light-year“ly”
Parsec“parsec” or “pc”
Pica (1/72 inch)“Picapt” or “Pica”
Pica (1/6 inch)“pica”
U.S survey mile (statute mile)“survey_mi”

Time

TimeFrom_unit or to_unit
Year“yr”
Day“day” or “d”
Hour“hr”
Minute“mn” or “min”
Second“sec” or “s”

Pressure

PressureFrom_unit or to_unit
Pascal“Pa” (or “p”)
Atmosphere“atm” (or “at”)
mm of Mercury“mmHg”
PSI“psi”
Torr“Torr”

Force

ForceFrom_unit or to_unit
Newton“N”
Dyne“dyn” (or “dy”)
Pound force“lbf”
Pond“pond”

Energy

EnergyFrom_unit or to_unit
Joule“J”
Erg“e”
Thermodynamic calorie“c”
IT calorie“cal”
Electron volt“eV” (or “ev”)
Horsepower-hour“HPh” (or “hh”)
Watt-hour“Wh” (or “wh”)
Foot-pound“flb”
BTU“BTU” (or “btu”)

Power

PowerFrom_unit or to_unit
Horsepower“HP” (or “h”)
Pferdestärke“PS”
Watt“W” (or “w”)

Magnetism

MagnetismFrom_unit or to_unit
Tesla“T”
Gauss“ga”

Temperature

TemperatureFrom_unit or to_unit
Degree Celsius“C” (or “cel”)
Degree Fahrenheit“F” (or “fah”)
Kelvin“K” (or “kel”)
Degrees Rankine“Rank”
Degrees Réaumur“Reau”

Volume

Volume (or liquid measure)From_unit or to_unit
Teaspoon“tsp”
Modern teaspoon“tspm”
Tablespoon“tbs”
Fluid ounce“oz”
Cup“cup”
U.S. pint“pt” (or “us_pt”)
U.K. pint“uk_pt”
Quart“qt”
Imperial quart (U.K.)“uk_qt”
Gallon“gal”
Imperial gallon (U.K.)“uk_gal”
Liter“l” or “L” (“lt”)
Cubic angstrom“ang3” or “ang^3”
U.S. oil barrel“barrel”
U.S. bushel“bushel”
Cubic feet“ft3” or “ft^3”
Cubic inch“in3” or “in^3”
Cubic light-year“ly3” or “ly^3”
Cubic meter“m3” or “m^3”
Cubic Mile“mi3” or “mi^3”
Cubic yard“yd3” or “yd^3”
Cubic nautical mile“Nmi3” or “Nmi^3”
Cubic Pica“Picapt3”, “Picapt^3”, “Pica3” or “Pica^3”
Gross Registered Ton“GRT” (“regton”)
Measurement ton (freight ton)“MTON”

Area

AreaFrom_unit or to_unit
International acre“uk_acre”
U.S. survey/statute acre“us_acre”
Square angstrom“ang2″ or “ang^2”
Are“ar”
Square feet“ft2” or “ft^2”
Hectare“ha”
Square inches“in2” or “in^2”
Square light-year“ly2” or “ly^2”
Square meters“m2” or “m^2”
Morgen“Morgen”
Square miles“mi2” or “mi^2”
Square nautical miles“Nmi2” or “Nmi^2”
Square Pica“Picapt2”, “Pica2”, “Pica^2” or “Picapt^2”
Square yards“yd2” or “yd^2”

Information

InformationFrom_unit or to_unit
Bit“bit”
Byte“byte”

Speed

SpeedFrom_unit or to_unit
Admiralty knot“admkn”
Knot“kn”
Meters per hour“m/h” or “m/hr”
Meters per second“m/s” or “m/sec”
Miles per hour“mph”

The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.

PrefixMultiplierAbbreviation
yotta1E+24“Y”
zetta1E+21“Z”
exa1E+18“E”
peta1E+15“P”
tera1E+12“T”
giga1E+09“G”
mega1E+06“M”
kilo1E+03“k”
hecto1E+02“h”
dekao1E+01“da” or “e”
deci1E-01“d”
centi1E-02“c”
milli1E-03“m”
micro1E-06“u”
nano1E-09“n”
pico1E-12“p”
femto1E-15“f”
atto1E-18“a”
zepto1E-21“z”
yocto1E-24“y”
Binary PrefixPrefix ValueAbbreviationDerived from
yobi2^80 = 1 208 925 819 614 629 174 706 176“Yi”yotta
zebi2^70 = 1 180 591 620 717 411 303 424“Zi”zetta
exbi2^60 = 1 152 921 504 606 846 976“Ei”exa
pebi2^50 = 1 125 899 906 842 624“Pi”peta
tebi2^40 = 1 099 511 627 776“Ti”tera
gibi2^30 = 1 073 741 824“Gi”giga
mebi2^20 = 1 048 576“Mi”mega
kibi2^10 = 1024“ki”kilo

Important Conditions of the CONVERT Function

  • If the input data types are incorrect, the CONVERT function yields the #VALUE! error value.
  • When the unit is non-existent, CONVERT brings back the #N/A error value.
  • Should the unit provide no support to a binary prefix, CONVERT captures the #N/A error value.
  • If the units are in various groups, CONVERT obtains the #N/A error value.
  • Unit names and prefixes are case-sensitive.

Examples of the CONVERT Function

FormulaDescriptionResult
=CONVERT(1, “lbm”, “kg”)Converts 1 pound mass to kilograms.0.4535924
=CONVERT(68, “F”, “C”)Converts 68 degrees Fahrenheit to Celsius.20
=CONVERT(2.5, “ft”, “sec”)Data types are different, thus an error is returned.#N/A
=CONVERT(CONVERT(100,”ft”,”m”),”ft”,”m”)Converts 100 square feet into square metres.9.290304

Copy the following to a blank worksheet

1A
2Data
6
FormulaDescription (Result)
=CONVERT(A2,”C”,”F”)Convert 6 degrees Celsius to Fahrenheit (42.8).
=CONVERT(A2,”tsp”,”tbs”)Convert 6 teaspoons to tablespoons (2).

=CONVERT(A2,”gal”,”l”)

Convert 6 gallons to liters (22.71741274).
=CONVERT(A2,”mi”,”km”)
Convert 6 miles to kilometres (9.656064).
=CONVERT(A2,”km”,”mi”)Convert 6 kilometres to miles (3.728227153).

=CONVERT(A2,”in”,”ft”)Convert 6 inches to feet (0.5).
=CONVERT(A2,”cm”,”in”)Convert 6 centimetres to inches (2.362204724).

Other Maths Functions Topics

Round a number to the decimal places I want 

Find the remainder with MOD

Build random number generators with RAND

Generate random number between numbers you specify with RANDBETWEEN

SUBTOTAL function

ROUND function

Round a number

Leave a Reply

%d bloggers like this: