Note: The other languages of the website are Google-translated. Back to English

Excel cell conversion tutorial– convert units, text and number, currencies, time zones, etc.

When working with data in Excel, there are times you may need to do some data conversions on cells, such as unit conversion, currency conversion, time zone conversion and so on. Here, this tutorial collects different kinds of cell conversions that you may encounter in your daily Excel work and provides step-by-step detailed solutions to help you handle the tasks with ease.

This tutorial primarily covers: (Click on any heading in the table of contents below or on the right to navigate to the corresponding content.)


1. Convert between various unit measurements in Excel

Normally, we use the CONVERT function to convert a number from one unit of measure to another. However, for some unit conversions, the CONVERT function does not work.

The following examples demonstrate the CONVERT function and different formulas to help convert between various units of measurements in cells.

1.1 Convert between various unit measurements with the CONVERT function

Excel provides a built-in function "Insert Comment” to help you easily add a comment to a cell in Excel.

As shown in the screenshot below, to convert the inches listed in the range B3:B10 to foot, cm and mm separately, you can apply the CONVERT function to get it done.

Syntax

CONVERT(number, from_unit, to_unit)

Arguments

Number (required): It is the value to be converted.
From_unit (required): The original unit for “number”.
To_unit (required): The unit to convert the “number” to.

In Excel, there are numbers of measurement units. To view the units for each category that can be applied to the “from_unit” and “to_unit” arguments of the CONVERT function, please click here.

Convert inch to foot:

Select a cell (say D3 in this case), copy or enter the following formula into it and press the Enter key to get the first result. Select this result cell and then drag its AutoFill Handle down to get the rest of the results. See screenshot:

=CONVERT(B3,"in","ft")

Convert inch to cm:

Select a cell (say E3 in this case), copy or enter the following formula into it and press the Enter key to get the first result. Select this result cell and then drag its AutoFill Handle down to get the rest of the results.

=CONVERT(B3,"in","cm")

Convert inch to mm:

Select a cell (say F3 in this case), copy or enter the following formula into it and press the Enter key to get the first result. Select this result cell and then drag its AutoFill Handle down to get the rest of the results.

=CONVERT(B3,"in","mm")

Notes:

1) For the “from_unit” and “to_unit” paraments, you need to use the abbreviations of the measurement names instead of using the full names. In cell D3, if you apply the formula below, it will return an error value of #N/A.
1)=CONVERT(B3,"inch","foot")
1)2) The text abbreviations for the “from_unit” and “to_unit” arguments are case-sensitive.
1)3) You can convert between different units by changing the “from_unit” and “to_unit” paraments in the formula.

1.2 Convert CM (centimeters) to feet and inches in Excel with formula

There are some unit conversions that the CONVERT function cannot handle, such as convert centimeters or meters to feet and inches as the following screenshot shown. You need to apply the formula below to get it done.

1. Select a blank cell (say D2 in this case), enter the formula below and press Enter to get the first result. Select this result cell and drag its AutoFill Handle down to get other results.

=TRUNC(B2/2.54/12)&"' "&ROUND(MOD(B2/2.54,12),0)&""""

Note: To retain 2 decimal places for inches, you can apply the following formula.

=INT(CONVERT(B2,"cm","ft")) & "' " & TEXT(12*(CONVERT(B2,"cm","ft")-INT(CONVERT(B2,"cm","ft"))),"0.00") & """"


1.3 Convert M (meters) to feet and inches in Excel with formula

To convert meter to feet and inches as shown in the screenshot below, the following formulas can help.

1. Select a blank cell, such as D2, enter the following formula and press Enter to get the first result. Select this result cell and drag its AutoFill Handle down to get other feet and inches.

=TRUNC(B2*100/2.54/12)&"' "&ROUND(MOD(B2*100/2.54,12),0)&""""

Note: To retain 2 decimal places for inches after converting, you can apply the following formula.

=INT(CONVERT(B2,"m","ft")) & "' " & TEXT(12*(CONVERT(B2,"m","ft")-INT(CONVERT(B2,"m","ft"))),"0.00") & """"


1.4 Convert feet and inches to inches or vice versa with formula

As shown in the screenshot below, if you want to convert a measurement in feet and inches to inches only or vice versa in Excel, try the following formulas.

Convert feet and inches to inches

To convert a measurement in feet and inches to inches, you can apply a formula based on the LEFT, FIND, MID and SUBSTITUTE functions as follows.

Select a cell to output the first inch, copy or enter the following formula into it and press the Enter key. Select this result cell and drag its AutoFill Handle down to get all inches.

