When you try to format a cell as a number in Microsoft Excel, the cell remains formatted as text.
This issue occurs when the following conditions are true:
Below are our best 47 results for convert text to a number in excel, updated recently.
If the steps above didn't work, you can use this method, which can be used if you're trying to convert more than one column of text. Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter. Press CTRL + C to copy the cell. Select the cells that have numbers stored as text.
Numbers that are stored as text can cause unexpected results. Select the cells, and then click to choose a convert option. Or, do the following if that button isn't available.
Select a column with this problem. If you don't want to convert the whole column, you can select one or more cells instead. Just be sure the cells you select are in the same column, otherwise this process won't work. (See "Other ways to convert" below if you have this problem in more than one column.)
The Text to Columns button is typically used for splitting a column, but it can also be used to convert a single column of text to numbers. On the Data tab, click Text to Columns.
The rest of the Text to Columns wizard steps are best for splitting a column. Since you're just converting text in a column, you can click Apply right away, and Excel will convert the cells.
Press CTRL 1 (or 1 on the Mac). Then select any format.
Note: If you still see formulas that are not showing as numeric results, then you may have Show Formulas turned on. Go to the Formulas tab and make sure Show Formulas is turned off.
You can use the VALUE function to return just the numeric value of the text.
Insert a new column next to the cells with text. In this example, column E contains the text stored as numbers. Column F is the new column.
In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example it's cell E23.
Now you'll fill the cell's formula down, into the other cells. If you've never done this before, here's how to do it: Rest your cursor on the lowerright corner of the cell until it changes to a plus sign.
Click and drag down to fill the formula to the other cells. After that's done, you can use this new column, or you can copy and paste these new values to the original column. Here's how to do that: Select the cells with the new formula. Press CTRL C. Click the first cell of the original column. Then on the Home tab, click the arrow below Paste, and then click Paste Special > Values.
If the steps above didn't work, you can use this method, which can be used if you're trying to convert more than one column of text.
Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter.
Press CTRL C to copy the cell.
Select the cells that have numbers stored as text.
On the Home tab, click Paste > Paste Special.
Click Multiply, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.
Press CTRL 1 (or 1 on the Mac). Then select any format.
Replace a formula with its result
Top ten ways to clean your data
CLEAN function
There are several ways to convert text to numbers in Excel. Follow these stepbystep instructions for the four easiest methods, illustrated with screenshots.
A common frustration in Excel is when numbers are stored as text.
You will not be able to perform Excel tasks such as mathematical calculations, create charts from the values, or group them into ranges while the numbers are stored as text.
In this tutorial, you will learn how to recognize numbers stored as text and multiple ways to convert text to numbers.
Here’s what we’ll cover:
You can follow along with this workbook.
It is not always clear that a number is stored incorrectly. It can appear as a number, yet be stored as text. This causes much confusion for Excel users.
However, there are some things to look out for, and ways to accurately check it.
Firstly, Excel will always display text to the left of a cell and numeric values to the right of a cell. So, if a value is leftaligned, it is probably text.
This is just a clue and not guaranteed, because users can change the alignment of cell values. However, it is a great place to start and a good clue to look out for.
Another way of checking is to select the range of values and look at the calculations down on the Status Bar.
If the values are all numeric, you will probably see average, count and sum calculations. And if any are text, only the count is performed.
Once again, these calculations can be changed by users so it’s not a perfect check. However, it will work 95% of the time and is a fast and easy way of checking.
To be completely sure, we can use an Excel function named ISNUMBER. This function will return TRUE if the value is numeric and FALSE if it is not.
You only need to provide the ISNUMBER function with the value to check. In this example, the following formula was entered into cell C2.
=ISNUMBER(B2)
I have converted some of the values to numbers. So, even though they all look like numbers, the ISNUMBER formula returns FALSE, making it easy to identify the problem values.
The first technique we will look at to convert text to numbers is the ‘Convert to Number’ option provided in Excel.
It is quick and simple, but it is unfortunately not always available to you.
This option is available when numbers are stored as text as a result of downloading the data from a website or some database, or maybe copied and pasted between programs.
Also, it may be that someone accidentally typed an apostrophe before the number. This tells Excel to store the number as text, and is very useful for entering ‘fake’ numbers such as phone numbers and ID numbers.
A green triangle is shown in the top left corner of the cell if this option is available to you.
This triangle means that the value has failed one of Excel’s error check rules, and is used to identify other errors outside of just recognizing numbers stored as text.
To convert to numbers;
The values are converted. The green triangle has disappeared and the values are now right aligned. This is how we expect to see numbers.
Paste Special in Excel is a fabulous tool. It is no surprise that it can help us convert text to numbers too.
To do so, we need to use Paste Special to perform a simple calculation. When a text value is used in a calculation, the result is numeric.
5. Select Values and the Multiply operation. Click Ok.
Text to columns is another great way to quickly convert text to numbers.
4. In step 2 remove all delimiter checkboxes.
5. Step 3 asks about the value formatting. Leave it as General (the date option is great when working with date values). Click Finish.
You probably didn’t change an option during the Text to Columns process, and we could have clicked Finish before step 3.
However, you may have to consider if your value contains delimiters such as spaces or commas.
I like this method of converting text to numbers. It is quick and simple.
The previous methods are great, but they require manual intervention. A user will need to perform the steps to convert the text to numbers.
With a formula, we can automate the solution. It will not change the value in the cell containing text, but rather, create a clean version in another cell.
If the data is regularly changed, a formula will ensure that the values fed to a PivotTable, chart or other formulas continue to be numeric. And no manual intervention is required.
Using a formula is also great for combining with other functions. For example, converting results from text functions into numbers.
That is the example we will look at here.
In this example, we have data downloaded from a source and the values in column B are stored as text due to the dollar sign.
Using a formula, the number has been extracted successfully. However, the RIGHT and LEN functions used in the formula are text functions. So, the result is returned as text.
The VALUE function of Excel converts text that represents a number to a number.
Using this function, the result of the RIGHT function is converted from text to number.
The values can then be formatted as required. For example, in the accounting format with the dollar sign.
So there you have it: some simple methods for converting text to numbers. If you’re getting to grips with Excel in your quest to become a data analyst, you can try a free data analytics short course here. Keen to learn more Excel functions? Check out the following:
To convert simple text values to numbers, you can use the the VALUE function, or simply add zero as described below. In the example shown, the formula in C5 is: = VALUE ( B5 )
1
Copy all of your tabdelimited text. Tabdelimited text is a format for storing data from a spreadsheet as a text file. Each cell is separated by a tab stop, and each record exists on a separate line in the text file. Select all of the text you want to copy to Excel and copy it to your clipboard.
2
Select the cell in Excel that you want to paste into. Select the upperleftmost cell that you want your pasted data to appear in. Your pasted data will fill up the cells below and to the right of your starting cell.
3
Paste the data. In newer versions of Excel, and if your data was properly delimited with tab stops, the cells should fill out appropriately with the correct data. Each tab stop should translate directly into a new cell for the data. If all of your data appears in a single column, there's a good chance Excel's delimiter was changed from tabs to something else, such as a comma. You can change this back to tabs by using the Text to Columns Tool.
4
Select the entire column of data. If your tabdelimited data did not paste correctly, you can use Excel's Text to Columns tool to format it properly. To do this, you'll need to select the entire column that contains all of the data you pasted.^{[1] }
5
Open the Data tab and click "Text to Columns". You'll find this in the Data Tools group in the Data tab.
6
Select "Delimited" and click "Next". This will tell Excel that it will be looking for a specific character to mark cell divisions.
7
Select the character that your data is separated by. If your data is tabdelimited, check the "Tab" box and uncheck any other boxes. You can check different characters if your data was separated by something else. If your data was split by multiple spaces instead of a tab stop, check the "Space" box and the "Treat consecutive delimiters as one" box. Note that this may cause problems if you have spaces in your data that don't indicate a column division.
8
Choose the format of the first column. After selecting your delimiter, you'll be able to set the data format for each of the columns that are being created. You can select between "General", "Text", and "Date".
9
Repeat for additional columns. Select each column in the frame at the bottom of the window and choose the format. You can also choose not to include that column when converting the text.
10
Finish the wizard. Once you have formatted each of the columns, click Finish to apply the new delimiter. Your data will be split into columns according to your Text to Column settings.
Question
How do I copy and paste in to an excel cell mid sentence?
To copy and paste into an excel cell mid sentence, you'll have to go look at the formula bar and select the part of the text that you'd like. From the formula bar, you don't have to select the entire cell. Then, copy normally from that selection (Control C on Windows), and then go to the desired place and paste it. If you wish to paste into a cell mid sentence, again, go up to the formula bar and select where you want to paste it.
Question
What do I do if a 13 digit number in the first column is coming up short with E 13?
Rightclick on the cell with the 13 digit numbers. Select "Format "Cells in the dropdown menu. on the "Number" tab, under Category, select "Custom". Under "Type", select "0". Click "OK". Make sure your cell width is wide enough to show the numbers completely.
Ask a Question
wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are cowritten by multiple authors. To create this article, 9 people, some anonymous, worked to edit and improve it over time. This article has been viewed 377,479 times.
Coauthors: 9
Updated: January 12, 2020
Views: 377,479
Categories: Microsoft Excel
"Awesome! wikiHow is a brilliant site. It makes it so easy to learn!"
Data entry is one of the most important functions in Excel  and one of the most tedious, especially when the data is repetitive. This chapter shows you tricks for copying down data, fixing entered data, and helping your users enter data correctly by providing a predefined list of entries.
Read Excel 2013 Absolute Beginner's Guide or more than 24,000 other books and videos on Safari Books Online. Start a free trial today.
It’s important to differentiate types of data because Excel treats each differently. You tell Excel what kind of data is in a cell by how you type it into the cell or by how you format the cell. Data in Excel can fall into one of four categories.
You can’t combine types of data in a cell. You can type “5 oranges,” but Excel will see that as text. It won’t separate the “5” as a number and the “oranges” as text. If you want to deal with the 5 as a number, then you need to enter it into its own cell.
1
Research your own phone's manual. Texting doesn't vary that much depending on the phone, but there can be some slight differences, especially between older cell phones and smart phones.
2
Check the cost with your phone service provider. Be aware that you may have to pay to receive or send texts.^{[1] }
1
Go to your phone's home or menu page. Find the text messaging function in the menu. Usually you are looking for the icon that says “messages” or messaging” on the main menu of the phone.
2
Click on the messages icon. You should see a list of your old texts organized by recipient if you've texted people before. If you haven't, it will take you to a blank page.
1
Open the spot to create a new text message. On newer smart phones, choose “new message” or “write message” or choose the icon for creating a new message.
2
Type in the phone number of the person you want to message. You will type this in the message's “To:” field at the top of the message box. Make sure to include the area code.
1
Type the message. How you do this depends on your phone type. If you have a smart phone with a keyboard (push button or touch screen), you simply use that to type the message.
2
Add multimedia to your text. Perhaps you want to add a video or photo to your text. This is pretty easy to do.
1
Send the text. To send the text, after you've typed in your message in the message box, you will simply need to hit “send.”
2
Stay short. Texting format is usually pretty short. In fact, you will be limited in how much you can write. The purpose of texting is to allow people to send short bursts of messages to each other quickly.
1
Use texting language. Because texting is short and people are often texting others on the fly, a texting lingo has developed in which people use shorthand abbreviations for longer sentences.
2
Add emoticons to your text. Sometimes people add emoticons to their texts to indicate their emotion. For example, you can choose a smiley face emoticon. This is the most common and would indicate you're happy.
3
Sign up for text message alerts. It's possible to sign up to receive text alerts. Sometimes, commercial products will allow you to do this and will send you information about sales and things like that.
4
Send or receive a text from a landline. Some phone companies, like AT&T allow your to pay an extra charge to send and receive text messages from a landline.^{[6] }
Question
Should I type the text message all in CAPS?
THIS IS NOT A GOOD IDEA. It will make you sound like you are angry or shouting at the recipient.
Question
Is it possible to text to a landline?
Yes. In fact, some cell phones offer this service automatically. In other cases, contact your cell phone provider to see if it's available. You text to a landline the same way as to a cell phone. The service will convert the text into a voicemail that it sends to the landline. Just type in the phone number of the landline and send the text if you have the service automatically.
Question
Do I need the area code or just the last seven numbers?
You definitely need the area code. For example, if the area code is "123", you will need to include it before the other digits.
Question
When I text, sometimes it’s in blue and says delivered. Sometimes it’s in green and doesn’t say delivered. What’s the difference?
On an iPhone, blue means that the text was sent via iMessage, while green means it was sent as an SMS text message. It's still been delivered if it's green  unlike iMessage, SMS simply doesn't have the feature to say so.
Ask a Question
Coauthors: 15
Updated: October 27, 2021
Views: 138,080
Categories: Texting
17072018 · Switch to the Data tab, Data Tools group, and click the Text to Columns button. In step 1 of the Convert Text to Columns Wizard, select Delimited under Original data type, and click Finish. That's all there is to it! Convert text to number with a formula. So far, we have discussed the builtin features that can be used to change text to number in Excel. In many situations, a conversion can be done even …
The tutorial shows many different ways to turn a string into a number in Excel: Convert to Number error checking option, formulas, mathematic operations, Paste Special, and more.
Sometimes values in your Excel worksheets look like numbers, but they don't add up, don't multiply and produce errors in formulas. A common reason for this is numbers formatted as text. In many cases Microsoft Excel is smart enough to convert numerical strings imported from other programs to numbers automatically. But sometimes numbers are left formatted as text causing multiple issues in your spreadsheets. This tutorial will teach you how to convert strings to "true" numbers.
Excel has an inbuilt error checking feature that alerts you about possible problems with cell values. This appears as a small green triangle in the top left corner of a cell. Selecting a cell with an error indicator displays a caution sign with the yellow exclamation point (please see the screenshot below). Put the mouse pointer over the sign, and Excel will inform you about the potential issue: The number in this cell is formatted as text or preceded by an apostrophe.
In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of textnumbers:
Numbers  Strings (text values) 


