Occasionally it might recognize numerical data and determine that you are entering currency or dates, then apply that type of formatting to it. Other times you might be typing names or locations, or any type of primarily-text content, and it will format that as text. But you may be wondering how to convert text to numbers in Excel if you are having trouble getting formulas to execute properly. You can change the format for a range of cells in an Excel sheet by right-clicking on the data, choosing Format Cells, then selecting one of the options in the column on the left side of the window. But Excel for Office 365 lets you switch your data formats, and it will typically let you do whatever you want. This means that occasionally data will be switched to the wrong format accidentally, or may have been done intentionally rather than changing a formatting setting like commas or decimal places that can occur with certain types of data. Regardless of how data came to be formatted as text or numbers, you can follow our steps below to learn how to switch cell formats in a Microsoft Excel spreadsheet.
How to Switch Data in the Text Format to Numeric Values in Excel
Our guide continues below with additional information on how to convert number excel values into text values, or how to convert from text to number.
Microsoft Excel – Convert Number to Text and Vice Versa (Guide with Pictures)
The steps in this article were performed in the Microsoft Excel for Office 365 version of the application, but will also work in most other versions of Excel.
Step 1: Open the Excel spreadsheet containing the data that you need to re-format.
Step 2: Select the data.
Step 3: Right-click on the selected data and choose the Format Cells option.
Step 4: Click on the desired format type in the column at the right side of the window.
Depending on the formatting type you selected you might need to make some other adjustments on this window. For example, if you picked a currency format then you might need to choose whether or not to display the currency symbol in front of the numbers.
Step 5: Click OK when you are done to apply the changes to your data.
Our tutorial continues below with additional discussion about using the number to text converter in Excel via the Formatting dialog box.
More Information on Changing Cell Format Between Number Format and Text
The way that you select text doesn’t matter when you are converting text to numbers, date, money, or whatever. But you can make things a bit easier on yourself by selecting an entire column or an entire row at the same time. You can accomplish this by clicking the column letter at the top of the column, or the row number to the left of the row. This selects that entire range. You can then change the column data format or the row data format by right-clicking on the selection and choosing the Format Cells option like we have been doing in this article. While you might be trying to paste a copied text string or text value from another location, there might be more than just the characters that you see. Many types of text that you copy also have their own formatting applied to them, which can be included when you paste into word. One way to resolve this problem is to use options in the Paste Special dialog box. After you have copied data, right-click inside the cell where you want to paste it, then choose the Paste Special option. this lets you pick the type of cell data that you want to place into the empty cell so that you don’t give yourself a headache by trying to remove a bunch of unwanted formatting. Some of the other ways that you can manage the data in your spreadsheet can be found on the Data tab at the top of the window. For example, you can find a variety of Excel functions here that enable you to format numeric data and text data so that it fits properly in your spreadsheet. One such function is called “Text to Columns” and is found in the Data Tools group on the ribbon. If you select a range of text cells that contain, for example, two words, then you could click the Text to Columns button and it would automatically split all of that data into two columns, with one word in a cell in each column. This occurs with the help of the “Convert Text to Columns Wizard” that opens after you click the Text to Columns button. You can choose different ways to split your text or number data, either based on the number of characters, spaces, or commas; then you can specify the column data format as well. The text to column wizard is a useful Excel function that can give you a lot of control when you want to convert data from another location and Excel is forcing incorrect cell values on all the cells that you have imported. You can do a convert text to numbers Google Sheets action, too. when you open your file in Google Sheets, simply select the cells that you want to change. You can then click the Format tab at the top of the window, choose the Number option, then select the type of number formatting that you would like to use.
Conclusion
Hopefully, this article has provided you with more information on what you need to do if you have a number stored that needs to be text, or if you need to format cells with number formatting when you have text values. Microsoft Excel gives you a lot of control over the data that you enter into columns and rows but making sure that Excel can understand the type of data is a critical part of making sure that you have the right value function assigned to the information in your spreadsheet. When this is not the case, numbers formatted as text will not change to numbers. To be completely sure that numbers formatted as text are converted into numbers, you will need to use the formula to convert the numbers. If this causes any confusion, or if you need further information on how to convert numbers to numbers in Excel, then you can use the resources listed in this post to assist you.
Additional Sources
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites. His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well. Read his full bio here.
You may opt out at any time. Read our Privacy Policy