=LEFT(B3,FIND("'",B3)-1)*12+SUBSTITUTE(MID(B3,FIND("'",B3)+1,LEN(B3)),"""","")

Convert inches to feet and inches

To convert inches to feet and inches, please do as follows.

Select a cell, enter the following formula into it and press Enter to get the first result. Select this cell and drag its AutoFill Handle down to get all feet and inches. See screenshot:

=INT(B3/12)&"' "&MOD(B3,12)&""""


1.5 Convert angle between degrees and radians with formula

This section is going to show you how to convert angels between degrees and radians in Excel.

Convert degrees to radians

To convert the degrees to radians as shown in the screenshot below, the RADIANS function can do you a favor.

Syntax

=RADIANS(angle)

Argument

Angle: Required. It is an angle in degrees that you want to convert to radians.

Select a blank cell (say D3 in this case), enter the following formula and press the Enter key to return the first radian. Select this result cell and drag its AutoFill Handle down to get the rest of the radians.

=RADIANS(B3)

Convert radians to degrees

Excel has a DEGREE function that you can use to convert radians to degrees. Please do as follows.

Syntax

=DEGREES(angle)

Argument

Angle: Required. It is an angle in radians that you want to convert to degrees.

Select a blank cell (say D3 in this case), enter the following formula and press the Enter key to return the first degree. Select this result cell and drag its AutoFill Handle down to get the rest of the degrees.

=DEGREES(B3)

Note: Or you can directly use the formula as follows.

=B3*180/PI()

where the PI function returns the number 3.14159265358979 of the mathematical constant called pi. Click to know more about this PI function.


1.6 Easily convert between various unit measurements with an amazing tool

Here we recommend a handy feature – Unit Conversion of Kutools for Excel. It collects various kinds of unit measurements in a dialog box to help you easily convert a number from one measurement system to another. You can do as follows to know how to use it.

It includes the following measurement systems:

1) Angle
2) Bits & Bytes
3) Distance
4) Energy
5) Force
6) Liquid
7) Mass
8) Surface
9) Temperature
10) Time
11) Volume
12) Hex

1. Select a range of cells that contains the numbers you want to convert from one measurement system to another, then enable the Unit Conversion feature by clicking Kutools > Content (or Conversion in different Toolbar modes) > Unit Conversion. See screenshot:

2. In the Unit Conversion dialog box, please configure as follows.

2.1) Select a measurement system from the Units drop-down list;
2.2) In the left units box, select the unit of the selected numbers;
2.3) In the right units box, select the unit for the result;
2.4) Click the OK button.

In this case, I am going to convert the angels from degrees to radians, so I select Angle from the drop-down list, choose Degree and Radian from the left and right unit boxes separately, and then click the OK button.

Tips:

1) You can preview the conversion results in the Preview box.
2) You can add the conversion results as comments (or notes in Excel 365) by checking the Add results as comment (or Add results as note) box.
3) You can easily navigate to different measurement systems as you need.

2. Convert numbers to different number systems in Excel

This section demonstrates how to convert numbers to different number systems in Excel.

2.1 Convert numbers to different number systems with built-in functions

Excel has several functions that you can use to convert between different number systems. For example, you can use the DEC2HEX function to convert a decimal number to a hexadecimal number or use the DEC2BIN function to convert a decimal number to binary. This section is going to show you how to use these built-in functions to convert between different number systems in Excel.

Convert a decimal number to a hexadecimal

To convert a decimal number to a hexadecimal number, you can apply the DEC2HEX function to get it done.

Syntax

DEC2HEX(number, [places])

Arguments

Number: Required, the decimal integer number that needs to be converted to a hexadecimal number.
Places: Optional, the number that specifies the number of characters of the result.

Select a cell, enter the formula below and press the Enter key to get the first hexadecimal number. Select this result cell and drag it AutoFill Handle down to get the rest of the hexadecimal numbers.

=DEC2HEX(B3)

The following table lists the functions that you can use to convert numbers to different number systems.

Choose the function you need and apply it as the above steps shown.

 Function  Description  Syntax
 BIN2DEC  Converts a binary number to a decimal number  =BIN2DEC(number)
 BIN2HEX  Converts a binary number to a hexadecimal number  =BIN2HEX(number, [places])
 BIN2OCT  Converts a binary number to an octal number  =BIN2OCT(number, [places])
 DEC2HEX  Converts a decimal number to a hexadecimal number  =DEC2HEX(number, [places])
 DEC2BIN  Converts a decimal number to binary  =DEC2BIN(number, [places])
 DEC2OCT  Converts a decimal number to an octal number  =DEC2OCT(number, [places])
 HEX2DEC  Converts a hexadecimal number to a decimal number  =HEX2DEC(number)
 HEX2BIN  Converts a hexadecimal number to binary  =HEX2BIN(number, [places])
 HEX2OCT  Converts a hexadecimal number to an octal number  =HEX2OCT(number, [places])
 OCT2DEC  Converts an octal number to decimal number  =OCT2DEC(number)
 OCT2HEX  Converts an octal number to hexadecimal number  =OCT2HEX(number, [places])
 OCT2BIN  Converts an octal number to binary  =OCT2BIN(number, [places])

2.2 Easily convert numbers to different number systems with an amazing tool

While each of the above functions is easy to use, they can also be easily confused. Here, Kutools for Excel's the Number Systems Conversion feature collects all number systems in one dialog box, helping you easily navigate between number systems when you need to convert a number from one number system to another.

1. Select the rang of cells where you want to convert numbers from one system to another, and then enable the feature by clicking Kutools > Content (or Conversion) > Number Systems Conversion.

2. In the Conversion of Number Systems dialog box, you need to configure as follows.

2.1) In the left box of the Conversion type section, select the number system of the selected numbers;
2.2) In the right box, select the number system for the results;
2.3) Click the OK button. See screenshot:

In this case, I need to convert decimal numbers to hexadecimal, so I choose Decimal and Hexadecimal from the left and right boxes separately.

Tips:

1) You can preview the conversion results in the Preview box.
2) You can easily navigate to different number systems as you need.

3. Convert between number and text (number stored as text) in Excel

This section is going to show you how to convert number to text or text to number in Excel.

3.1 Convert number to text (number stored as text) in Excel

There are times you may need to convert numbers to text. For example, you need to store a long number which has more than 11 digits in a spreadsheet, you need to convert the number to text in case Excel changes it to scientific notification. Here we provide three methods to help you convert numbers to text in details.

3.1.1 Convert number to text with TEXT function

To convert the numbers in the following table to text, you can use the TEXT function to get it done.

1. Select a cell (say D3 in this case), enter the formula below and press the Enter key to get the first result. Select this result cell and drag its AutoFill Handle down to get the rest of the results.

=TEXT(B3,"0")

Click here to know more about the TEXT function in Excel.

Now you need to convert the formulas to values.

3. Select all result cells, press Ctrl + C keys to copy them, and then press the Ctrl + Alt + V, or Alt + E + S keys simultaneously to display the Paste Special dialog box.

4. In the Paste Special dialog box, select the Values option in the Paste section and then click OK to save the changes. See screenshot:

Then you can see a tiny green triangle appears in the top-left corner of each cell in the selection. The numbers are converted to text now. See screenshot:

Notes:

1) The converted numbers can’t be used in calculations.
2) The above formula does not retain any decimal places after converting. In order to preserve the decimal part of the number, you need to change the second argument in the TEXT function as follows.
Take the number 1263.25 as an example:
To keep one decimal place: =TEXT(1263.25,"0.0"), the result will be 1263.3.
To keep two decimal places: =TEXT(1263.25,"0.00"), the result will be 1263.25.
3) If there are only several numbers in a number list that need to be converted to text, such as two or three, you can directly add an apostrophe before the number.

3.1.2 Convert number to text with the Format Cells command

You can also convert a range of numbers to text by using the built-in Format Cells command in Excel.

1. Select the range with the numbers you want to convert to text.

2. Right click the selection and then select Format Cells from the context menu.

3. In the opening Format Cells dialog box, select Text in the Category box under the Number tab, and finally click the OK button to save the changes.

Then the selected cells are formatted as text.

3.1.3 Convert number to text with the Text to Columns command

The Text to Columns command can also help to convert a range of numbers to text in Excel. Please do as follows.

1. Select the range with the numbers you want to convert to text.

2. Click Data > Text to Columns.

3. In the opening Convert Text to Columns Wizard dialog box, click the Next buttons to go through the Step 1 and Step 2 dialog boxes. In the Step 3 dialog box, select the Text option in the Column data format section, and then click the Finish button. See screenshot:

You can see the selected numbers are converted to text with an apostrophe adding before each number.

3.1.4 Convert number to text with an amazing tool

In addition to the above methods, Kutools for Excel’s Convert between Text and Number feature can help you easily convert a range of numbers to text. Please install it and try as follows.

1. Select the range with the numbers you want to convert to text.

2. Click Kutools > Content (or Conversion) > Convert between Text and Number.

3. In the Convert between Text and Number dialog box, select the Number to text option, and then click the OK button.

Then all selected numbers are converted to text as shown in the screenshot below.


3.2 Convert text (number stored as text) to number in Excel

Sometimes values in your worksheet look like numbers, but they are text values and may not be used in calculations. This section provides several methods to help you convert text to number in Excel.

3.2.1 Convert text to number with the Convert to Number option

If there is a tiny green triangle appears in the top-left corner of the text formatted cell, you can apply the Convert to Number command to convert it to number easily.

1. Select the text cells where you want to convert to numbers.

2. Click the warning sign and then choose Convert to Number from the drop-down menu.

Then the selected texts are converted to numbers as shown in the screenshot below.

3.2.2 Convert text to number by using the Paste Special command

For the cells that are flagged with green triangles, you can also apply the paste Special command to convert them to numbers.

1. Select a blank cell, press the Ctrl + C keys to copy it.

2. Select the range of cells where you want to convert to numbers, and then press the Ctrl + Alt + V, or Alt + E + S keys simultaneously.

3. In the Paste Special dialog box, select Values in the Paste section, choose Add in the Operation section, and then click the OK button.

See the following screenshot:

Then the selected cells are converted to numbers immediately.

3.2.3 Convert text to number by changing the cell formatting

If numbers have been formatted as Text as shown in the screenshot below (no tiny green triangle appear in the top-left corner of cells), you can manually change the cell format to Number to finish the conversion.

1. Select the range of text-formatted numbers that you want to convert to regular numbers.

2. Right click the selection and select Format Cells from the context menu.

3. In the opening Format Cells dialog box, stay in the Number tab, and then configure as follows.

3.1) Select Number in the Category box;
3.2) Change the Decimal places to 0;
3.3) Click OK to save the changes.

Then the selected text-formatted numbers are converted to regular numbers.

3.2.4 Easily convert text to number with an amazing tool