In the image below, you can see the text representations of numbers on the right and actual numbers on the left:
There are a handful of different ways to change text to number of Excel. Below we will cover them all beginning with the fastest and easiest ones. If the easy techniques don't work for you, please don't get disheartened. There is no challenge that cannot be overcome. You will just have to try other ways.
If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a twoclick thing:
Done!
Another quick way to convert numerical values formatted as text to numbers is this:
Compared to the previous techniques, this method of converting text to number requires a few more steps, but works almost 100% of time.
To fix numbers formatted as text with Paste Special, here's what you do:
If done correctly, your values will change the default alignment from left to right, meaning Excel now perceives them as numbers.
It is another formulafree way to convert text to number in Excel. When used for other purposes, for example to split cells, the Text to Columns wizard is a multistep process. To perform the text to number conversion, you click the Finish button in the very first step :)
That's all there is to it!
So far, we have discussed the builtin features that can be used to change text to number in Excel. In many situations, a conversion can be done even faster by using a formula.
Microsoft Excel has a special function to convert a string to number  the VALUE function. The function accepts both a text string enclosed in quotation marks and a reference to a cell containing the text to be converted.
The VALUE function can even recognize a number surrounded by some "extra" characters  it's what none of the previous methods can do.
For example, a VALUE formula recognizes a number typed with a currency symbol and a thousand separator:
=VALUE("
,000")
=VALUE(A2)
To convert a column of text values, you enter the formula in the first cell, and drag the fill handle to copy the formula down the column:
For more information, please see VALUE formula to convert text to number.
Apart from textnumbers, the VALUE function can also convert dates represented by text strings.
For example:
=VALUE("1Jan2018")
Or
=VALUE(A2)
Where A2 contains a textdate.
By default, a VALUE formula returns a serial number representing the date in the internal Excel system. For the result to appear as an actual date, you just have to apply the Date format to the formula cell.
The same result can be achieved by using the DATEVALUE function:
=DATEVALUE(A2)
For more information, please see How to convert text to date in Excel.
The VALUE function also comes in handy when you extract a number from a text string by using one of the Text functions such as LEFT, RIGHT and MID.
For example, to get the last 3 characters from a text string in A2 and return the result as a number, use this formula:
=VALUE(RIGHT(A2,3))
The screenshot below shows our convert text to number formula in action:
If you don't wrap the RIGHT function into VALUE, the result will be returned as text, more precisely a numeric string, which makes any calculations with the extracted values impossible.
For more information, please see How to extract number from string in Excel.
One more easy way to convert a text value to number in Excel is to perform a simple arithmetic operation that does not actually change the original value. What can that be? For example, adding a zero, multiplying or dividing by 1.
=A2 0
=A2*1
=A2/1
If the original values are formatted as text, Excel may automatically apply the Text format to the results too. You may notice that by the leftaligned numbers in the formula cells. To fix this, be sure to set the General format for the formula cells.
That's how you convert text to number in Excel with formulas and builtin features. I thank you for reading and hope to see you on our blog next week!
30112018 · How to Convert Text to Numbers in Excel? (Step by Step) There are so many ways we can convert text to numbers in excel. We will see one by one. Using
There are so many ways we can convert text to numbers in excel. We will see one by one.
This is probably the simplest of ways in excel. Lot people use the apostrophe ( ‘ ) before they enter the numbers in excel.
Below are the steps for converting text to numbers excel option –
Now I am moving to another of changing the text to numbers. Here I am using Paste Special methodPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including rightclicking on the target cell and selecting paste special, or using a shortcut such as CTRL ALT V or ALT E S.read more. Consider the same data that I have used in the previous example.
This is the third method of converting text to numbers. This is a bit lengthier process than the earlier two, but it is always a good thing to have as many alternatives as possible.
In addition, a formula can convert text to numbers in Excel. VALUE function will do the job for us. Follow the below steps to learn how to do it.
This has been a guide to Convert Text to Numbers in Excel. Here we discuss how to Convert Text to Numbers in Excel using 1) Error handler, 2) text to the column, 2) paste special, and 4) Value Function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
Select all the cells that you want to convert from text to numbers. Go to Data –> Data Tools –> Text to Columns. In the Text to Column Wizard: In Step 1: Select Delimited and click on Next. In Step 2: Select Tab as the delimiter and click on Next. In Step 3: In Column data format, make sure General is selected.
03082020 · One of the most common annoyances that people cite with Excel is dealing with how numbers and text are formatted in cells. It’s especially annoying when numbers inadvertently get entered [...]
One of the most common annoyances that people cite with Excel is dealing with how numbers and text are formatted in cells. It’s especially annoying when numbers inadvertently get entered into a spreadsheet in text format.
When this happens, calculations and different formulas don’t work quite right, or may not work at all.
In this article you’ll learn how to identify when a column or row of numbers are actually formatted as text, and how to convert text to numbers so that they’ll work in formulas and calculations again. This is one of those basic Excel tips everyone should know.
There are several ways you can see if a number or set of numbers in a column or row is formatted as text in Excel.
The easiest way is to select the cell, select the Home menu, and under the Number group in the ribbon, note the number format displayed in the dropdown box.
If the dropdown box displays “text”, you know the cell is formatted as text format. If you want to perform numerical calculations on the cell using Excel formulas, you’ll need to convert it first.
In the case where someone has entered numbers in text format using the apostrophe in the cell, you’ll see a small green triangle indicating the value has been entered as text.
Note: Preceding a cell entry with an apostrophe forces the cell formatting to textbased.
If you’ve discovered, using either of the approaches above, that the numerical data is entered into the Excel sheet in text format, you can use any of the methods below to convert that text to numbers.
If you need to convert data that’s been entered into Excel with an apostrophe, you can easily convert it back to number format using the Convert to Number option.
1. First, select the cells you want to convert back to number format. You will see a yellow diamond appear near the selection with an exclamation symbol in the middle.
2. Select this symbol. From the dropdown, choose Convert to Number.
This will update all of the text based numbers you’ve selected to the General numeric data format.
You’ll know it worked when all the numbers in your selection switched from being left aligned to right aligned in the cells.
Another easy way to convert text to numbers in Excel is by converting over an entire column of values at once. You can do this using the Text to Column feature.
1. Select the entire column of data that you want to convert from text to numbers.
2. Select Data from the menu, and then select Text to Columns in the Data Tools section of the ribbon.
3. In the Wizard window, keep the default Delimited selected and select Next.
4. On the next Wizard page, keep the default Tab selected, and select Next again.
5. Finally, on the last page of the Wizard, make sure General is selected under Column data format. For the Destination field, you can either select a new column where you want the number data to go, or just keep the current selected column as is. Select Finish.
Now your data will all be converted to numeric values, which you can use in Excel formulas and calculations.
Note: You’ll notice that the actual cell formatting doesn’t change from Text to General even though the values themselves can now be used as numbers. However, if you set your output column to a new column, you will notice that the formatting of the new column is set to General. This is only a cosmetic issue and doesn’t affect how the numbers in the “Text” formatted column behave.
The easiest and fastest way to convert text to numbers in Excel is simply changing the cell formatting from the Home menu.
To do this:
1. Select all of the cells you want to convert. You can select an entire column (don’t include the header) if you want to convert all of the cells in a column.
2. Select the Home menu, and in the Number group on the ribbon, select the dropdown box with Text in it.
3. You’ll see a list of formats to choose from. Select General to convert to number format. Or you can select Number, Currency, Accounting, or Percentage if you want those specific number formats applied to your numerical data.
If you need to move text cells that contain numbers into a new cell or column, you can use the Paste Special feature.
1. Select the group of empty cells where you want to place your output of numeric data. Select Format Cells from the popup menu.
2. In the window that opens, make sure General is selected as the number format and select OK.
3. Select the entire column of cells you want to convert from text to numbers, rightclick, and select Copy.
4. Select the first cell in the empty column you formatted, rightclick the cell and select Paste Values. You’ll see all of the text formatted numbers pasted in the General number format.
This works because when you select Paste Values, it pastes only the values from the source cell and not the original cell formatting. Instead, it uses the destination cell formatting, which you configured in the first part of this process.
There is a special function in Excel that’ll convert a number formatted as text into a numeric value. This is the VALUE function.
To use this function, select the cell where you want the converted number to go and type:
=VALUE(G2)
Replace “G2” above with the cell that has the number you want to convert. If you’re converting an entire column of numbers, start with the first cell only.
Press Enter and you’ll see that the textformatted number has been converted to a Generalformat number.
You can then fill the rest of the empty column to the bottom of that column and the VALUE formula will convert the rest of the cells in the original column as well.
After using any of these options for reformatting your numbers, you may need to refresh cell data after applying the new formatting.
As you can see, there are a number of ways to convert text to numbers in Excel. The option you choose just depends on where you’re trying to place the output. It also depends whether you prefer using copy and paste, Excel formulas, or menu options.
Ultimately, each of these choices provides you with the same end result.
You can use the above formula in an adjacent cell and copy and paste it down to convert an entire column. If you want to remove the formula after, you can copy and paste them as values. Convert Text to Number with Paste Special Multiply. Multiplying by 1 is a great trick for converting text to values, but you might not want to use a formula for ...
You cannot convert text to number when the cell contains a number Symptoms. When you try to format a cell as a number in Microsoft Excel, the cell remains formatted as text. Cause. This issue occurs when the following conditions are true: In the Format Cells dialog box, you format a cell as text. You then type a number into that cell.
When you try to format a cell as a number in Microsoft Excel, the cell remains formatted as text.
This issue occurs when the following conditions are true:
In the Format Cells dialog box, you format a cell as text.
You then type a number into that cell.
In the Format Cells dialog box, you then format the cell as a number.
The cell remains formatted as text.
To convert cells that are formatted as text to numbers, follow these steps:
Select the cell that is formatted as text that you want to convert to a number.Notice that the Error Checking Options button appears if you select the cell or rest the mouse pointer over the cell. The cell should contain an Error Indicator in the upper left corner of the cell.NOTE: If the Error Checking Options button does not appear or you do not see an Error Indicator, turn on background error checking.
To do this in Microsoft Excel 2002 or in Microsoft Office Excel 2003, follow these steps:
On the Tools menu, click Options.
In the Options dialog box, click the Error Checking tab.
In the Settings section, click to select the Enable background error checking check box.
In the Rules section, make sure the Number stored as text rule is selected, and then click OK.
To do this in Microsoft Office Excel 2007, follow these steps:
Click the Microsoft Office Button, and then click Excel Options.
On the Formulas tab, click to select the Enable background error checking check box in the Error Checking section.
Under Error checking rules, make sure that the Numbers formatted as text or preceded by an apostrphe check box is selected, and then click OK.
On the Error Checking Options button, click the down arrow.
Notice that the Number Stored as Text menu appears.
Click Convert to Number.
Notice that the cell is converted to the number format and the Error Indicator no longer appears in the cell.
You can use his formula but just replace the letters in order in which you want them to appear. The First letter you put will be what is entered when you enter the number 1 in the cell. For example: If you put in =MID("JGHTS",A2,1) Where A2 is the cell in which you want to convert. Lets say for instance cell A2 contains the number 1, in the target cell, J should pop up.
If you have a list of letters of an alphabet, and now you want to convert these letters to their relative numbers, such as A=1, B=2, C=3… And in other cases, you need to reverse this option to change the numbers to their associated letters. Have you ever tried to solve this task in Excel worksheet?
To convert the letters to relative numbers, you can use the following formula:
1. In a blank cell besides your data, for example, cell D2, please enter below formula, and press Enter key.
=COLUMN(INDIRECT(B3&1))
2. Then drag the fill handle over to the range that you want to apply this formula, and the letters have been converted to their relative numbers as below screenshot shown:
It’s easy to manually convert the currency 7.6 to the currency word of three hundred fiftyseven dollars and sixty cents. But how about converting hundreds of numbers to currency words. Use the amazing Numbers to Currency Words feature of Kutools of Excel, and batch convert numbers to currency words easily in Excel. Full Feature Free Trial 30day!
Kutools for Excel  Includes more than 300 handy tools for Excel. Full feature free trial 30day, no credit card required! Get It Now
If you want to convert the integer numbers which are in the range of 1 to 26 to the corresponding letters, the following formula can do you a favor.
1. In a blank cell besides your data, for example, cell D3, please enter below formula, and press the Enter key.
=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",B3,1)
2. Then drag the fill handle over to the range that you want to contain this formula, and the numbers have been converted to their relative letters as you need. See screenshot:
Tip: (1) If the number is greater than 26, the formula will return empty; (2) If the number is less than 1, the formula will return #VALUE! error value;
(3) If the number is a decimal number, the formula will trim number to a whole number and then calculate.
If have installed Kutools for Excel, you can use its Numbers to Currency Words feature to convert all selected numbers to currency words in bulk in Excel.
Kutools for Excel Includes more than 300 handy tools for Excel. Full feature free trial 60day, no credit card required! Get It Now
1. Select the numbers you will convert to currency word, and click Kutools > Content > Numbers to Currency Words.
2. In the Numbers to Currency Words dialog, please check English or Chinese option as you need, and click the Ok button.
Now you will see all selected numbers are converted to currency words in bulk. See screenshot:
29122017 · Try the easiest ways to make your Excel convert Text to Numbers. Step 1: Retype Values in the Cells ; Step 2: Select "Edit Directly in Cell" and so on…
Excel is undoubtedly a powerful application of Microsoft Office suite. In this powerful application there are plenty of Excel functions allotted to perform complex tasks easily and effortlessly.
But in some cases, Excel becomes a big disaster.
Today in this article I am going to describe a common problem of how to convert text to number in Excel.
When the user import data from some source, like Access or text files then in some cases the numbers might get formatted as text in cells
Well, this is a quite frustrating situation as, when the numbers are formatted or stored as text the entire calculations and sorting goes wrong.
But don’t take tension as there are several methods that will help you to convert text to number in Microsoft Excel.
This issue causes the functions such as SUM and AVERAGE to ignore values in these cells. These text strings might hold an actual text in addition to the numbers you need to covert
However, if you want to become more productive in Excel and want to learn powerful Excel function and features, then Learn Advance Excel Easily.
To convert Excel text to number, try any of the belowgiven methods and check which help you out.
Please Note: Every given method assume you have already changed any cell number formatting in cells to General. Follow the steps to do so:
Now follow the given methods one by one:
On the Format menu,> click Cells and then > click Number tab for changing the number format of the cells. After that retype the numbers
Hope doing this will help you to convert Excel text to number.
To do this, follow the given steps and check if this helps you to convert text to number in Excel.
Doing this will help your Excel convert text to numbers, so here follow the steps to do so:
If you are getting any issue regrading pasting your data in Excel worksheet then check out our post: 5 Working Solutions to Fix Microsoft Excel Cannot Paste Data Error
This step works well if the data is arranged in a single column or row. This tip utilizes the TRIM function and the CLEAN function for removing extra spaces and nonprinting characters that might be imported with the file.
Following example assumes the data is in column A and starts in row 1 ($A
).Follow the given steps to use this example:
You can see the text that was in column A is in number format now.
Microsoft offers programming examples for illustration only, and without warranty either expressed or implied.
It includes but is not limited to, the implied warranties of merchantability or fitness for a particular reason.
In this article, we suppose that you are familiar with a programming language that is being confirmed and with the tools that are utilized for creating and to debug procedures.
Microsoft support engineers can help in describing the functionality of particular process but they would not change these examples to provide added functionality or build procedures to meet your particular requirements.
Create a Visual Basic for Applications macro to reenter the numbers in the chosen cell or range of cells.
Follow the steps to do so:
This method works best of data is arranged in a single column. The example presumes that the data is in column A and starts in row 1 ($A
). Follow the steps to do so:And that all your text is converted to numbers.
Another very interesting way to make your Excel Convert Text to Number is by using the VBA code. It is well applicable for Excel 2003/2007 application version.
It is found that in many cases all the abovementioned solution fails to work. Well in such situation Excel Convert Text to Number using VBA works superb.
This following VBA Macro code is provided in the Microsoft page to make Excel convert Text to Number.
Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub
If in case the above VBA code shows error on execution, then you need to do following changes in coding:
For Each xCell In Selection
xCell.Value = CDec(xCell.Value)
Next xCell
Try it, you will surely going to find this step too easy for converting text to number in Excel.
Well, this is all about how to convert text to numbers in Excel.
I tried my best to put together the possible steps that will help you out.
Follow the given steps one by one and easily make your Excel convert text to number.
Good Luck!!!
29112021 · 3 useful and effective VBA codes to convert text to number in excel. Download the practice workbook and try these now for free.
In Microsoft Excel, working with numbers is most common. Sometimes, we have to process a dataset with large amounts of numbers. But, you may find yourself in a situation where your numbers look like text. For this reason, you can not add, subtract, divide, multiply or perform any kind of operation. It always shows an error when you try to do this. In this tutorial, we will teach you how to convert text to number in Excel using VBA codes with suitable examples and proper illustrations.
Download this practice workbook
Convert Text to Numbers Using VBA.xlsm
Microsoft Excel is smart enough to understand the difference between Text and Number. It automatically converts them to their respective formats. But, sometimes it cannot do that because of some misuse and misinterpretations of the dataset. For that reason, it keeps that numbers as texts in your workbook.
Take a look at this dataset. Here, we have some numbers in a column.
Though we have numbers in the column, they all are leftaligned like the text. Now, click any cell. You will find this box beside the cell.
Now, hover the mouse cursor over the box. After that, you will see this message.
It shows that the cell is formatted as text. In this way, you can be sure whether the cells are formatted as text or not.
Though you can easily convert text to numbers manually, this tutorial is all about converting text to number using VBA codes. We recommend you learn and apply all these methods to your dataset. Surely, it will come in handy in a lot of situations.
This method is pretty simple and easy to use on any dataset. All you have to do is select the range of cells and insert it into the VBA code.
📌 Steps
1. First, press ALT F11 on your keyboard to open the VBA editor.
2. Click on Insert > Module.
3. Then, type the following code:
Sub ConvertTextToNumber()
With Range("B5:B14")
.NumberFormat = "General"
.Value = .Value
End With
End Sub
4. Save the file.
5. Then, press ALT F8. It will open the Macro dialog box.
6. Select ConvertTextToNumber and Click on Run.
In the end, this code will convert our text to numbers.
Read more: How to Convert String to Number in Excel VBA
In this method, we are using Loop and CSng functions. The CSng function basically takes any text as an argument and converts it to a single number. Our loop will go through each and every cell of the selected column. After that, we will pass each cell’s value to CSng function to convert it from text to number.
📌 Steps
1. First, press ALT F11 on your keyboard to open the VBA editor.
2. Click on Insert > Module.
3. Then, type the following code:
Sub ConvertUsingLoop()
For Each r In Sheets("Loop&CSng").UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r) Then
r.Value = CSng(r.Value)
r.NumberFormat = "General"
End If
Next
End Sub
4. Save the file.
5. Then, press ALT F8. It will open the Macro dialog box.
6. Select ConvertUsingLoop and Click on Run.
As you can see, we have converted our text to numbers using this VBA code.
Similar Readings
Now, previous methods were for the selected ranges. That means you have to manually input your range of cells in the code. But sometimes your dataset might be large. In those cases, you have to remember the range of cells. This method will overcome that problem. We know our dataset starts from Cell B5. But we don’t know where it might finish.
So we dynamically identify the last used excel row that has data in it using Cells(Rows.Count, "B").End(xlUp).Row
. It returns the last nonempty row number that we are concatenating with “B5:B“.
📌 Steps
1. First, press ALT F11 on your keyboard to open the VBA editor.
2. Click on Insert > Module.
3. Then, type the following code:
Sub ConvertDynamicRanges()
With Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value
End With
End Sub
4. Save the file.
5. Then, press ALT F8. It will open the Macro dialog box.
6. Then select ConvertDynamicRanges and Click on Run.
As you can see, we are successful in converting text to numbers using the VBA codes.
✎ Here, we are using column B for our dataset. If your data is in a different column, change the range of cells in VBA codes accordingly.
✎ The VBA codes will work only on the active sheet.
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about converting text to number in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excelrelated problems and solutions.
In this ArticleVALUE FunctionDATEVALUE FunctionTIMEVALUE FunctionTEXT to COLUMNSConvert Text to Number in Google Sheets This tutorial will demonstrate how to convert text to numbers in Excel and…
Return to Excel Formulas List
Download Example Workbook
Download the example workbook
This tutorial will demonstrate how to convert text to numbers in Excel and Google Sheets.
If you have a number stored as text in Excel, you can convert it to a value by using the VALUE function.
If you have a date stored as text in Excel, you can get the date value from it by using the DATEVALUE function.
This will only work however, if the date is stored as text.
If the date is stored as an actual date, the DATEVALUE Function will return an error.
If you have time stored as text, you can use the TIMEVALUE Function to return the time value. The TIMEVALUE function will convert the text to an Excel serial number from 0 which is 12:00:00 AM to 0.999988426 which is 11:59.59 PM.
Excel has the ability to convert data stored in a text string into separate columns. If there are values within this data, it will convert the value to a number.
All of the examples above work the same way in google sheets.
Selecting Convert to Number option. The same process is to be repeated by selecting the remaining entries of column C & clicking on Convert to Number as shown in the below image. Converting all Cells to Number Format. Once done, the green mark disappears, the apostrophe preceding the number is gone for good & the format of the cell is now ...
06052013 · Excel has a builtin feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.
Excel has a builtin feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns. In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.
Here's a quick guide for the keyboard shortcuts to select the range of cells you want to convert.
Shift Space bar – selects the entire rowShift Up/Down Arrow – selects multiple rows
_
The Menu Key is located between the Alt and Ctrl keys on the right side of the keyboard. If your keyboard does not have a menu key, you can use Shift F10 as an alternate.
_
I find the builtin feature to be the fastest, but there are other ways to accomplish this task:
Do you have a different or easier way? Please leave a comment.
Graphic skills In daily work, we often come across text formats that represent numbers.So how can we convert text to number? There are two methods. · Option 1 1. Click the Formulas tab > Insert Function to popup a dialog.. 2. Input “ VALUE ” in the text box and then select the desired cell in the new dialog. Then click OK.. 3. Drag the cell to fill the formula into …
In daily work, we often come across text formats that represent numbers. So how can we convert text to number? There are two methods.
· Option 1
1. Click the Formulas tab > Insert Function to popup a dialog.
2. Input “VALUE” in the text box and then select the desired cell in the new dialog. Then click OK.
3. Drag the cell to fill the formula into the rest of the cells.
Tips: This method is suitable for situations where multiple data needs to be processed.
· Option 2
WPS Spreadsheets identifies numbers intelligently. When numbers in the cells are formatted as text, the cells are marked with a green triangle on the top left.
1. Click the cell, and you can see a dropdown button.
2. Click the dropdown button and click Convert to Number.
Tips: This method is suitable for situations where only small amounts of data needs to be processed.
For all, easy your work with WPS office suite. Free download WPS Office for PC, Mobile, Windows, Mac, Android, iOS online.
Fields marked * are required please
13122016 · 3 Ways To Convert Text To Numbers In Excel: Convert To Number, Text To Columns, Paste Special Add Zero or Multiply By One
Sometimes you get data and something doesn’t look right. See below. The data in the left column is formatted as text. Therefore, it is useless in calculations (you can see that, when I summed the column, the result was zero).
What you will often want instead is for these “numbers” to be truly formatted as numbers — so that you can use them in formulas.
Below, I have converted the text in the left column to actual numbers in the right column. You can see that the SUM function now works.
If you didn’t notice above, when “numbers” are formatted as text, there’ll be little green triangles in the upper left of the cell. When you’ve selected one of those cells, a little yellow diamond with an exclamation point inside appears to the left of the cell. As shown below, when you hover over that yellow diamond, Excel will tell you it is formatted as text by saying, “the number in the cell is formatted as text or preceded by an apostrophe.” In this case there is no apostrophe so I know the data is formatted as text.
So how do I convert the text to numbers?
If you click on the little down arrow next to the yellow diamond, you’ll get a dropdown menu as shown below. Simply click on the option that says, “Convert to Number.” You can do this on one cell or on an entire range that you select.
Caution!
If you do this on a very large range with, say, 100,000 cells, it may take a very long time. In fact, it may even cause Excel to crash. This is happened to me numerous times. Therefore, I learned two other methods that do not suffer from this drawback, which I’ll share with you now.
Here’s another pretty fast method that doesn’t make Excel slow to a crawl. You will go to the Data tab as shown below, and basically “hack” the Text to Columns feature — after selecting your data.
You’re basically going to “pretend” to split a column into multiple columns, but not allow Excel to actually do that. In the process, Excel magically converts the text to numbers.
Below are the screenshots.
When you click on the Text to Columns button (after selecting your data), you’ll get the first of three screens as shown below. Choose “Delimited,” and then click on Next at the bottom.
This will bring up step two of the wizard, shown below. To make this work, just make sure none of the boxes are checked, as below. Then click Next to go to the third and final step.
Below is the step three window. Just leave “General” selected, click Finish and you’re done.
The only drawback with this method is that sometimes it just doesn’t seem to want to work. You try and try and it just won’t happen. I believe it has something to do with some rare and bizarre data formats.
In these cases you can try a third option which I’m about to show you.
In this third method you are performing a mathematical operation on the text by using Paste Special. In the process Excel magically converts that text into numbers.
The key is to perform an operation which will not change the value of the text/numbers. Most people either add 0 or multiply by 1. In my example below, I will add 0.
The fastest way to do this is simply to copy a blank cell, which Excel interprets as 0. This saves you the step of typing a number before copying it.
Next, select the cells where you want to convert the text into numbers.
Finally, rightclick, type “S” and then “S” again to get to this Paste Special menu below.
Once you’re in this menu, choose “Values” in the top section, and “Add” in the bottom section, then click on OK. Doing this adds 0 to each cell in your range, magically converting the text to numbers.
Even faster than clicking on “Values” and “Add,” just type “V” and then “D” and then click OK.
Using one big shortcut, you could do, RCSSVDTabTabTabSpace (RC stands for RightClick).
Now you have three different ways to convert numbers that are formatted as text into actual numbers, allowing you to use them in formulas.
To summarize:
Convert to Number: this is the easiest method, however it’s dangerous to use when you have extremely long columns.
Text to Columns: this method works fine with extremely long columns, but sometimes it just won’t work.
Adding 0: this method, while not the fastest, always seems to work.
29032022 · RELATED: How to Convert Text to Date Values in Microsoft Excel. Use the Warning Symbol. If you enter your data and see a warning symbol on the bottom left corner, this is a fast way to convert the text. When you hover your cursor over the warning, you’ll see an explanation as in the screenshot below.
When you enter numeric data into your spreadsheet, do you pay attention to how it’s formatted? Many times, numbers are formatted as text which can wreak havoc with formulas. Here are various ways to convert text to numbers in Excel.
Depending on how your number is entered as text, one method below may work better for you. For instance, you might have many nonadjacent cells where converting the cell format would be tedious. Or you might not see the warning symbol in order to use that method.
A quick and easy way to convert text to a number is by changing the cell format on the Home tab. Use the dropdown box at the top of the Number section. Choose “Number” from the list.
Alternatively, rightclick the cell(s) and pick “Format Cells.” Select the Number tab and pick “Number” on the left. Optionally adjust the Decimal Places and click “OK.”
You can also use the General format instead of Number if you like. General is Excel’s default number format. The difference with General compared to other number formats is that it uses scientific notation for numbers over 12 digits and rounds decimal numbers if the cell isn’t wide enough to accommodate the entire string.
RELATED: How to Convert Text to Date Values in Microsoft Excel
If you enter your data and see a warning symbol on the bottom left corner, this is a fast way to convert the text. When you hover your cursor over the warning, you’ll see an explanation as in the screenshot below.
Click the dropdown arrow next to the warning and choose “Convert to Number.”
This removes the warning and formats the text as a number.
Maybe you have an entire column of numbers formatted as text. Using the Text to Columns feature is the way to go. Note, however, that you can also use this option for a cell range.
RELATED: How to Use Text to Columns Like an Excel Pro
Select the column, go to the Data tab, and click “Text to Columns” in the Data Tools section of the ribbon.
When the wizard displays, simply click “Finish.” Alternatively, you can click “Next” through each step without making any changes and hit “Finish” at the end.
This changes the format to Excel’s default General format for numbers. You can leave the format asis or select the cells and choose “Number” in the Number section on the Home tab as described earlier.
You might have many cells or multiple columns that you need to convert to numbers from text. In this case, you can use the Paste Special with the Multiply operator.
RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel
Go to a blank cell that’s not formatted as text but as Number or General instead. Type the number 1 and press Enter or Return.
Copy the cell by rightclicking and selecting “Copy” or clicking the Copy button in the Clipboard section of the Home tab.
Select all of the cells that you need to convert to numbers from text.
Either rightclick and choose Paste Special > Paste Special or go to the Home tab and choose “Paste Special” in the Paste dropdown box.
Leave Paste set to “All” at the top. Under Operation at the bottom pick “Multiply”. Click “OK.”
This multiplies all values by the number 1, giving you the same numbers as results, and changes the format to that of the copied cell, either General or Number. You can then remove the number 1 you used.
The VALUE
function gives you one more way to convert text to numbers in Excel. With this option, you’ll use a column to the right to convert the text.
RELATED: How to Add and Remove Columns and Rows in Microsoft Excel
If you need to insert a new column, rightclick the column to the right of your text formatted column and pick “Insert.”
In the top cell (or second cell if you have a column header) enter the following replacing the cell references with your own.
=VALUE(A2:A6)
Alternatively, you can enter a single cell reference in the parentheses to convert one cell. Then, copy the formula down to the remaining cells using the fill handle.
=VALUE(A2)
You can then use the numbers in the new column as needed and do what you please with the original column.
Once you convert your text to numbers in Excel, your formulas are ready for their calculations with correctly formatted numbers!
To convert text to number you can apply the Excel VALUE function, using both Excel and VBA methods.
10032022 · 4 quick and easy ways to convert numbers stored as text to real numbers in Excel . Microsoft Excel depends on data that’s stored correctly. If …
Numbers stored as text in Microsoft Excel aren’t necessarily wrong. For instance, a street address that begins with numbers—555 Town Street—is stored as text. On the other hand, we often receive data from a foreign source that should be stored as a number, but it stored as text at the source. When this happens, we must convert that text into numbers before we can use the data.
SEE: 83 Excel tips every user should master (TechRepublic)
In this Microsoft Excel tutorial, I’ll show you four easy ways to convert numbers stored as text to real numbers. All four methods have their own pros and cons, so knowing them all will come in handy.
I’m using Microsoft 365 on a Windows 10 64bit system. I recommend that you hold off on upgrading to Windows 11 until all the kinks are worked out. For your convenience, you can download the demonstration .xlsx and .xls files. The first method doesn’t work in Excel Online, but the other three do.
Figure A shows a short list of text values. You can tell right away that they’re text because the numbers are left aligned, and they have leading zeros. If you check the Format setting in the Numbers group (on the Home tab), you can see that these values are text and not stored as real numbers. I set the Text format myself to simulate the problem. You can do so by selecting a small range and choose Text from the Number Format dropdown in the Number group.
Figure A
For my money, I like the SmartTag method the most because it converts the text values in place. The only real disadvantage is that selecting a long list of values might be a bit awkward, so I’ll show you a shortcut to make that easier.
To select the values quickly, select the first cell (B2) and press Ctrl Shift Down Arrow. Doing so will select all the contiguous values. (You must accommodate any blanks in the range.)
With the values selected, click one of the SmartTag indicators—the green triangle in the topleft corner of the cell—and do the following:
Figure B
Figure C
For better or worse, the conversion converts the values to General, not Number.
With this easy method mentioned first, you might be wondering why I’m going to show you more. Being able to select all the values at once is fairly new—originally, you couldn’t convert them all at once. So, if you’re using an older version, you need another solution. In addition, there’s no onesizefitsallsituations solution.
The Text to Columns feature is incredibly easy. It leaves the values in place and removes the leading zeros, but the values remain text, so you have to reset the format. Select the values as you did before and then do the following:
Unfortunately, the values are still stored as text. With the values selected do the following:
Figure D
This one takes a bit of work. You’d think you could change only the format, right? Unfortunately, it doesn’t work; you can change the format, but it leaves the leading zeros.
It’s possible that you might need to retain the source values, so the next two methods involve an expression and a function.
It isn’t unusual to need to retain the source data. When that’s the case, you won’t want to convert the original values in place. That’s when a simple expression such as
=textvalue * 1
comes in handy. Figure E shows the result of entering the expression
=B2*1
into cell C2 and copying it to the remaining cells.
Figure E
As you can see, the resulting values have no leading zeros, but are still stored as text, so do the following to change the format to Number:
If you’re thinking that there ought to be a function that will do both, there is.
You can skip a step by using the VALUE() function because it not only removes the leading zeros, but formats the resulting values as General, which has no specific format. You can go the extra step (shown above) to format the resulting values as Number, but in many cases, it won’t be necessary.
Figure F shows the result of entering
=VALUE(B2)
it to C2 and copying the function to the remaining cells.
Figure F
These four methods provide quick and easy ways to convert text to real numbers. Knowing your data will help you decide which to use. You might use them all at some time or another.
Using Value Function. You can convert a text to a number by using certain functions together. Enter the function =VALUE () in the cell where you want the text to be converted as a number. Let’s look at the example Excel sheet below. The value (55) as a text is in cell A2.
The Excel VALUE Function converts a text string that represent a number to a number. VALUE Syntax: =VALUE(text) In cell C2, execute the formula: =VALUE(B2) Click Enter and copy down the formula; In the caption below, we can see actual numbers in column C
Convert Text to a Number Using the Excel Value Function. The Excel Value function converts a text value into a number. This might be useful if you want to extract the numeric values from a more complicated text string. Value Function Examples Example 1. Column A of the spreadsheets below contain text representations of numbers.
Home » BasicExcel » ConvertTextToNumber
If your spreadsheet contains text representations of numbers, rather than actual numeric values, this may produce errors when you try to use these values in calculations. Therefore, this page describes four different methods that can be used to convert text to numbers.
Excel Value function converts a text value into a number. This might be useful if you want to extract the numeric values from a more complicated text string.Column A of the spreadsheets below contain text representations of numbers. The Excel Value function is used in column B of the spreadsheet, to convert the text values into numbers.
Formulas:
 Results:

