How to convert Text to Number Format Excel
Have you ever noticed that numbers in Excel are aligned to the left, calculations don't work, or you see a green triangle in the corner of a cell? This usually means that the values are stored as text instead of numbers. When numbers are stored as text, Excel cannot perform mathematical operations correctly, causing errors in formulas, sorting, filtering, and PivotTables. Fortunately, converting text to number format is simple and can be done using several methods.
One of the easiest methods is using the Error Checking option. If a green triangle appears in the top-left corner of a cell, select the cells, click the warning icon, and choose Convert to Number. Excel will instantly convert the text values into numeric values.
Another common method is using the VALUE function. Suppose the text value is in cell A2. In another cell, type the formula:
=VALUE(A2)
Press Enter, and Excel will convert the text into a number. You can then copy the formula down for all rows and paste the results as values if needed.
You can also use the Text to Columns feature. Select the cells containing text numbers, go to the Data tab, click Text to Columns, and simply click Finish without changing any settings. Excel will automatically recognize the numeric values and convert them into proper numbers.
A faster method is using Paste Special. Type the number 1 in an empty cell and copy it. Then select the text-formatted numbers, choose Paste Special, select Multiply, and click OK. Since every number is multiplied by 1, Excel converts the text values into actual numbers without changing their values.
Sometimes numbers are imported with leading apostrophes ('12345), extra spaces, or hidden characters. In such cases, use the TRIM function to remove extra spaces:
=TRIM(A2)
or combine it with the VALUE function:
=VALUE(TRIM(A2))
This ensures that the data is cleaned and converted into a usable numeric format.
To verify whether the conversion was successful, check if the numbers are right-aligned (the default alignment for numbers in Excel). You can also test by using a formula like =SUM(A:A). If the total is calculated correctly, your numbers have been converted successfully.
Converting text to numbers is especially useful when working with imported data from CSV files, websites, accounting software, ERP systems, or databases. It ensures accurate calculations, proper sorting, filtering, chart creation, and reliable PivotTables.
In this tutorial, you'll learn multiple techniques to convert text into numbers in Excel, understand why this issue occurs, and discover the fastest method for different situations. Whether you're a beginner or an advanced Excel user, these tips will help you clean your data and improve your productivity.