Here we recommend a handy tool – Kutools for Excel’s Convert between Text and Number feature. With this feature, you can easily convert texts in a selection or multiple selections to numbers easily.

Please do as follows to apply this feature.

1. Select the text cells where you want to convert to numbers.

2. Click Kutools > Content (or Conversion) > Convert between Text and Number.

3. In the Convert between Text and Number dialog box, select the Text to number option and then click the OK button.

Then texts in selected range are converted to numbers immediately.


4. Convert currencies in Excel

Supposing you have a large currency report in USD, and you want to convert the USD to your own country’s currency, such as EUR. By default, Excel does not include any built-in tools to convert currency. In this section, you will learn how to convert between any two currencies in Excel with two methods.

4.1 Convert between any two currencies with exchange rates in Excel

Before using this method, you need to find out the current exchange rate between the two currencies. In this case, I need to convert from U.S. dollar to Euro, so I use the current exchange rate between the US dollar and the Euro: 1 USD = 0.95 EUR.

1. Enter the exchange range 0.95 into a blank cell, such as D3 in this case.

2. Select the top cell E3, enter the following formula and press the Enter key to convert the first U.S. dollar to Euro. Select this result cell and then drag its AutoFill Handle down to get the rest of the results.

=C3*$D$3

Now all U.S. dollars are converted to Euros as shown in the screenshot above.


4.2 Easily convert between any two currencies with an amazing tool

It’s annoying to find out the current exchange rate between two currencies each time you need to convert between them. Kutools for Excel’s Currency Conversion feature gathers all currencies in a dialog box. With it, you can easily convert from one currency to another with current exchange rate.

1. Select the range of currency-formatted numbers from which you want to convert to other currencies.

2. Click Kutools > Content (or Conversion) > Currency Conversion.

2. In the opening Currency Conversion dialog box, you need to do the following settings.

2.1) Click the Update rate button to get the latest exchange rate;
2.2) In the left box, select the currency for the selected numbers. Here I select USD -U.S. Dollar.
2.3) In the right box, select the currency for the results. Here I select EUR -Euro.
2.4) Click the OK button to start converting.

Note: The above settings will overwrite the original numbers with the results. You can just output the results as comments if you need: click the Fill options button in the Currency Conversion dialog box. In the Fill options dialog box, select the Only comment (or Only note) option, specify the information that the comments or notes will include, and then close the dialog box.


5. Convert between time zones in Excel

This section is going to show you how to convert a time from one time zone to another. As shown in the screenshot below, to convert the time in Beijing listed in the range C3:C8 to times in different cities, you can try the following method.

5.1 Convert time from one time zone to another with formula

The following formula can help to convert a time from one time zone to another based on the given time difference.

1. Firstly, you need to find out the time difference between the two time zones.

In this case, I enter the corresponding time difference in each cell of the range F3:F8.

For example, as Beijing, China is 12 hours ahead of New York, USA, I enter the time difference -12 into the cell next to New York. See screenshot:

2. Select a cell, enter the following formula and press the Enter key to get the New York time. Select this result cell and drag its AutoFill Handle down to get the times in other cities.

=MOD(C3+(F3/24),1)

Note: when working with dates that include both a date and time, such as 5/10/2022 2:55:00 PM, you can apply the following formula.

=datetime+(hours/24)


6. Convert between Arabic and Roman numerals in Excel

To convert between Arabic and roman numerals in Excel, the following methods can do you a favor.

6.1 Convert Arabic numeral to Roman numeral with the ROMAN function

The Excel bult-in ROMAN function can convert an Arabic numeral to roman as text.

Syntax

ROMAN(number, [form])

Arguments

Number (required): The Arabic numeral you want to convert to roman.
Form (optional): The type of roman numeral you want.

To convert the numbers in the following table to roman, you can apply the ROMAN function as follows.

1. Select a cell, enter the following formula and press Enter to convert the first Arabic to roman. Select this cell and drag its AutoFill Handle down to get other results.

=ROMAN(B3)

Tips: Click to know more about this ROMAN function.


6.2 Convert Roman numeral to Arabic numeral with formula

It is easy to convert an Arabic numeral to roman with the ROMAN function. However, if you want to convert a roman numeral to an Arabic, you need a more complex formula to get it done. Please do as follows.

1. Select a blank cell, such as D3 in this case, enter the following formula and press the Enter key to get the first result. Select this result cell and drag its AutoFill Handle down to get the rest of the results.

=MATCH(B3,INDEX(ROMAN(ROW(INDIRECT("1:4000"))),0),0)

Note: In the formula, B3 is the cell containing the Arabic numeral you want to convert to roman.


6.3 Easily convert between Arabic and Roman numerals with an amazing tool

It may be troublesome for us to remember different formulas to convert between Arabic numerals and roman numerals in Excel. Here is a handy tool for you – Kutools for Excel. With its Convert between Roman and Number utility, you can easily convert between Arabic numerals and roman numerals with a few clicks only.

1. Select the range of Arabic numerals or roman numerals you want to convert.

2. Click Kutools > Content (or Conversion) > Convert between Roman and Number.

3. In the Convert between Roman and Number dialog box, select the Number to roman or Roman to number option, depending on the original data you selected, and then click OK. See screenshot:

Then the selected data will be converted to roman or Arabic numeral at once.