In the spreadsheet below, the Excel Value function is used in combination with the Excel Left function, to extract the numeric value from the text string in cell A1 of the spreadsheet.
Formulas:
 
Results: 
Note that, if the text argument that is supplied to the Value function cannot be interpreted as a numeric value, the function returns the #VALUE! error.
Methods of Converting Excel Text to Numbers are discussed further on the Microsoft Office Support website.
Continue to the Convert Number To Text page >>
09102014 · This tutorial shows how to convert number to text in Excel 2016, 2013, and 2010. See how to accomplish the task with the Excel TEXT function and use number to string to specify the formatting. Learn how to change number format to text with the Format Cells… and Text to Columns options.
by Maria Azbel  updated on
This tutorial shows how to convert number to text in Excel 2016, 2013 and 2010. See how to accomplish the task with the Excel TEXT function and use number to string to specify the formatting. Learn how to change number format to text with the Format Cells… and Text to Columns options.
If you use Excel spreadsheets to store long and not so long numbers, one day you may need to convert them to text. There may be different reasons to change digits stored as numbers to text. Below you'll find why you may need to make Excel see the entered digits as text, not as number.
The same issue can occur if the cells are formatted as ZIP code, SSN, telephone number, currency, etc.
In this article I'll show you how to convert numbers to text with the help of the Excel TEXT function. If you are not so formulaoriented, have a look at the part where I explain how to change digits to text format with the help of the standard Excel Format Cells window, by adding an apostrophe and employing the Text to Columns wizard.
convertnumbertotextexcelTEXTfunction
The most powerful and flexible way to convert numbers to text is using the TEXT function. It turns a numeric value into text and allows to specify the way this value will be displayed. It's helpful when you need to show numbers in a more readable format, or if you want to join digits with text or symbols. The TEXT function converts a numeric value to formatted text, thus the result cannot be calculated.
If you are familiar with using formulas in Excel, it will not be a problem for you to employ the TEXT function.
=TEXT(C2,"0")
to the cell D2. In the formula, C2 is the address of the first cell with the numbers to convert. You will see a tiny triangle appear in the topleft corner of each cell in your helper column, which means the entries are now text versions of the numbers in your main column.
Now you can either rename the helper column and delete the original one, or copy the results to your main and remove the temporary column.
The result of =TEXT(123.25,"0")
will be 123.
The result of =TEXT(123.25,"0.0")
will be 123.3.
The result of =TEXT(123.25,"0.00")
will be 123.25.
To keep the decimals only, use =TEXT(A2,"General")
.
=TEXT(A12,"£#,###,###.##")
. Just type the format to use in quotes > insert the £ symbol by holding down Alt and pressing 0163 on the numeric keypad > type #,###.## after the £ symbol to get commas to separate groups, and to use a period for the decimal point. The result is text! If you need to quickly change the number to string, do it with the Format Cells… option.
You'll see the alignment change to left, so the format will change to text. This option is good if you don't need to adjust the way your numbers will be formatted.
If these are just 2 or 3 cells in Excel where you want to convert numbers to string, benefit from adding an apostrophe before the number. This will instantly change the number format to text.
Just doubleclick in a cell and enter the apostrophe before the numeric value.
You will see a small triangle added in the corner of this cell. This is not the best way to convert numbers to text in bulk, but it's the fastest one if you need to change just 2 or 3 cells.
You may be surprised but the Excel Text to Columns option is quite good at converting numbers to text. Just follow the steps below to see how it works.
I hope the tips and tricks from this article will help you in your work with numeric values in Excel. Convert number to string using the Excel TEXT function to adjust the way your numbers will be displayed, or use Format Cells and Text to Columns for quick conversions in bulk. If these are just several cells, add an apostrophe. Feel free to leave your comments if you have anything to add or ask. Be happy and excel in Excel!
Convert Text to Numbers in Excel. Below is the tutorial showing you the conversion process. The conversion process of the number actually influences the method that you will use. This tutorial will cover the following. The use of the option called ‘Convert to Number’ Text format change into Number/General. The use of Paste Special
What is the Excel TEXT Function? The Excel TEXT Function is used to convert numbers to text within a spreadsheet. Essentially, the function will convert a numeric value into a text string. TEXT is available in all versions of Excel. Formula =Text(Value, format_text) Where: Value is the numerical value that we need to convert to text
The Excel TEXT Function is used to convert numbers to text within a spreadsheet. Essentially, the function will convert a numeric value into a text string. TEXT is available in all versions of Excel.
=Text(Value, format_text)
Where:
Value is the numerical value that we need to convert to text
Format_text is the format we want to apply
We use the TEXT function in the following circumstances:
With the following data, I need to convert the data to “d mmmm, yyyy” format. When we insert the text function, the result would look as follows:
We use the old price and the discount given in cells A5 and B5. The quantity is given in C5. We wish to show some text along with the calculations. We wish to display the information as follows:
The final price is $xxx
Where xxx would be the price in $ terms.
For this, we can use the formula:
=”The final price is “&TEXT(A5*B5*C5, “$###,###.00”)
The other way to do it by using the CONCATENATE function as shown below:
When I use the date formula, I would get the result below:
Now, if we try to combine today’s date using CONCATENATE, Excel would give a weird result as shown below:
What happened here was that dates that are stored as numbers by Excel were returned as numbers when the CONCATENATE function is used.
How to fix it?
To fix it, we need to use the Excel TEXT function. The formula to be used would be:
We all know any zero’s added before numbers are automatically removed by Excel. However, if we need to keep those zeros then the TEXT function comes handy. Let’s see an example to understand how to use this function.
We are given a 9digit product code, but Excel removed the zeros before it. We can use TEXT as shown below and convert the product code into a 9digit number:
In the above formula, we are given the format code containing 9digit zeros, where the number of zeros is equal to the number of digits we wish to display.
If we wish to do the same for telephone numbers, it would involve the use of dashes and parentheses in format codes.
Here, I want to ensure the country code comes in brackets (). Hence the formula used is (##) ### ### ###. The # is the number of digits we wish to use.
To learn more, launch our free Excel crash course now!
It is quite easy to use TEXT Function in Excel but it works only when the correct format code is provided. Some frequently used format codes include:
Code  Description  Example 

# (hash)  It does not display extra zeros  #.# displays a single decimal point. If we input 5.618, it will display 5.6. 
0 (zero)  It displays insignificant zeros  #.000 would always display 3 decimals after the number. So if we input 5.68, it will display 5.680. 
, (comma)  It is a thousand separator  ###,### would put a thousands separator. So if we input 259890, it will display 259,890. 
Sometimes, the TEXT function will give an error “#NAME?”. This happens when we skip the quotation marks around the format code.
Let’s take an example to understand this.
If we input the formula =TEXT(A2, mmddyy). It would give an error because the formula is incorrect and should be written this way: =TEXT(A2,”mmddyy”).
Plus sign  
  Minus sign 
()  Parenthesis 
:  Colon 
{}  Curly brackets 
=  Equal sign 
~  Tilde 
/  Forward slash 
!  Exclamation mark 
<>  Less than and greater than 
Check out our Free Excel Crash Course and work your way toward becoming an expert financial analyst. Learn how to use Excel functions and create sophisticated financial analysis and financial modeling.
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
Using Excel Notification to Convert Text into Number. Whenever a number is in form of a text, excel notifies you by showing a green corner of cell. When you click on cell it shows a small exclamation icon at the left corner of cell. When you click on it, it shows an option of >Covert to Number. Click on it and text will be converted to number.
Many times, like when we extract numbers out of string, they be text by property. You can’t do any number operations on them. Then we get the need of converting that text to number. So to convert any text to number we can take various approaches depending on the situation. Let’s have a look to those approaches…
Use VALUE Function to Convert Text into Number
So if you downloaded sum data from a website, its common to get some numbers as text format special dates. Just wrap these text into VALUE function. It will return a pure number.
=VALUE(text)
So to convert a text in cell B2 into number write this VALUE formula.
=VALUE(B2)
Add 0 to text to Convert Text into Number
If you add any number using operator to text formated number, it will convert the text to number and then add the given number to converted number. Finally result will be a number.
For example if there is text formated number in B4, then just add 0 to convert string to number.
=B4 0
Result will be a number for sure.
As I told you in the beginning that this problem occurs when we extract number from string. So if you want to the extracted text to be number just add 0 in the end.
For example i am extracting citycode from text B6. I extracted it using LEFT function. But its not a number so I added a 0 in the formula itself.
=LEFT(B6,6) 0
Using Excel Notification to Convert Text into Number
Whenever a number is in form of a text, excel notifies you by showing a green corner of cell.
When you click on cell it shows a small exclamation icon at the left corner of cell.
When you click on it, it shows an option of >Covert to Number. Click on it and text will be converted to number.
15052019 · Another way to convert text to numbers is to use Copy, Paste Special Excel option: Select the numbers stored as text with errors, rightmouse click and choose Copy. Figure 4. Copy numbers stored as text. Rightmouse click again and choose Paste Special. In Paste Special choose Values and Add and press OK. Figure 5.
To convert text to number we can use error message that appears in the upper left corner of the cell, simple CopyPaste special option or VALUE function.
This step by step tutorial will assist all levels of Excel users in learning how to convert text to number.
We can easily recognize number formatted as text with a green triangle in the upper left corner of the cell. When we put the cursor on the triangle the error message will appear.
Figure 1. number stored as text
There are also other signs that number is formatted as text:
Figure 2. convert number to text
To convert string to number, follow the steps:
Figure 3. Convert text to number
Another way to convert text to numbers is to use Copy, Paste Special Excel option:
Figure 4. Copy numbers stored as text
Figure 5. Change text to number
Figure 6. change the text to number with Copy, Paste Special
Another way to change the text to a number is to use VALUE function:
=VALUE(B3)
Figure 7. How to convert text to number with VALUE function
The VALUE function has one argument, the number stored as text. When we copy the formula down to the other cells, Excel converts all text cells to number.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
11042022 · The first is the Serial Number [B] which represents the number of days since 1/1/1900. Excel uses this number for various date calculations. Below that large number is a smaller value [C] which also shows in the Formula result field. That number should be between 1 and 12. It represents the month number. Pin. Click OK.
Recently, I compiled a listing of posts from a friend’s website. Included in the file was a list of dates that represented when the post was published. He then asked if I could change the text string to numerical data. In this tutorial, I’ll show you how to convert the month name to a number using Excel’s MONTH function. For example, we’ll convert March to 3.
In this case, the post’s published date showed as “March, 2022”. And when I extracted the info, I pulled that text into a single field.
The problem was my friend couldn’t sort by the “Published date”. Since the data was text, the sort was alphabetical starting with April. He wanted a separate cell for the month and year so he could do time analysis.
Granted this isn’t a complex scenario. I could have done a “search and replace” on the list of month names.
If you’re not sure of a cell’s format, you can use the CELL function. If we were dealing with a date format, the result in cell D2 should’ve resulted with a “D” value and number for Date. As you can see below, I got “G” for “general”.
The first thing I needed to do was to split the Published Date column into Month and Year columns as requested. There are a number of ways to do this. If you’ve read my tutorial on how to split names in Excel, you know that the text to columns wizard is an easy solution.
I opted to split the Published date column into two columns by using both a comma and space for delimiters. And since I didn’t want a blank column with just the space, I ticked the consecutive delimiters box.
Now that I isolated the month into a separate column, I can go about converting the month text value to its corresponding month number. The MONTH function as defined by Microsoft states, “Returns the month, a number from 1 (January) to 12 (December).
I added an example spreadsheet you can use for practice called monthnameconversion.xlsx. You can find it at the bottom of the page.
You’ll notice that Excel displays two values in the Function Arguments dialog box. The first is the Serial Number [B] which represents the number of days since 1/1/1900. Excel uses this number for various date calculations. Below that large number is a smaller value [C] which also shows in the Formula result field. That number should be between 1 and 12. It represents the month number.
.
One reason the Excel formula month name conversion may not work is Step 6 above. Sometimes people use a formula with just the cell reference for the Serial_number. This produces a #VALUE! error. If you look closely at the Function Arguments dialog, you won’t see any month number results.
The fix is to concatenate or append an integer to the month’s cell reference. In my case, I used “1”. You can wrap the number in quotes if you prefer. This string forces a date calculation using the current year.
=MONTH(B2&1)
Another reason this might not work is misspellings. If for some reason March was misspelled, you would also get a #VALUE! Error. The good news is this formula does work on abbreviated month names.
Use the following steps to convert the text to number . Step 1: Select all the targeted cells you want to convert from text to number. Step 2: Click the yellow diamond icon that appears near the selected cells (at the top right). Step 3: Now, select and click the Convert to Number option.
When the numeric values are stored as text, the user cannot perform numeric operations on it. Number stored as text can generate unexpected errors on performing operations on them. The Excel users have to convert the text to number to perform the arithmetic operation on values that stored as a text string.
Excel allows its users to convert the number to text as well as text to number. In this chapter, we will show you several ways to convert the text data (number stored as text) to number.
Numbers store as text can mislead the result and lead to incorrect calculations when performing operations like SUM or AVERAGE. It is easy to determine when a number is stored as a text.
With the help of given points, you can determine the number stored as text 
These given differences will help you to understand the numeric value as number format and in text format.
Number as Number format  Number as text format 

Number is aligned to the right in the Excel cells.  Number stored as a text is aligned to the left in the Excel cells. 
On selecting the numeric values, Excel shows numeric functions like Average, Sum, Count in the status bar.  On selecting the numbers stored as text, Excel displays only COUNT method in the status bar. 
You will see a normally stored value as numbers.  You may see a leading apostrophe sign in the formula bar corresponding to the value. 
You can perform numeric operations on numbers without any error.  Any numeric calculation will lead to generating the error. 
Excel enables the various ways to convert the text (number as text) to number. These are the different methods that we will discuss in this chapter 
When the apostrophe is added before a number, it changes from number format to text format. In that case of converted number to text format, a green color small triangle is added to the top of left corner of the cell.
Use the following steps to convert the text to number 
Step 1: Select all the targeted cells you want to convert from text to number.
Step 2: Click the yellow diamond icon that appears near the selected cells (at the top right).
Step 3: Now, select and click the Convert to Number option.
Step 4: It will immediately convert the number stored in text format, to the number format.
Paste Special is another way to convert the text to number. Using this, you can convert the number stored in text format back to number format. Except the above methods, this method follows a long process and takes more steps.
It is a perfect method for converting the text to number. Follow the steps carefully 
Step 1: Select all the cells you want to convert from text format to number format.
Step 2: Set the format to General inside number group.
Step 3: Now, copy a blank cell using the Ctrl C shortcut copy key.
Step 4: One more time, select the text formatted cells for conversion and rightclick on the selected cell then choose the Paste Special.
Alternatively, you can also press the Ctrl Alt V shortcut key to open the Paste Special dialogue box directly.
Step 5: A dialogue box panel will open where choose Values in Paste section and Add in Operation section then click OK.
Step 6: If you have performed all steps correctly, your data is converted from text to number.
You will note that after converting the text to number, number is aligned to the right in the cell as the below Wireless and Speaker price.
As we told you that several methods are available in Excel to convert the number stored as text to the number. You can use the text to column option is one of the conversion methods in Excel. It is a formulafree way to convert the text to number.
Following are the simple steps for text to number conversion 
Step 1: Select the targeted cells for the text to number conversion.
Step 2: Go to the Data tab in the Excel ribbon and click Text to Column button inside the Data Tools group.
Step 3: A wizard will open where select Delimited radio button and click Next.
Step 4: On the next wizard, keep the options default and click Next.
Step 5: Select the column data format to General and click the Finish button to end the process.
Step 6: Selected number stored as text is now converted to number format.
Sometimes, you don't see a green color triangle at the left corner of the cell, as you see in all the above examples. But still, the numbers are in text format. You can find that the numbers are aligned on the right side of the cell. It means the numbers are in text format.
When a number is stored as text, you need to convert it back to as number to perform any numerical operations on it. For this type of number stored as text, you have to convert it back to number format. You can easily convert it by changing the cell format from text to number.
Following are the simple steps for it 
Step 1: Select all the cells (text formatted cells) for text to number conversion.
Step 2: Inside the Home tab, you will see that the selected data is stored as text in the Number section.
Step 3: Here, change the format from Text to General or Number.
Step 4: Text alignment will automatically change from right to left when the number is changed to the general or number format.
You can now perform any arithmetic or numeric operation on it.
Next TopicHow to remove dropdown in Excel
1. Select the range with numbers that you want to change to text. 2. Click Kutools > Content > Convert between Text and Number. See screenshot: 3. In the Convert between Text and Number dialog box, check the Number to text option, and then click the OK or Apply button. And all numbers have been converted to text in the original range.
Sometimes you may not want the numbers in the cells getting involved in calculating, or you may want to display leading zeros in numbers in cells. For doing so, you may need to change a number into text. The following methods can convert numbers in cells to text in Excel.
Convert number to text with Text function
Convert number to text with Format Cells command
Convert number to text with Kutools for Excel
If you are familiar with Microsoft Excel's formulas, you can convert numbers in cells to text with Text function.
If you just only want to convert the number to text without any formatting, you can use the formula: =TEXT(A1,"0");
1. In cell E1, please enter the formula =TEXT(A1,"0").
2. Then press Enter key. And select the cell E1, drag the fill handle over the range of cells that you want to apply this formula.See screenshot:
Note: As the data is formula, you can change the formulas to values. Copy the cells and rightclick where you want to paste the values and select Paste special > Values.
If you want to display the leading zero in numbers, you can use this formula: =TEXT(A1,"00000"); such as convert 23 to 00023.
As the above steps, if you apply this formula, you will get the following result:
And if you want to convert the numbers to text which keep several decimal digits, you can use this formula: =TEXT(A1,"0.000"). Such as convert 23 to 23.000.
Also with above steps, if you apply this formula, you will get the result as the following screenshots:
Note: you can define the text style. For example, if you want to convert the numbers to text with a telephone number format, you can replace the "000" with "0000000", or others.
Convert or spell out numbers to English or Chinese currency: With Kutools for Excel's Numbers to Words feature, you can spell out numbers into English and Chinese words in Excel.For example, it can spell out the "12.75" to "Twelve dollars and seventyfive cents". 
Microsoft Excel's Format Cells command is also able to convert numbers to text.
1. Select the numbers that you want to convert to text.
2. Right click the selected range, and choose the Format Cells item from context menu. See screenshot:
3. In the Format Cells dialog box, select the Text item in the Category box under Number tab, and then click the OK button. See screenshot:
4. Then numbers in selected range are converted into text.
If you have Kutools for Excel installed, it will be easier for you to convert numbers in cells to text with Convert between Text and Number.
After installing Kutools for Excel, please do as below:
1. Select the range with numbers that you want to change to text.
2. Click Kutools > Content > Convert between Text and Number. See screenshot:
3. In the Convert between Text and Number dialog box, check the Number to text option, and then click the OK or Apply button. And all numbers have been converted to text in the original range. See screenshot:
Click to Download Kutools for Excel and free trial Now!
Related article:
How to change or convert text to number in Excel?
Sometimes when importing data from another program into Microsoft® Excel®, the numbers are stored as text which causes havoc in your report. In some instances, the simple method of changing the format…
Sometimes when importing data from another program into Microsoft® Excel®, the numbers are stored as text which causes havoc in your report. In some instances, the simple method of changing the format of the column to numbers will work, but only on the most basic numbers formatted as text. In the below examples you will note that the numbers are displayed as text by the small green triangles appearing in the top left corner.
Here are 3 ways to convert your data starting with the most favoured method.
This method can be used to easily convert text to numbers by multiplying them by a number – usually 1 so that the values don’t change.
In our example, enter a 1 in the A1 cell. With A1 still selected, copy the cell either by using Ctrl C or by selecting Copy from the Clipboard section under the Home menu.
Select the cells containing the text to be converted, right click, select the arrow to the right of Paste Special and select Paste Special
In the Paste Special dialogue box, select Multiply and then OK.
The data has been converted to numbers and can be formatted
(This method is not recommended when using a very large data range)
Select the relevant cell range, i.e. A3:A12
Notice the small box with the exclamation to the right of the first cell. Hover over the box, click the dropdown arrow and then select Convert to Number
The text data is now converted to numbers. Notice that the green triangles in the top left corner are no longer visible and the numbers are now on the right hand side of the cell.
Select the relevant cell range, i.e. A3:A12
On the menu, go to Data and under Data Tools select Text to Columns
As no changes are required in the Convert Text to Columns Wizard, select Finish
It is important to now select the numbers again, go the Home menu and format the number into the required format.
In Excel, if you have text that represents a number that is not recognized as a number value some Excel formulas may not work correctly. In order to convert a text number into a number value you have a few options. Convert text …
The «Textnumber» macro. You can convert to the numbers are saved as the text into the numbers with helping the macro. There is the set of values stored in text format: For inserting the macro, on the «DEVELOPER» tab we find the Visual Basic editor (ALT+F11). The …
When importing files or copying of the data with numeric values, you can often meet the problem: a number is converted to a text. As a result, formulas do not work and calculations become impossible. How you can fix this problem quickly? At the beginning we will see how to fix the error without macros.
Excel helps to an user to determine immediately, whether the values in the cells are formatted as the numbers or as the text. Numeric formats are aligned on the right, text  to the left.
When importing files or crashing in Excel to the numeric format becomes text, the green triangle appears in the upper left corner of the cells. This is the sign of error. The error occurs also if you put the apostrophe before the number.
There are several methods of converting text to a number. So let`s consider the most simple and convenient methods.
The last method is suitable, if the values are in the same column.
You can convert to the numbers are saved as the text into the numbers with helping the macro.
There is the set of values stored in text format:
For inserting the macro, on the «DEVELOPER» tab we find the Visual Basic editor (ALT+F11). The editor window opens. For adding of the code, you need to press F7 and to paste the following code:
To make it work, you need to save it, but the Excel workbook must be saved in the format that supports macros.
Now we come back to the page with the numbers: to select the column with the data and press the button «Macros». In the opened window – there is the list of available macros for this book, chose: «DEVELOPER»«CODE»«MACROS» the right one and click «Run».
Sub Conv() With ActiveSheet.UsedRange .Replace ",","." arr = .Value .NumberFormat = "General" .Value = arr End With End Sub
The symbols moved to the right.
Consequently, the values in the cells are «became» by the numbers.
Download example transformation text to number in Excel.
If in the column we can see the arguments with the certain number of decimal places (for example, 3.45), you may use to another macro.
Read also: How to translate the number and amount in words in Excel.
In this way, when you import or copy to the numeric data error you can easy to eliminate. To convert the text in a number you may in many ways (with and without macros) and to make it simple and fast. And subsequently with numerical arguments all necessary operations are performed.
3 hours ago · The Base64 Encode data converter can be configured using the following properties: Description Type in a description to be shown in the list of data converters. …
How to convert Excel column name to a number in C#; How to convert a column number to an Excel column name in C#. For Excel 2003, the column name is able to from A to IV, it's 2^8 = 256 columns. For Excel 2007+, the column name is able to from A to XFD, it's 2^14 = 16384 columns. And the algorithm is converting a number calculated base 26. Here ...
Summary. To convert numbers into text values, you can use the TEXT function. In the example shown, the formula in C5 is: = TEXT( B5,"0") The result is the number 1021 formatted as text "1021". All numbers in column D are formatted as text with the formulas seen in column F.
Learn Excel From MrExcel, Podcast Episode 2143: ALT+D, E, F To Convert Numbers Stored As Text To Real Numbers. Alright, so, here I have a VLOOKUP formula. VLOOKUP formula is looking for 11,000 in this table and it is saying it can't be found, which is wrong.
August 31, 2017  by Bill Jelen
Excel convert text numbers to real numbers. Why aren't my VLOOKUPs working? Why won't this formula calculate? Maybe it is because your numbers are stored as text! Do you see a green triangle in the corner of each cell? If so, this will solve your problem.
Select the whole column. Alt D, E, F. The text numbers are converted to numbers. You are actually doing a default Text to Columns with this shortcut.
Learn Excel From MrExcel, Podcast Episode 2143: ALT D, E, F To Convert Numbers Stored As Text To Real Numbers.
Alright, so, here I have a VLOOKUP formula. VLOOKUP formula is looking for 11,000 in this table and it is saying it can't be found, which is wrong. It should be able to be found. These, though, are stored as numbers, these are stored as text. I need to convert these quickly from text numbers to numbers, and, yes, I could grab the mouse and come all the way up here and hover and click the arrow and choose CONVERT TO NUMBER, but it's just faster.
Hold down ALT D, E, F and it will quickly convert those text numbers to numbers. What are we doing in there? We’re doing DATA, TEXT TO COLUMNS, FINISH, and that solves the problem. ALT D, E, F: a fast way to solve that problem.
Thanks for stopping by. We’ll see you next time for another netcast from MrExcel.
Title Photo: Capri23auto / Pixabay
Select Convert to Number. We will get the number converted from text. Another method to convert text to numbers by using Paste Special command. Select the cell containing text. Type Ctrl + C to Copy the cell. Click on the cell where you want to paste it as value. Right click & click on Paste Special.
In this article we will learn about how to convert text to numbers in Microsoft Excel.
We all knows that in Excel by default numbers are aligned right side and text are aligned left side to the cell. In this article we will learn how to convert text strings that represent the numbers to numbers.
While importing data into Excel from another software or Internet website, Excel is not able to make any difference in Text & Numeric.
The first indicator of a number that gets converted to Text format is the alignment.
By default, text is left aligned & numbers are right aligned.
To change the text to number, we can use Value Function.
Value: Converts a text string that represents a number to a number
Syntax: =VALUE(text)
To convert number to text, follow the below mentioned steps:
To convert the text into number, we will use the Value Function.
Another method to convert text to numbers by using Paste Special command
The following code uses the CLng function to convert a string to an integer: 1. MsgBox CLng("13.5") The result is: Note: You can use the CInt or CLng function to convert a string to an integer or long (respectively) data types. The Long Data type is the same as …
Return to VBA Code Examples
You may be required to convert numbers stored as text to actual numbers in your VBA code. In this tutorial, we are going to go over the functions that you need to use to convert a string to an integer, long, double, decimal or currency data type (Click here to learn about converting numbers to strings)
You can use the CInt or CLng function to convert a string to an integer. If the fraction is less than .5 the function will round down, if the fraction is greater than or equal to .5 the function will round up. The following code will convert a string to an integer:
The result is:
The following code uses the CLng function to convert a string to an integer:
The result is:
Note: You can use the CInt or CLng function to convert a string to an integer or long (respectively) data types. The Long Data type is the same as an integer data type except larger numbers are allowed. In the past, the distinction was required because of memory constraints. In modern programming, there’s no reason not to use the long data type since memory is no longer an issue. So it’s always better to use a long data type instead of an integer.
You can use the Immediate Window to see how the value would be processed if not converted to an integer:
Debug.Print "13.5" "13.5" 
Usually, the text will be stored as a variable and this variable will need to be converted to a number data type as shown in the code below:
MsgBox CLng(valueOne) CLng(valueOne) 
You can use the CDbl or CDec function to convert a string to a decimal. The following code would convert a string to a double data type:
The result is:
The following code would convert a string to a decimal data type:
MsgBox CDec("13.57") CDec("13.4") 
The result is:
You can use the Immediate Window to see how the value would be processed if not converted to a double or decimal data type:
Debug.Print "13.57" "13.4" 
The result is:
Note: The decimal data type can store larger numbers than the double data type, so it’s always advisable to use the decimal data type when you are uncertain.
You can use the CCur function to convert a string to a currency. The following code would convert a string to a currency data type:
Range("A1").Value = CCur("18.5") 
The result is:
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam addin.
(No installation required!)
Free Download
Return to VBA Code Examples
Finally, if the numeric text is a date, it might use a different convention (MDY, DMY or YMD) than what Excel expects based on your regional settings. For example, if Excel is expecting 31 Dec 2019 in the form DMY, but the text is 12/31/2019, Excel cannot recognize that as a date because 31 is not valid "month".
If adding zero etc does not do the conversion, there might be extraneous characters that prevent Excel from recognizing the text as a number. Try the following in a parallel column, assuming the original data is in column A:
=CLEAN(TRIM(SUBSTITUTE(A1, CHAR(160), CHAR(32))))
If that does not return #VALUE, the conversion was completed. Copy the converted data and pastespecialvalue into column A. Then delete the formulas in the parallel column.
Alternatively, the numeric text might use the thousands separator and decimal point differently than what Excel expects based on your regional settings or Advanced Options. For example, the text might be 1.234.567,89 , but Excel expects 1,234,567.89 .
Finally, if the numeric text is a date, it might use a different convention (MDY, DMY or YMD) than what Excel expects based on your regional settings. For example, if Excel is expecting 31 Dec 2019 in the form DMY, but the text is 12/31/2019, Excel cannot recognize that as a date because 31 is not valid "month".
The correction for that is tricky because some ambiguous dates might have been interpreted as a wrong date. For example, 2/3/2019 might been converted to the numeric value for 2 March 2019, when you expected 3 Feb 2019.
Rather than continue to work through all of these examples, if the CLEAN/TRIM/SUBSTITUTE solution does not work for you, I suggest that you at least post examples of some of the text that cannot be converted.
Even better: upload an example Excel file that demonstrates the problems to a filesharing website, and post the download URL in a response here. I like box.net/files. But onedrive.live.com might be more convenient. In either case, try the download URL yourself when you are __not__ logged into the filesharing website.
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Details (required):
250 characters remainingWas this reply helpful?
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
01122014 · So I have this excel file that contains features from 211 cars and their values (e.g. fuel type, engine type, etc.). I have to do an analysis on that data using Orange, but I have one problem. A fe...
28062021 · In this article, I am going to discuss the 10 effective ways of converting numbers to text with leading zeros including real life examples
Leading zeros aid predominantly to specify the length of a number. In the case of maintaining records such as any special identity number, zip code, security numbers, etc., you have to keep leading zeros on cells.
However, when you try to enter a zip code like ‘00901’ of Puerto Rico in a cell, Excel truncates it to ‘901′ right away.
It’s extremely important for Microsoft Excel to deal with leading zeros. For this, you have to convert the number to text. In this article, I am going to discuss the ways of converting numbers to text with leading zeros.
Let me introduce the dataset first. Actually, nine postcodes are given without leading zeros, and good to say that the postcode consists of five digits.
Now, we have to add leading zeros and we may proceed with the following effective ways.
It’s a simple solution
You can add leading zero without any problems if you change the cell format from number to text.
This means Excel does not treat your number as an actual number and does not remove zeros.
So how can I do that? Just follow the steps
Steps:
Now, the cells are ready to show leading zeros. After inputting the postcode manually, we will get the following output.
Custom Format is a special type of format where you have numerous options to select in Format Cells. We will pick one from them.
Steps:
Thereafter, you will get the following
Adding apostrophe is the easiest way to keep the leading zeros before any number. The main advantage is that the apostrophe isn’t visible in the cell though it is available in the formula bar and you can edit the cell.
In the following dataset, if you type apostrophe (‘) character before inputting the ZIP code and press Enter (that means you have to type ‘04330 in lieu of just 04330), you’ll see the ZIP code with leading zeros.
The Excel TEXT function converts numbers into text in a workingsheet
The syntax of the function is
=TEXT(value, format_text)
The arguments of the function are the following:
Value: the value you want to convert to text
Format_text: the formatting to apply
Steps:
=TEXT(C5, "00000")
where C5 is the value of a ZIP code and “00000” is the desired formatting as the ZIP code number consists of five digits.RIGHT function returns all characters in a text string, depending on the number of characters you specify.
The syntax function is
The arguments of the function are the following:
Text: The text string containing the characters you want to extract.
Num_chars: Specifies the number of characters you want RIGHT to extract.
Steps:
=RIGHT("00000"&C5,5)
where C5 is the value of a ZIP Code, “00000” is the desired formatting and 5 is the number of charactersThe BASE function converts a number into a text representation with the given radix (base).
The main syntax of the function is
=BASE(Number, Radix [Min_length])
The arguments of the function are the following:
Number: The number that you want to convert.
Radix: The base radix that you want to convert the number into.
Min_length: The minimum length of the returned string.
Steps:
=BASE(C5,10,5)
where C5 is the number of a ZIP code, 10 is the base, and 5 is the desired length of characters.This function joins a string of two or more texts. The syntax is
=CONCATENATE(text1, [text2], …)
The arguments of the function are the following:
text1: the first item to join
text2: the second item
Steps:
=CONCATENATE("00", C5)
where “00” is the first item & C5 is the second item, mainly the number of a ZIP code.Note: Using CONCATENATE function you can add leading zero but you can not pad leading zeros.
A number of times, REPT can repeat a text.
The syntax of REPT function is
=REPT(text, number_times)
The arguments of the function are the following:
Text: The text you want to repeat.
Number_times: A positive number specifying the number of times to repeat text.
In addition, LEN returns the number of characters in a text string. The syntax is
=LEN(text)
Text Required. The text whose length you want to find.
The whole process can be done in the following steps.
Steps:
=REPT(0,5)
where 0 is the item to repeat and 5 is the times to repeat=REPT(0,5)&C5
=REPT(0,5LEN(C5))&C5
where C5 is the number of ZIP codes.
Returns text of a specified length by padding the start of the given text.
The syntax of the function is
=Text.PadStart(text, count as number, character to pad)
The arguments of the function are the following:
Text: the text you want to format (remember, it must in text format)
Count as number: The number of digits
Character to pad: The character you want to extract into the formatting
Steps:
Now we have to convert the ZIP code (number) into the text as the formula says.
At this moment, we have to use the function. Thus, we need to add a new column.
=Text.PadStart([#" ZIP Code (Number)"],5,"0")
where the column namely ZIP Code (Number) is input as text, 5 is the number of digits & 0 is the character to padThe final output you’ll get like the following:
Power Pivot is an Excel addin for powerful analysis and sophisticated data models. You can use this option.
Power Pivot can mash large numbers of data from different sources, quickly analyze information and easily share insights.
The function used here is FORMAT. The syntax of the function is
=FORMAT(value, format_text)
The arguments of the function are the following:
value: The value you want to convert
format_text: The desired formatting you want to apply
Steps:
First, you have to enable the addin Power Pivot. You can do this task by simply search Power Pivot in Excel and choose the option Enable AddIn. Please visit the link Start the Power Pivot addin for Excel – Excel (microsoft.com) for a detailed guide.
The final output of conversion of number to text is like the following
Be sure that the data is in text format before using the Text.PadStart function.
Besides, check the requirement whether you have to find added leading zeros or padded leading zeros.
As added leading zeros are just the added prefix before zeros of a number on the other hand padded zeros are merged with the number forming a specified number of digits.
Though all the ways discussed above are effective, it also depends on your requirements. So, choose the best one for you and your dataset. And please share your thoughts in the comments section below. Thanks for being with me.