Suppose you have a dataset in Excel that has long numbers. To improve readability, you may want to convert the numbers to text with commas. This tutorial shows three methods of converting numbers to text with commas in Excel.
How to Convert Numbers to Text with Commas in Excel
Method 1: Use the TEXT Function
The first method is the use of the TEXT function.
Suppose we have the following dataset that has long numbers in column A.
We want these numbers converted to text with commas in column B.
We use the following steps:
- Select cell B2 and type in the formula below:
|
1 |
=TEXT(A2,"#,#") |
- Press Enter on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
The values in column B are left-aligned meaning they are text strings.
Explanation of the formula
|
1 |
=TEXT(A2,"#,#") |
The TEXT function converts the number in cell A2 to text with commas in the custom number format specified by the code “#,#”.
Method 2: Use the FIXED Function
The second method is the use of the FIXED function.
Suppose we have the following dataset that has long numbers in column A.
We want these numbers converted to text with commas in column B.
We use the following steps:
- Select cell B2 and type in the following formula:
|
1 |
=FIXED(A2,0,) |
- Press Enter on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
The values in column B are left-aligned meaning they are text values.
Explanation of the formula
|
1 |
=FIXED(A2,0,) |
The FIXED function rounds the number in cell A2 to 0 (zero) number of decimals and returns the result as text with commas.
Method 3: Use the DOLLAR Function
The third method is the use of the DOLLAR function.
Suppose we have the following dataset that has long numbers in column A.
We want these numbers converted to text with commas in column B using the currency format.
We use the steps below:
- Select cell B2 and type in the following formula:
|
1 |
=DOLLAR(A2,0) |
- Press Enter on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
The values in column B are left-aligned because they are text strings.
Explanation of the formula
|
1 |
=DOLLAR(A2,0) |
The DOLLAR function converts the number in cell A2 to text with commas with 0 (zero) decimal points using the currency format.
Note: The settings on your computer determine the currency format used in the result returned by the DOLLAR function.
Free SEO add-in for Excel
You can use the add-in to add commas to numbers. Here’s how it works:
First, select the numbers to which you want to add commas.
Click the icon:
It opens a menu to the right.
Select the option at the bottom and click Humanize.
Conclusion
Sometimes we may need to convert long numbers to text values with commas in Excel to improve readability. This tutorial has explained three methods for converting numbers to text with commas in Excel.
The first method is the use of the TEXT function, and the second method is the use of the FIXED function. The third technique is the use of the DOLLAR function.