7. Convert formulas to results in Excel

When copying and pasting a range of formulas cells to a new place, the results are dynamically changed with the cell references. If you want to keep the result constant when copying and pasting a formula to a new place, you need to convert the formula to its result. This section demonstrates two ways that help replace formulas in Excel with their results.

7.1 Convert formulas to results with the Paste Special command

You can convert a range of formulas to their results by using the Paste Special command in Excel.

1. Select the cells with formulas that you want to convert to their results, and then press the Ctrl + C keys to copy it.

2. If you want to replace the formulas in the selection directly with the results, select the first cell of the selected range. To output the results in a new place, just select a cell in the new range. Then right click the selected cell and select Values from the context menu.

Then you can see the formulas are converted to their results as shown in the screenshot below.


7.2 One click to quickly convert formulas to results

If you have Kutools for Excel installed, you can apply it’s To Actual feature to convert all formulas to their results with only one click.

1. Select a range or multiple ranges that contain formulas you want to convert.

2. Click Kutools > To Actual.

Then all formulas in the selected range(s) are converted to their results immediately.


8. Convert numbers to ordinal numbers in Excel

As shown in the screenshot below, to convert the numbers listed in B3:B12 to ordinal numbers, this section provides three methods to help you get it done.

8.1 Convert numbers to ordinal numbers with formula

You can apply the following formula to convert a number to its corresponding ordinal number in Excel. Please do as follows.

1. Select a blank cell (say C3 in this case), copy or enter the formula below and press Enter to get the ordinal number of the first number. Select this result cell and drag its AutoFill Handle down to get the rest of the ordinal numbers.

=B3&IF(OR(--RIGHT(B3,2)={11,12,13}),"th",IFERROR(CHOOSE(RIGHT(B3),"st","nd","rd"),"th"))

Note: B3 is the first cell of the range containing numbers that you want to convert to their ordinal numbers. You can change it as you need.


8.2 Convert numbers to ordinal numbers with user-defined function

If the above formula is complex for you, you can apply the following user-defined function to convert numbers to ordinal numbers in Excel.

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, then copy the following VBA code into the Module window.

VBA code: Convert numbers to ordinal numbers in Excel

Function Addth(pNumber As String) As String
'Updateby20220516
Select Case CLng(VBA.Right(pNumber, 1))
    Case 1
    Addth = pNumber & "st"
    Case 2
    Addth = pNumber & "nd"
    Case 3
    Addth = pNumber & "rd"
    Case Else
    Addth = pNumber & "th"
End Select
Select Case VBA.CLng(VBA.Right(pNumber, 2))
    Case 11, 12, 13
    Addth = pNumber & "th"
End Select
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell next to the first number you want to convert to ordinal, enter the formula below and press the Enter key to get the result. Select this result cell, drag its AutoFill Handle down to get the rest of the results.

=Addth(B3)


8.3 One click to quickly convert numbers to ordinal numbers

For Excel newbies, the above two methods may not be easy to handle. Here we recommend the Convert Number to Ordinal utility of Kutools for Excel. With this feature, you can convert a range of numbers to ordinals in bulk with just one click.

As the following demo shows, you just need to select the range of cells from which you want to convert the numbers to ordinals, and then click Kutools > Content > Convert Number to Ordinal.

Then all selected numbers are converted to ordinals. Meanwhile, a dialog box will pop up to tell you how many cells have been successfully converted, click the OK button.

Click to know more about this feature.


9. Convert numbers to words in Excel

In this section, you will learn completely how to convert a number to words in Excel.

9.1 Convert numbers to English words with user-defined function

As shown in the screenshot below, to display numbers as English words in Excel, the following user-defined function can do you a favor. Please have a try.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, then copy the following VBA code into the Module window.

VBA code: Convert numbers to English words

Function NumberstoWords(ByVal MyNumber)
'Update by Extendoffice 20220516
Dim xStr As String
Dim xFNum As Integer
Dim xStrPoint
Dim xStrNumber
Dim xPoint As String
Dim xNumber As String
Dim xP() As Variant
Dim xDP
Dim xCnt As Integer
Dim xResult, xT As String
Dim xLen As Integer
On Error Resume Next
xP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
xNumber = Trim(Str(MyNumber))
xDP = InStr(xNumber, ".")
xPoint = ""
xStrNumber = ""
If xDP > 0 Then
xPoint = " point "
xStr = Mid(xNumber, xDP + 1)
xStrPoint = Left(xStr, Len(xNumber) - xDP)
For xFNum = 1 To Len(xStrPoint)
xStr = Mid(xStrPoint, xFNum, 1)
xPoint = xPoint & GetDigits(xStr) & " "
Next xFNum
xNumber = Trim(Left(xNumber, xDP - 1))
End If
xCnt = 0
xResult = ""
xT = ""
xLen = 0
xLen = Int(Len(Str(xNumber)) / 3)
If (Len(Str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1
Do While xNumber <> ""
If xLen = xCnt Then
xT = GetHundredsDigits(Right(xNumber, 3), False)
Else
If xCnt = 0 Then
xT = GetHundredsDigits(Right(xNumber, 3), True)
Else
xT = GetHundredsDigits(Right(xNumber, 3), False)
End If
End If
If xT <> "" Then
xResult = xT & xP(xCnt) & xResult
End If
If Len(xNumber) > 3 Then
xNumber = Left(xNumber, Len(xNumber) - 3)
Else
xNumber = ""
End If
xCnt = xCnt + 1
Loop
xResult = xResult & xPoint
NumberstoWords = xResult
End Function
Function GetHundredsDigits(xHDgt, xB As Boolean)
Dim xRStr As String
Dim xStrNum As String
Dim xStr As String
Dim xI As Integer
Dim xBB As Boolean
xStrNum = xHDgt
xRStr = ""
On Error Resume Next
xBB = True
If Val(xStrNum) = 0 Then Exit Function
xStrNum = Right("000" & xStrNum, 3)
xStr = Mid(xStrNum, 1, 1)
If xStr <> "0" Then
xRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "
Else
If xB Then
xRStr = "and "
xBB = False
Else
xRStr = " "
xBB = False
End If
End If
If Mid(xStrNum, 2, 2) <> "00" Then
xRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)
End If
GetHundredsDigits = xRStr
End Function
Function GetTenDigits(xTDgt, xB As Boolean)
Dim xStr As String
Dim xI As Integer
Dim xArr_1() As Variant
Dim xArr_2() As Variant
Dim xT As Boolean
xArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
xStr = ""
xT = True
On Error Resume Next
If Val(Left(xTDgt, 1)) = 1 Then
xI = Val(Right(xTDgt, 1))
If xB Then xStr = "and "
xStr = xStr & xArr_1(xI)
Else
xI = Val(Left(xTDgt, 1))
If Val(Left(xTDgt, 1)) > 1 Then
If xB Then xStr = "and "
xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))
xT = False
End If
If xStr = "" Then
If xB Then
xStr = "and "
End If
End If
If Right(xTDgt, 1) <> "0" Then
xStr = xStr & GetDigits(Right(xTDgt, 1))
End If
End If
GetTenDigits = xStr
End Function
Function GetDigits(xDgt)
Dim xStr As String
Dim xArr_1() As Variant
xArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
xStr = ""
On Error Resume Next
xStr = xArr_1(Val(xDgt))
GetDigits = xStr
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell to output the first result, such as D3 in this case. Enter the following formula and press Enter. Select this result cell, drag its AutoFill Handle down to get the rest of the results.

=NumberstoWords(B3)


9.2 Convert numbers to currency words with user-defined function

When processing financial related numbers, you may need the numbers’ words form (see the screenshot below). Here we demonstrate a user-defined function to help you convert a number to its equivalent English currency word in Excel.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, then copy the following VBA code into the Module window.

VBA code: Convert numbers to English currency words

Function SpellNumberToCurrency(ByVal pNumber)
'Update by Extendoffice 20220516
Dim Dollars, Cents
arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
pNumber = Trim(Str(pNumber))
xDecimal = InStr(pNumber, ".")
If xDecimal > 0 Then
    Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & "00", 2))
    pNumber = Trim(Left(pNumber, xDecimal - 1))
End If
xIndex = 1
Do While pNumber <> ""
    xHundred = ""
    xValue = Right(pNumber, 3)
    If Val(xValue) <> 0 Then
        xValue = Right("000" & xValue, 3)
        If Mid(xValue, 1, 1) <> "0" Then
            xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred "
        End If
        If Mid(xValue, 2, 1) <> "0" Then
            xHundred = xHundred & GetTens(Mid(xValue, 2))
        Else
            xHundred = xHundred & GetDigit(Mid(xValue, 3))
        End If
    End If
    If xHundred <> "" Then
        Dollars = xHundred & arr(xIndex) & Dollars
    End If
    If Len(pNumber) > 3 Then
        pNumber = Left(pNumber, Len(pNumber) - 3)
    Else
        pNumber = ""
    End If
    xIndex = xIndex + 1
Loop
Select Case Dollars
    Case ""
        Dollars = "No Dollars"
    Case "One"
        Dollars = "One Dollar"
    Case Else
        Dollars = Dollars & " Dollars"
End Select
Select Case Cents
    Case ""
        Cents = " and No Cents"
    Case "One"
        Cents = " and One Cent"
    Case Else
        Cents = " and " & Cents & " Cents"
End Select
SpellNumberToEnglish = Dollars & Cents
End Function
Function GetTens(pTens)
Dim Result As String
Result = ""
If Val(Left(pTens, 1)) = 1 Then
    Select Case Val(pTens)
        Case 10: Result = "Ten"
        Case 11: Result = "Eleven"
        Case 12: Result = "Twelve"
        Case 13: Result = "Thirteen"
        Case 14: Result = "Fourteen"
        Case 15: Result = "Fifteen"
        Case 16: Result = "Sixteen"
        Case 17: Result = "Seventeen"
        Case 18: Result = "Eighteen"
        Case 19: Result = "Nineteen"
        Case Else
    End Select
Else
Select Case Val(Left(pTens, 1))
    Case 2: Result = "Twenty "
    Case 3: Result = "Thirty "
    Case 4: Result = "Forty "
    Case 5: Result = "Fifty "
    Case 6: Result = "Sixty "
    Case 7: Result = "Seventy "
    Case 8: Result = "Eighty "
    Case 9: Result = "Ninety "
    Case Else
End Select
Result = Result & GetDigit(Right(pTens, 1))
End If
GetTens = Result
End Function
Function GetDigit(pDigit)
Select Case Val(pDigit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
End Select
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell to output the first result, such as D3 in this case. Enter the following formula and press Enter. Select this result cell, drag its AutoFill Handle down to get the rest of the results.

=SpellNumberToCurrency(B3)


9.3 Easily convert numbers to English/currency words with an amazing tool

The above codes are somewhat troublesome for Excel newbies. If you have Kutools for Excel installed, you can apply its Numbers to Words feature to easily convert a range of numbers to English words or English currency words as you need with several clicks only.

1. Select a range of cells where you want to convert the numbers to English words or English currency words, and then click Kutools > Content > Numbers to Words. See screenshot:

2. In the Numbers to Currency Words dialog box, you need to configure as follows.

To convert the selected numbers to English words:

2.1) Select the English radio button in the Languages section;
2.2) Check the Not converted to Currency checkbox;
2.3) Click the OK button.

To convert the selected numbers to English currency words:

2.1) Select the English radio button in the Languages section;
2.2) Click the OK button.

Then the selected numbers are converted to English words or English currency words based on your configuration.

Click to know more about this feature.


10. Convert numbers to dates in Excel

This section provides some methods to help you convert a serial number or an 8-digit number to Excel recognized date in Excel.

10.1 Convert serial numbers to dates in Excel

Sometimes, Excel returns a serial number (5-digit number) instead of the date you entered (see the screenshot below). That’s because Excel stores date and time as serial numbers in the backend. To convert a serial number to dates in Excel, you can try one of the following three methods.

10.1.1 Convert serial numbers to dates with Format Cells function

You can easily convert a range of serial numbers to dates by changing the cells’ formatting. Please do as follows.

1. Select the range of cells where you want to convert the serial numbers to dates, right click the selection and select Format Cells from the context menu.

2. In the Format Cells dialog box, you need to:

2.1) Stay in the Number tab;
2.2) Click Date in the Category list box;
2.3) Specify a date type;
2.4) Click OK. See the screenshot:

Then the selected serial numbers are converted to dates as shown in the screenshot below.

10.1.2 Convert serial numbers to dates with formula

You can also apply a formula as follows to convert a serial number to date in Excel.

1. Select a cell, enter the following formula and press the Enter key to convert the first serial number to date. Select this result cell, drag its AutoFill Handle down to get other dates.

=TEXT(B3,"m/d/yyyy")

Note: In the formula, you can change the “m/d/yyyy” to other date formats as you need.

10.1.3 Easily convert serial numbers to dates with an amazing tool

Have you ever tried to apply third-party tools to handle Excel tasks? If you want to increase your productivity and save your working time, here we recommend the Apply Date Formatting of Kutools for Excel. This feature helps to convert serial numbers in a range or multiple ranges to the date format you need.

1. Select one or more ranges that contain the serial numbers you want to convert to dates.

2. Click Kutools > Format > Apply Date Formatting.

3. In the Apply Date Formatting dialog box, you need to:

3.1) Select a date format in the Date formatting list box;
3.2) Click the OK button.

Note: You can preview the results in the Preview box after selecting a date format.

Then all selected numbers in the selected range(s) are converted to dates immediately.

Click to know more about this feature.


10.2 Convert 8-digit numbers to Excel recognized dates

10.2.1 Convert 8-digit numbers to dates with formulas

To convert an 8-digit number to an Excel recognized date, you can apply the DATE function in combination with the RIGHT, LEFT and MID functions. Please do as follows.

1. Select a cell, enter the following formula and press the Enter key to convert the first 8-digit number to date. Select this result cell, drag its AutoFill Handle down to get the rest of the dates.

=DATE(LEFT(B3,4),MID(B3,5,2),RIGHT(B3,2))

10.2.2 Convert 8-digit numbers to dates with the Text to Columns command

The built-in Text to Columns command can help to convert a range of 8-digit numbers to Excel recognized dates.

1. Select the range of 8-digit numbers, click Date > Text to Columns.

2. In the Convert Text to Columns dialog box, you need to configure as follows:

2.1) Click Next > Next in the Step 1 and Step 2 wizards separately;
2.2) In the Step 3 wizard, select Date, then select YMD from the drop-down next to the Date option;
2.3) Click Finish.

Then all selected 8-digit numbers are converted to Excel recognized dates.

10.2.3 Easily convert 8-digit numbers to dates with an amazing tool

Here we introduce the Convert to Date utility of Kutools for Outlook to help you easily convert 8-digit numbers in one or more ranges to Excel recognized dates in Excel with one click only.

1. Select the 8-digit numbers in one or more ranges.

2. Click Kutools > Content > Convert to Date. Then all selected numbers are converted to dates immediately as shown in the demo below.

Click to know more about this feature.


11. Convert number to time or vice versa in Excel

In this section, you will learn how to convert number to time or vice versa in Excel.

11.1 Convert number to time (hh:mm:ss) in Excel

To convert hours, minutes, or seconds in decimal format to a valid Excel time, you can try the corresponding method provided below.

11.1.1 Convert decimal hours to time (hh:mm;ss)

To convert hours in decimal format to hh:mm:ss Excel time format, you need to divide the number by 24 (represents the total number of hours in a day), and then format the cell as time. Please do as follows.

1. Select a cell, enter the following formula and press the Enter key. Select this result cell and drag its AutoFill Handle down to the cells where you want to apply the same formula.

=B3/$C$3

In this case, I have entered the number 24 into the cell C3. You can directly enter the number 24 into the formula, such as:

=B3/24

2. Select all the result cells, and then press the Ctrl + 1 keys simultaneously to open the Format Cells dialog box.

3. In the Format Cells dialog box, you need to:

3.1) Stay in the Number tab;
3.2) Select Time in the Category list box;
3.3) Specify a time format you need in the Type list box;
Tips:
If you only want to display a time of day, choose 13:30:55 in the Type list box. And the result will be:
36.75 > 12:45:00
If you want to display time intervals over 24 hours, choose 37:30:55 in the Type list box. And the result will be:
36.75 > 36:45:00
3.4) Click OK to save the changes.

Then the results are formatted as time format hh:mm:ss as shown in the screenshot below.

11.1.2 Convert decimal minutes to time (hh:mm;ss)

To convert minutes in decimal format to hh:mm:ss Excel time format, you need to divide the number by 1440 (represents the total number of minutes in a day), and then format the cell as time. The steps are the same as above.

1. Select a cell, enter the following formula and press the Enter key to get the first result. Select this result cell and drag its AutoFill Handle down to the cells where you want to apply the same formula.

=B3/$C$3

In this case, I have entered the number 1440 into the cell C3. You can directly enter the number 24 into the formula, such as:

=B3/1440

2. And then format the result cells as time. Please refer to the above steps.

Finally, you will get the time shown in the screenshot below.

11.1.3 Convert decimal seconds to time (hh:mm;ss)

The steps are the same as above to convert seconds in decimal format to hh:mm:ss Excel time format. You just need to change the divisor to 86400 (represents the total number of seconds in a day), and then format the cell as time.

In this case, the formula is as follows:

=B3/$C$3

or

=B3/86400

Then format the result cells as time. Please refer to the above steps.

Finally, you will get the time shown in the screenshot below.


11.2 Convert time (hh:mm:ss) to decimal number in Excel

This section demonstrates different formulas to convert time to hours, minutes, seconds in Excel.

11.2.1 Convert time to hours with formulas

Contrary to the above method, to convert time to hours, you need to multiply the time by 24 (represents the number of hours in a day).

1. Apply the following formula:

=B3*$C$3

where B3 contains the time value and $C$3 contains the number 24.

Or you can directly enter the number 24 into the formula as follows:

=B3*24

You can see the results are displayed as time format after applying the formulas.

Note: If you don’t want to keep the fractional part of the results, apply the formula below.

=INT(B3*24)

2. You need to format the result cells as Number format.

Select the result cells, press the Ctrl + 1 keys to open the Format Cells dialog box. In the dialog box, select Number in the Category list box under the Number tab, specify the number of decimal places in the Decimal places box, and then click OK to save the changes.

Then the result cells are displayed as numbers as shown in the screenshot below.

11.2.2 Convert time to minutes with formulas

To convert time to minutes, you just need to multiply the time by 1440 (represents the number of minutes in a day).

Apply the following formula:

=B3*$C$3

where B3 contains the time value and $C$3 contains the number 1440.

Or you can directly enter the number 1440 into the formula as follows:

=B3*1440

Note: If you don’t want to keep the fractional part of the results, apply the formula below.

=INT(B3*1440)

Then format the result cells as Number. Please refer to the above steps.

Finally, you will get the minutes as shown in the screenshot below.

11.2.3 Convert time to seconds with formulas

To convert time to seconds, multiply the time by 86400 (represent the number of seconds in a day).

Apply the following formula:

=B3*$C$3

where B3 contains the time value and $C$3 contains the number 86400.

Or you can directly enter the number 86400 into the formula as follows:

=B3*86400

Note: If you don’t want to keep the fractional part of the results, apply the formula below.

=INT(B3*86400)

Then format the result cells as Number. Please refer to the above steps.

Finally, you will get the seconds as shown in the screenshot below.

11.2.4 Easily convert time to hours/minutes/seconds with an amazing tool

After applying the above formulas, you still need to change the cell formatting to display the actual decimal numbers. If you have Kutools for Excel installed, its Convert Time utility can help you easily convert time to decimal hours, minutes, or seconds directly with several clicks only.

1. Select the range of times you want to convert to hours, minutes, or seconds.

2. Click Kutools > Content > Convert Time.

1) To convert time to hours, click Time to Hours;
2) To convert time to minutes, click Time to Minutes;
3) To convert time to seconds, click Time to Seconds.

Then all selected times are converted to hours, minutes, or seconds. At the same time, a dialog box pops up to tell you how many cells have been successfully converted, click OK to close it.

Click to know more about this feature.

That’s all for the cell conversion tutorial in Excel. Hope the methods provided above can solve your problems. If you have more cell conversion questions, please leave your comments.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations