Search results for: Convert text to a number in excel

Below are our best 47 results for convert text to a number in excel, updated recently.

Convert numbers stored as text to numbers

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.

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

Cells with numbers stored as text with green triangles

Numbers that are stored as text can cause unexpected results. Select the cells, and then click Excel Error Alert button to choose a convert option. Or, do the following if that button isn't available.

Selected column

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.)

Data tab, Text to Columns button

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.

Currency format selected

Press CTRL 1 (or Image of the MAC Command button icon 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.

New column next to text numbers

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.

Cell F23 with formula: =VALUE(E23) and result of 107.71

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.

Cursor resting on lower-right corner of cell

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 lower-right corner of the cell until it changes to a plus sign.

Cursor dragged down, cells filled with formula

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.

  1. Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter.

  2. Press CTRL C to copy the cell.

  3. Select the cells that have numbers stored as text.

  4. On the Home tab, click Paste > Paste Special.

  5. Click Multiply, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.

  6. Press CTRL 1 (or Image of the MAC Command button icon 1 on the Mac). Then select any format.

Replace a formula with its result
Top ten ways to clean your data
CLEAN function

Facebook LinkedIn Email

Convert Text to Numbers in Excel [4 Methods + …

There are several ways to convert text to numbers in Excel. Follow these step-by-step 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.

1. How to check if a value is numeric or text

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 left-aligned, 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.

A simple Excel spreadsheet containing data for

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.

An Excel spreadsheet containing data for

2. The ‘Convert to Number’ Excel option

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.

An Excel spreadsheet containing two columns of data, one for

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;

  1. Select the range of cells to change.
  2. Click the exclamation mark icon and click Convert to Number.

An Excel spreadsheet containing two columns of data, one for

The values are converted. The green triangle has disappeared and the values are now right aligned. This is how we expect to see numbers.

An Excel spreadsheet containing two columns of data, one for

3. Change text to numbers with Paste Special

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.

  1. Type the number 1 in a cell, any cell.
  2. Copy that cell value.
  3. Select the range of values you need to convert to numbers.
  4. Click Home > Paste list arrow and Paste Special.

An Excel spreadsheet containing two columns of data, one for

5. Select Values and the Multiply operation. Click Ok.

The dialog box which appears when you select

4. Using Text to Columns to convert text

Text to columns is another great way to quickly convert text to numbers.

  1. Select the range of values.
  2. Click Data > Text to Columns.
  3. Step 1 of the Text to Columns wizard regards splitting text between columns. We do not need this, so click Next.

The

4. In step 2 remove all delimiter checkboxes.

Step two of the

5. Step 3 asks about the value formatting. Leave it as General (the date option is great when working with date values). Click Finish.

Step three of the

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.

5. Convert text to numbers using formulas

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.

An Excel spreadsheet containing three columns of data: name, value, number. The first cell in the “number” column has been highlighted and a formula entered into the formula bar.

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.

An Excel spreadsheet containing three columns of data: name, value, number. The first cell in the “number” column has been highlighted and the

The values can then be formatted as required. For example, in the accounting format with the dollar sign.

Final thoughts and further reading

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:

exceljet.net

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 )

People also ask
  • How to paste a number into excel as text?

    Choose "General" for numbers or a mix of numbers and letters.Choose "Text" for data that is just text, such as names.Choose "Date" for data that is written in a standard date format.
    1. 1

      Copy all of your tab-delimited text. Tab-delimited 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. 2

      Select the cell in Excel that you want to paste into. Select the upper-leftmost 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. 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. 4

      Select the entire column of data. If your tab-delimited 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]

      • You can quickly select the entire column by clicking the letter at the top.
      • You can only use Text to Columns on a single column at a time.
    5. 5

      Open the Data tab and click "Text to Columns". You'll find this in the Data Tools group in the Data tab.

      • If you're using Office 2003, click the Data menu and select "Text to Columns".
    6. 6

      Select "Delimited" and click "Next". This will tell Excel that it will be looking for a specific character to mark cell divisions.

    7. 7

      Select the character that your data is separated by. If your data is tab-delimited, 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. 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".

      • Choose "General" for numbers or a mix of numbers and letters.
      • Choose "Text" for data that is just text, such as names.
      • Choose "Date" for data that is written in a standard date format.
    9. 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. 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.

    Add New Question
    • Question

      How do I copy and paste in to an excel cell mid sentence?

      Community Answer

      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?

      Community Answer

      Right-click on the cell with the 13 digit numbers. Select "Format "Cells in the drop-down 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 co-written 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.

    Co-authors: 9

    Updated: January 12, 2020

    Views: 377,479

    Categories: Microsoft Excel

    • Print
    • Send fan mail to authors
    Thanks to all authors for creating a page that has been read 377,479 times.
    • Juan Lyon

      "Awesome! wikiHow is a brilliant site. It makes it so easy to learn!"

    Share your story
    How to Paste Numbers as Text
  • How to fix numbers stored as text in Excel?

    Select the range consisting of all the cells you need to convert (making sure that the first cell in the range needs to be converted). ...Click the warning symbol in the first cell.From the drop-down, select Convert to Number, and all cells in the selected range will be modified, turning the numbers to true numbers.

    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.


    This chapter is from the book

    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.

    • Numbers—Numeric data that can be used for calculation purposes.
    • Text—Alphabetic or numeric data that is not used for calculation purposes. Examples of numeric text are phone numbers or Social Security numbers.
    • Dates and Times—Although dates and times may be considered alphanumeric, there are occasions where you might want to perform calculations on the values, so it is important to identify the data correctly to Excel.
    • Formulas and Functions—It’s important that Excel knows you’re entering a formula or it will treat what you enter like text. This topic is covered in detail in Chapter 5, “Using Formulas.”

    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.

    How to Fix Excel Numbers That Don't Add Up
  • How do you format a text formula in Excel?

    Click on the Format button to open the Find Format menu.Go to the Alignment tab.Check the Merge cells option.Press the OK button in the Find Format menu.Press the Find All or Find Next button in the Find and Replace menu.
    VLOOKUP For Text
  • How do you text to a number?

    If you want to reply to a text message, it works the same way. ...Then hit “send” after you've completed your reply, and it will text the reply back to the person who wrote to you. ...To forward a text message, tap and hold the message. ...Again you will have to type the recipient's phone number into the To: field and send it.
    1. 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.

      • For example, you might see “messaging” on a phone's menu to start the texting process. Or you might see “Send SMS” instead. It means the same thing.
      • Newer smart phones will offer keyboards that are touch screens. You will use this to type the message. On older phones, you will need to use a keyboard built into the phone itself, that doubles as the buttons you press with numbers to call people.
    2. 2

      Check the cost with your phone service provider. Be aware that you may have to pay to receive or send texts.[1]

      • Some cell phone plans have unlimited texting built into the plan. If you're going to be texting a lot, this might be a better idea. Some plans will only let you send a certain number of texts per month before incurring overcharges, though.
      • If you don't have an unlimited plan, you could be charged something like 20 cents per text, so make sure you research the costs beforehand.
    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.

      • On some phones, it will say messages on the bottom of the screen. Choose it. Select “new text message.”
      • With an iPhone, you may see a green messages box on the home page. Touch it or click OK, depending on the phone.[2]
    2. 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.

      • To continue texting a person you've texted before, simply click on their name or phone number first.
      • If you've never texted the person, you will need to have their phone number handy in order to text them. You will be texting directly to their cell phone (or landline).
    1. 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.

      • This icon is a sign in some phones. In iPhones, it's a little square with a line. This should open up the new text message box.
      • When you click on the icon, a new message box opens. This is where you will type your message. First you need to decide who you will be sending the text to and obtain their contact information.
    2. 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.

      • You could also select the number from the contact list in your phone's address book. Just type their name in the field, and it might autofill the number in for you. If you've texted the person before, when you start typing in the number, it might also show up automatically for you.
      • To send a text to more than one person with a smart phone, simply click on the plus sign icon. Or you can just add more contacts in the To: field by adding more phone numbers or names (if they are in your contact list).
    1. 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.

      • If you have an older handset that has a numeric keypad, you will see letters under each of the numbers you use to call people. You may need to press the key several times to enter the letter you want if the number key has several letters on it.
      • On some phones you will press “O” to type a space. If you have a newer smart phone, like an iPhone or Android, you can just tap out letters on a touch screen keyboard, so it will be a lot easier.
    2. 2

      Add multimedia to your text. Perhaps you want to add a video or photo to your text. This is pretty easy to do.

      • Click the camera icon for some newer smart phones. The phone's photo library will pop up. You can select a preexisting photo of video from it. Clicking on it will add it to your text.
      • Some newer phones also have a “take photo or video” function. If you click it, you can take a photo or record a video right into the text message.
      • Some phones also allow you to record an audio text. To do this, click on the little microphone sign (or similar icon) on the bottom right of the text message box. Tap and hold to send audio. This allows you to record your voice instead of texting via written word.
    1. 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.”

      • If you want to reply to a text message, it works the same way. You click on the text you've received, and then type your reply in the smaller message box that appears at the bottom of the screen.
      • Then hit “send” after you've completed your reply, and it will text the reply back to the person who wrote to you. The benefit of a text message is it should reach the recipient immediately.
      • To forward a text message, tap and hold the message. Select the option to forward the message. You will probably find this under the “options” menu.
      • Again you will have to type the recipient's phone number into the To: field and send it.
    2. 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.

      • You can only write 160 characters for each text, so get to the point. Think of a text email as a short email.[3]
      • You can keep texting, but the cell phone will break your text into multiple texts. Watch your tone. People can misperceive it, thinking you are angry when you are not. Stay polite in text messages and remember that people could save them to use against you.
    1. 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.

      • You may receive these codes from someone texting you, and you will need to know what they mean. Some common ones are: BRBR (Be right back). BTW (by the way). IDK (I don't know). IMO (in my opinion).
      • Other codes include LOL (laugh out loud). ILY (I love you). FWIW (For what it's worth). And CUL8R (See you later).[4]
    2. 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.

      • There are also emoticons for kisses, love, anger, sadness, crying, and basically every emotion you might be feeling. People use emoticons in texts to express how they are feeling in a way that is less ambiguous.[5]
      • Newer smart phones, such as iPhones, are going to have more complex and elaborate emoticons than the typical smiley faces. You will usually see the emoticons as a choice at the bottom of your keyboard screen or text message box when texting.
    3. 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.

      • Sometimes people sign up for text alerts because they want to receive information about breaking news, traffic, or weather information.
      • Some television shows allow automated voting or contest participation via text messaging. It's also possible to forward your emails to your mobile device.
    4. 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]

      • You can also send text messages to landline phones using “text to landline” services.[7] The service will convert your text into a voicemail.
      • You create and text the message the same way, simply plugging in the number of the landline. However, you may have to purchase the service first. Call your phone provider. Some phone plans include text to landline automatically. If the landline is eligible to receive the text, the message will be sent, usually with a recorded female voice. Some landline recipients can reply with voice message.
    Add New Question
    • Question

      Should I type the text message all in CAPS?

      Community Answer

      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?

      Community Answer

      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?

      Community Answer

      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?

      Community Answer

      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


    • Thanks!

    wikiHow Staff

    This article was co-authored by wikiHow Staff. Our trained team of editors and researchers validate articles for accuracy and comprehensiveness. wikiHow's Content Management Team carefully monitors the work from our editorial staff to ensure that each article is backed by trusted research and meets our high quality standards. This article has been viewed 138,080 times.

    Co-authors: 15

    Updated: October 27, 2021

    Views: 138,080

    Categories: Texting

    • Print
    • Send fan mail to authors
    Thanks to all authors for creating a page that has been read 138,080 times.
    • Teresa Miller
      "The whole article was very useful to me, but the part about adding multimedia was something I have never used before because I didn't know how. Thank you for this tutorial."..." more
    Share your story
    How to Text Someone: 14 Steps (with Pictures)
Excel: convert text to number with formula and other ways ...

17-07-2018 · 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 built-in features that can be used to change text to number in Excel. In many situations, a conversion can be done even …

17-07-2018

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.

How to identify numbers formatted as text in Excel

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.
Number formatted as text in Excel.

In some cases, an error indicator does not show up for numbers formatted as text. But there are other visual indicators of text-numbers:

Numbers Strings (text values)
  • Right-aligned by default.
  • If several cells are selected, the Status Bar shows Average, Count and SUM.
  • Left-aligned by default.
  • If several cells are selected, the Status Bar only shows Count.
  • The Number Format box displays the Text format (in many cases, but not always).
  • There may be a leading apostrophe visible in the formula bar.

In the image below, you can see the text representations of numbers on the right and actual numbers on the left:
The text representations of numbers and actual numbers in Excel

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.

Convert to number in Excel with error checking

If your cells display an error indicator (green triangle in the top left corner), converting text strings to numbers is a two-click thing:

  1. Select all the cells containing numbers formatted as text.
  2. Click the warning sign and select Convert to Number.

Done!
Excel's Convert to Number error checking feature

Convert text into number by changing the cell format

Another quick way to convert numerical values formatted as text to numbers is this:

  1. Select the cells with text-formatted numbers.
  2. On the Home tab, in the Number group, choose General or Number from the Number Format drop-down list.

Convert text to number by changing the format.

Note. This method does not work in some scenarios. For example, if you apply the Text format to a cell, enter a number, and then change the cell format to Number, the cell will remain formatted as text.

Change text to number with Paste Special

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:

  1. Select the text-number cells and set their format to General as explained above.
  2. Copy a blank cell. For this, either select a cell and press Ctrl C or right-click and choose Copy from the context menu.
  3. Select the cells you want to convert to numbers, right-click, and then click Paste Special. Alternatively, press the Ctrl Alt V shortcut.
  4. In the Paste Special dialog box, select Values in the Paste section and Add in the Operation section.
  5. Click OK.

Change text to number with Paste Special.

If done correctly, your values will change the default alignment from left to right, meaning Excel now perceives them as numbers.

Convert string to number with Text to Columns

It is another formula-free 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 multi-step process. To perform the text to number conversion, you click the Finish button in the very first step :)

  1. Select the cells you'd like to convert to numbers, and make sure their format is set to General.
  2. Switch to the Data tab, Data Tools group, and click the Text to Columns button.
  3. 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 string to number with Text to Columns.

Convert text to number with a formula

So far, we have discussed the built-in 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.

Formula 1. Convert string to number in Excel

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:
VALUE formula to convert a string to a number.

For more information, please see VALUE formula to convert text to number.

Formula 2. Convert string to date

Apart from text-numbers, the VALUE function can also convert dates represented by text strings.

For example:

=VALUE("1-Jan-2018")

Or

=VALUE(A2)

Where A2 contains a text-date.

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.
VALUE formula to change a text string to a date.

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.

Formula 3. Extract number from string

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:
Extract a number from a text string.

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.

Change Excel string to number with mathematic operations

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 left-aligned numbers in the formula cells. To fix this, be sure to set the General format for the formula cells.
Converting a string to a number with mathematic operations

That's how you convert text to number in Excel with formulas and built-in features. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

How to Convert Text to Numbers in Excel? (Using 4 Easy ...

30-11-2018 · 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

30-11-2018

There are so many ways we can convert text to numbers in excel. We will see one by one.

#1 Using quick Convert Text to Numbers Excel Option

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 –

  1. Select the data.


    Quick Convert Text to numbers Step 1

  2. Click on the error handle box and select the Convert to Numbers option.


    Quick Convert Text to numbers Step 2

  3. This would convert the text-formatted numbers to number format instantly, and now the SUM function-working fine and showing the accurate result.


    Quick Convert Text to numbers Step 3

#2 Using Paste Special Cell Formatting Method

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 right-clicking 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.

  • Step 1: Type the number either zero or 1 in any one cell.
Using Cell Formatting Method step 1
  • Step 2: Now copy that number. (I have entered the number 1 in the cell C2).
Using Cell Formatting Method step 2
  • Step 3: Now, select the numbers list.
Using Cell Formatting Method step 3
Using Cell Formatting Method step 4
  • Step 5: This would instantly convert the text to numbers instantly, and the SUM formula working fine now.
Using Cell Formatting Method step 5

#3 Using the Text to ColumnText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more Method

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.

Using Text to Column Step 1
  • Step 2: Click on the Data tab and Text to Columns option.
Using Text to Column Step 2
  • Step 3: This will open up the below dialogue box and make sure delimited is selected. Click on the Next button.
Using Text to Column Step 3
  • Step 4: Now make sure the Tab box is checked and click on the Next button.
Using Text to Column Step 4
  • Step 5: In the next window, select the General option and select the destination cell and click on the Finish button.
Using Text to Column Step 5
  • Step 6: This would convert your text to numbers, and SUM will work fine now.

#4 Using the VALUE Function

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.

  • Step 1: Apply the VALUE formula in cell B1.
Using VALUE Function step 1
  • Step 2: Drag and drop the formula to the remaining cells.
Using VALUE Function step 2
  • Step 3: Apply the SUM formula in the cell B6 to check whether it has converted or not.
Using VALUE Function step 3

Things to Remember

  1. If you find the green triangle button in the cell, then there must be something wrong with the data.
  2. VALUE function can be useful in converting the text string that represents a number to a number.
  3. If there are spacing problems, we nest the VALUE function with trim function, For example, =Trim(Value(A1))
  4. Text to Column is also useful in correcting dates, numbers, and time formats.

Recommended Articles

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 –

All in One Excel VBA Bundle (35 Courses with Projects)
  • 35 Courses
  • 120 Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
trumpexcel.com

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.

5 Ways To Convert Text to Numbers In Excel

03-08-2020 · 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 [...]

03-08-2020

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.

Is Cell Data Text Or Numbers?

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 text-based.

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.

1. Convert To Number

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.

2. Using Text to Column

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.

3. Changing Cell Format

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.

4. Using Paste Values

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 pop-up 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, right-click, and select Copy.

4. Select the first cell in the empty column you formatted, right-click 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.

5. Using The VALUE Function

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 text-formatted number has been converted to a General-format 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.

howtoexcel.org

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 ...

support.microsoft.com

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:

  1. 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:

    1. On the Tools menu, click Options.

    2. In the Options dialog box, click the Error Checking tab.

    3. In the Settings section, click to select the Enable background error checking check box.

    4. 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:

    1. Click the Microsoft Office Button, and then click Excel Options.

    2. On the Formulas tab, click to select the Enable background error checking check box in the Error Checking section.

    3. 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.

  2. On the Error Checking Options button, click the down arrow.

    Notice that the Number Stored as Text menu appears.

  3. Click Convert to Number.

Notice that the cell is converted to the number format and the Error Indicator no longer appears in the cell.

404 image 404 image 404 image
How to convert letter to number or vice versa in Excel?

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?

Convert letter to number with formula

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 fifty-seven 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 30-day!

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Convert number to letter with formula

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 60-day, 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:

7 Easy Ways To Convert Text To Numbers In Excel

29-12-2017 · 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…

29-12-2017

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.

How To Convert Text To Number In Excel

To convert Excel text to number, try any of the below-given 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:

  • On Format menu in Excel > click
  • Then on Number tab > under Category > click General and > OK.

Now follow the given methods one by one:

Step 1: Retype Values in the Cells

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.

Step 2: Select “Edit Directly in Cell”

To do this, follow the given steps and check if this helps you to convert text to number in Excel.

  1. First in the Tools menu > click Options (in Windows) > or In Mac click Preferences on the Edit menu.
  2. Then on the Edit tab,> check that the Edit directly in cell check box is selected > and click OK.
  3. And double click the cell you want to format > press Enter.

Step 3: Use Paste Special with Multiply

Doing this will help your Excel convert text to numbers, so here follow the steps to do so:

  1. Enter the value 1, in any blank cell.
  2. After that select, the particular cell where you type 1 > on the Edit menu click Copy.
  3. And with the values select the cells that you need to convert to numbers.
  4. Next on Edit menu > click Paste Special.
  5. Then under Operation > click Multiply, and Under Paste > click Values > click OK.

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

Step 4: Eliminate Hidden Characters and Spaces

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:

  1. To the right of column, A insert a column by selecting column B > click Columns on Insert menu
  2. The in the first cell of the inserted column (B1), type the following
  3. After that, in column B > to the right of the cell choose all cells that contain data in column A
  4. Next, on the Edit menu,> point to Fill > then click Down.
    The new column contains values of text in column A
  5. Then with same range selected > on the Edit menu > click Copy
  6. Select cell A1 > click Paste Special on Edit menu. Then under Paste > click Values > click OK to paste the converted values back on top of column A.
  7. And by selecting the column to delete the column B > click Delete on Edit menu

You can see the text that was in column A is in number format now.

Step 5: Utilize Visual Basic for Applications Procedure

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 re-enter the numbers in the chosen cell or range of cells.

Follow the steps to do so:

  1. Choose cells that exhibit the behavior.
  2. If you haven’t done so, then on the Format menu click Cells  > click the Number tab to modify number format of the cells
  3. Next, on Tools menu,> point to Macro > click Visual Basic Editor
  4. And on the Insert menu > click Module.
  5. After that type following macro code in the new module:
  6. Next, on File menu,> click Close and Return to Microsoft Excel
  7. Reselect affected cells if not already selected.
  8. Next on Tools menu > point to Macro > click Macros. In the Macro name list > click Enter_Values > click Run.

Step 6: Utilize Text to Columns

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:

  1. Choose one column of cells that hold the text.
  2. And on the Data menu > click Text to Columns.
  3. After that under Original data type > click Delimited > click Next.
  4. Next under Delimiters > click to choose the Tab check box > click Next.
  5. And under Column data format > click General.
  6. Then click Advanced > make any appropriate settings for the Decimal separator > Thousands separator and click OK.
  7. Click Finish.

And that all your text is converted to numbers.

Step 7: Excel Convert Text To Number Using VBA

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 above-mentioned 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.

Conclusion:

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!!!

7 Easy Ways To Convert Text To Numbers In Excel
How to Convert Text to Number with Excel VBA (3 Examples ...

29-11-2021 · 3 useful and effective VBA codes to convert text to number in excel. Download the practice workbook and try these now for free.

29-11-2021

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 Practice Workbook

Download this practice workbook

Convert Text to Numbers Using VBA.xlsm

How to Identify Numbers Formatted as Text

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.

dataset for convert text to number using VBA in excel

Though we have numbers in the column, they all are left-aligned like the text. Now, click any cell. You will find this box beside the cell.

click any cell to check number or text in excel

Now, hover the mouse cursor over the box. After that, you will see this message.

the values in are in text form instead of number

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.

3 VBA Codes to Convert Text to Number in Excel

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.

1. VBA Code with Range.NumberFormat Method to Convert Text to Number in Excel

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.

insert module for VBA editor

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.

macro dialog box to convert text to number using vba in excel

6. Select ConvertTextToNumber and Click on Run.

convert text to number in excel using vba

In the end, this code will convert our text to numbers.

Read more: How to Convert String to Number in Excel VBA

2. VBA Code with Loop and CSng to Convert the Text to Number

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.

insert module for VBA editor

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.

vba macro dialog box to run vba code

6. Select ConvertUsingLoop and Click on Run.

text converted to number in excel

As you can see, we have converted our text to numbers using this VBA code.

Similar Readings

3. Convert Text to Numbers for Dynamic Ranges in Excel

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 non-empty 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.

insert module for VBA editor

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.

macro dialog box for vba codes

6. Then select ConvertDynamicRanges and Click on Run.

text converted to number in excel

As you can see, we are successful in converting text to numbers using the VBA codes.

💬 Things to Remember

✎ 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.

Conclusion

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 Excel-related problems and solutions.

Related Articles

Convert Text to Number - Excel & Google Sheets - …

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.

convert text number Main Function

VALUE Function

If you have a number stored as text in Excel, you can convert it to a value by using the VALUE function.

convert text number 01

DATEVALUE Function

If you have a date stored as text in Excel, you can get the date value from it by using the DATEVALUE function.

Date Value

This will only work however, if the date is stored as text.

Date Value 01

If the date is stored as an actual date, the DATEVALUE Function will return an error.

Date Value 02

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.

convert text number 02

TEXT to COLUMNS

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.

  1. Select the range to apply the formatting (ex. B3:B6)
  2. In the Ribbon, select Data > Text to Columns.

TEXT TO COLUMN 01

  1. Keep the option as Delimited, and click Next

TEXT TO COLUMN 02

  1. Select Comma as the Delimiter, and make sure that the Text qualifier is set to {none}.

convert text number 03

  1. Select cell C3 as the destination for the data. You do not need to change the Column data format as Excel will find the best match for the data.

convert text number 03

  1. Click Finish to view the data. If you click in cell D3, you will see that the number part of the text string has been converted to a number.

convert text number 05

Convert Text to Number in Google Sheets

All of the examples above work the same way in google sheets.

convert text number Google Function

quickexcel.com

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 ...

Excel Convert Text to Number with Keyboard Shortcuts

06-05-2013 · Excel has a built-in 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.

06-05-2013

Excel has a built-in 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.

Keyboard Shortcuts to Convert Text to Numbers

Here's a quick guide for the keyboard shortcuts to select the range of cells you want to convert.

  1. Select any cell that contains the Number Stored as Text error.  You will see a small green triangle in the top left corner of the cell.Number Stored as Text Error
    _
  2. Use the following keyboard shortcuts to select the range:
    _
    Ctrl A – selects the entire contiguous range
    Ctrl A Twice – selects the entire sheet
    Ctrl  Space bar – selects the entire column Shift Left/Right Arrow – selects multiple columns

    Shift  Space bar – selects the entire rowShift Up/Down Arrow – selects multiple rows


    _
  3. The error menu drop-down will now be at the top left or right corner of your selected range.  You can then use the following keyboard shortcut to Convert to Numbers.
    _
    Alt Menu Key C – Convert to Number

Excel Convert Text to Number Keyboard Shortcut Alt Menu Key C_

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.

Keyboard Diagram Menu Key Windows_

Alternative Methods

I find the built-in feature to be the fastest, but there are other ways to accomplish this task:

    • Copy a cell containing a 1 and Paste Special>Multiple on the range you want to convert.
    • Use Text to Columns tool on the Data tab (ribbon).  This is limited to one column and doesn't work on rows.
    • Functions – the VALUE() function works well, but there are many ways to do this too.
    • VBA/Macro – write your own code to automate the process

Do you have a different or easier way?  Please leave a comment.

wps.com

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 pop-up 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 pop-up 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.

The VALUE function Convert text to number in Excel1.gif  

· 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 drop-down button.

2. Click the drop-down button and click Convert to Number.

Tips: This method is suitable for situations where only small amounts of data needs to be processed.

The VALUE function Convert text to number in Excel2.gif 

For all, easy your work with WPS office suite. Free download WPS Office for PC, Mobile, Windows, Mac, Android, iOS online.

  • Didn't match my interface
  • Too technical or incomprehensible
  • Incorrect operation instructions
  • Incomplete instructions on this function
  • Other

Fields marked * are required please

3 Ways To Convert Text To Numbers In Excel

13-12-2016 · 3 Ways To Convert Text To Numbers In Excel: Convert To Number, Text To Columns, Paste Special Add Zero or Multiply By One

13-12-2016

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.

excel-text-vs-number-formatting

How Do I Know The Data Is Formatted As Text?

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.

excel-text-to-numbers-message

So how do I convert the text to numbers?

The First And Easiest Way To Convert Text To Numbers In Excel: Convert To Number

If you click on the little down arrow next to the yellow diamond, you’ll get a drop-down 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.

excel-text-to-numbers-button

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.

The Second Way To Convert Text To Numbers In Excel: Text To Columns

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.

excel-text-to-columns-data-tab

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.

excel-text-to-columns-step-1

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.

excel-text-to-columns-step-2

Below is the step three window. Just leave “General” selected, click Finish and you’re done.

excel-text-to-columns-step-3

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.

The Third Way To Convert Text To Numbers In Excel: Paste Special (add zero)

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, right-click, 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.

excel-text-to-numbers-paste-special

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, RC-S-S-V-D-Tab-Tab-Tab-Space (RC stands for Right-Click).

Conclusion

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.

5 Ways to Convert Text to Numbers in Microsoft Excel

29-03-2022 · 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.

29-03-2022

Microsoft Excel logo on a green background

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.

Change the Cell Format

A quick and easy way to convert text to a number is by changing the cell format on the Home tab. Use the drop-down box at the top of the Number section. Choose “Number” from the list.

Number selected for cell format

Alternatively, right-click 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.”

Number selected for cell format

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

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.

Warning for number stored as text

Click the drop-down arrow next to the warning and choose “Convert to Number.”

Convert to Number in the warning drop-down box

This removes the warning and formats the text as a number.

Select Text to Columns

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.

Text to Columns on the Data tab

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.

Finish button in the wizard

This changes the format to Excel’s default General format for numbers. You can leave the format as-is or select the cells and choose “Number” in the Number section on the Home tab as described earlier.

Use Paste Special

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.

Number one formatted as a number

Copy the cell by right-clicking and selecting “Copy” or clicking the Copy button in the Clipboard section of the Home tab.

Copy button on the Home tab

Select all of the cells that you need to convert to numbers from text.

Either right-click and choose Paste Special > Paste Special or go to the Home tab and choose “Paste Special” in the Paste drop-down box.

Paste Special in the shortcut menu

Leave Paste set to “All” at the top. Under Operation at the bottom pick “Multiply”. Click “OK.”

Multiply in the Paste Special operations

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.

Converted text to numbers with Paste Special

Insert the VALUE Function

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, right-click 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)

VALUE function with a range

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)

VALUE function and dragging the formula

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!

exceldome.com

To convert text to number you can apply the Excel VALUE function, using both Excel and VBA methods.

4 quick and easy ways to convert numbers stored as text to ...

10-03-2022 · 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 …

10-03-2022
A blank Excel spreadsheet
Image: PixieMe/Shutterstock

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 64-bit 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.

1. Use SmartTag

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

These numbers are stored as text.
These numbers are stored as text.

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 top-left corner of the cell—and do the following:

  1. Click the resulting SmartTag (if the SmartTag is already visible, click it without clicking the triangle). Doing so displays a list of possible problems, as you can see in Figure B. These SmartTags are indeed “smart.” The first item tells you that the number is stored as text—that’s the problem. The next item is the solution: Convert to Number.
  2. Select the second item, Convert to Number, to instantly convert all selected values to real numbers, and in place, as shown in Figure C.

Figure B

The SmartTag displays several possible problems.
The SmartTag displays several possible problems.

Figure C

The SmartTag converts all the text values to real numbers instantly.
The SmartTag converts all the text values to real numbers instantly.

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 one-size-fits-all-situations solution.

2. Text to Columns

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:

  1. Click the Data tab and then click Text to Columns in the Data Tools group. Doing so launches a wizard.
  2. In the first pane, click the Fixed Width option—there are no delimiters.
  3. Click Finish—it’s that quick.

Unfortunately, the values are still stored as text. With the values selected do the following:

  1. Click the Home tab.
  2. From the Number Format dropdown (in the Number group), choose Number, as shown in Figure D. This one tacks on a few decimal places, which you might not want.
  3. If you don’t want those decimal places, choose More Number Formats from the dropdown.
  4. Choose Number in the Category list.
  5. Enter 0 in the Decimal Places option.
  6. Click OK to format the selected values.

Figure D

Choose the Number format.
Choose the Number format.

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.

3. An expression

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

The numbers are still stored as text.
The numbers are still stored as text.

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:

  1. Click the Home tab.
  2. From the Number Format dropdown (in the Number group), choose Number. This one tacks on a few decimal places, which you might not want.
  3. If you don’t want those decimal places, choose More Number Formats from the dropdown.
  4. Choose Number in the Category list.
  5. Enter 0 in the Decimal Places option.
  6. Click OK to format the selected values.

If you’re thinking that there ought to be a function that will do both, there is.

4. Value()

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

VALUE() removes the leading zeros and converts the values to General.
VALUE() removes the leading zeros and converts the values to General.

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.

simonsezit.com

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.

exceljetconsult.com.ng

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

excelfunctions.net

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 » Basic-Excel » Convert-Text-To-Number

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.

Value Function Examples

Example 1

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:

 AB
1111=VALUE( A1 )
2112=VALUE( A2 )
3113=VALUE( A3 )
4114=VALUE( A4 )
5115=VALUE( A5 )

 Results:

 AB
1111111
2112112
3113113
4114114
5115115

Example 2

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:

 AB
131 days this month=VALUE( LEFT( A1,2 ) )

 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  >>

How to convert number to text in Excel

09-10-2014 · 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.

09-10-2014

Maria Azbel

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.

  • Search by part not by the entire number. For example, you may need to find all numbers that contain 50, like in 501, 1500, 1950, etc.)
  • It may be necessary to match two cells using the VLOOKUP or MATCH function. However, if these cells are formatted differently, Excel will not see identical values as matching. For instance, A1 is formatted as text and B1 is number with format 0. The leading zero in B2 is a custom format. When matching these 2 cells Excel will ignore the leading 0 and will not show the two cells as identical. That's why their format should be unified.
    Matching 2 identical cells with different formats Excel ignores

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 formula-oriented, 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.

convert-number-to-text-excel-TEXT-function

Convert number to text using the Excel TEXT function

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.

  1. Add a helper column next to the column with the numbers to format. In my example, it's column D.
  2. Enter the formula =TEXT(C2,"0") to the cell D2. In the formula, C2 is the address of the first cell with the numbers to convert.
  3. Copy the formula across the column using the fill handle.
    Copy the formula across the column using the fill handle
  4. You will see the alignment change to left in the helper column after applying the formula.
    See the alignment change to left in the helper column
  5. Now you need to convert formulas to values in the helper column. Start with selecting the column.
  6. Use Ctrl C to copy. Then press the Ctrl Alt V shortcut to display the Paste Special dialog box.
  7. On the Paste Special dialog, select the Values radio button in the Paste group.
    Select the Values radio button on the Paste Special dialog box

    You will see a tiny triangle appear in the top-left corner of each cell in your helper column, which means the entries are now text versions of the numbers in your main column.
    You will see a tiny triangle added at the top of each cell in your helper 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.

    Note. The second parameter in the Excel TEXT function shows how the number will be formatted before being converted. You may need to adjust this based on your numbers:

    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").

    Tip. Say you need to format a cash amount, but the format isn't available. For instance, you cannot display a number as British Pounds (£) as you use the built-in formatting in the English U.S. version of Excel. The TEXT function will help you convert this number to Pounds if you enter it like this: =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!

Use the Format Cells option to convert number to text in Excel

If you need to quickly change the number to string, do it with the Format Cells… option.

  1. Select the range with the numeric values you want to format as text.
  2. Right click on them and pick the Format Cells… option from the menu list.
    Pick the Format Cells… option from the menu list
    Tip. You can display the Format Cells… window by pressing the Ctrl 1 shortcut.
  3. On the Format Cells window select Text under the Number tab and click OK.
    Select the Text option and click OK

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.

Add an apostrophe to change number to text format

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 double-click in a cell and enter the apostrophe before the numeric value.
Add an apostrophe to convert number to test

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.

Convert numbers to text in Excel with Text to Columns wizard

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.

  1. Select the column where you want to convert numbers to string in Excel.
  2. Navigate to the Data tab in and click on the Text to Columns icon.
    Navigate to the Data tab in Excel and click on the Text to Columns icon
  3. Just click through steps 1 and 2. On the third step of the wizard, make sure you select the Text radio button.
    Navigate to the Data tab in Excel and click on the Text to Columns icon
  4. Press Finish to see your numbers immediately turn into text.

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!

You may also be interested in

sheetzoom.com

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

Excel TEXT Function - Formula, Examples, How to Use Text ...

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.

Formula

=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

When is the Excel TEXT Function required?

We use the TEXT function in the following circumstances:

  1. When we want to display dates in a specified format
  2. When we wish to display numbers in a specified format or in a more legible way
  3. When we wish to combine numbers with text or characters

Examples

1. Basic example – Excel Text Function

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:

Text Function in Excel

2. Using Excel TEXT with other functions

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”)

Excel TEXT Function - Other Functions

The other way to do it by using the CONCATENATE function as shown below:

Excel TEXT Function - CONCATENATE

3. Combining the text given with data using TEXT function

When I use the date formula, I would get the result below:

TEXT Function - DATE

Now, if we try to combine today’s date using CONCATENATE, Excel would give a weird result as shown below:

TEXT Function - CONCATENATE Error

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:

TEXT Function - Fixed

4. Adding zeros before numbers with variable lengths

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 9-digit product code, but Excel removed the zeros before it. We can use TEXT as shown below and convert the product code into a 9-digit number:

TEXT Function - Variable Number Lengths

In the above formula, we are given the format code containing 9-digit zeros, where the number of zeros is equal to the number of digits we wish to display.

5. Converting telephone numbers to a specific format

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.

TEXT Function - Telephone Numbers

To learn more, launch our free Excel crash course now!

Format Code

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:

CodeDescriptionExample
# (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.

If the Excel TEXT function isn’t working

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, mm-dd-yy). It would give an error because the formula is incorrect and should be written this way: =TEXT(A2,”mm-dd-yy”).

Tips

  1. The data converted into text cannot be used for calculations. If needed, we should keep the original data in a hidden format and use it for other formulas.
  2. The characters that can be included in the format code are:

Plus sign
-Minus sign
()Parenthesis
:Colon
{}Curly brackets
=Equal sign
~Tilde
/Forward slash
!Exclamation mark
<>Less than and greater than
  1. TEXT function is language-specific. It requires the use of region-specific date and time format codes.

Free Excel Course

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.

free Excel keyboard shortcuts course

Additional Resources

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:

  • Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel.
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Download our free Excel ebook!
  • Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts
Convert Text To Number Excel

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…
006
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.

007


VALUE function Syntax

=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.
008
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

009
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.


0010
So yeah, these are the ways to convert string to text in Excel. Now you know how easy converting text to numbers in excel is.
How to Convert Text to Number in Excel

15-05-2019 · Another way to convert text to numbers is to use Copy, Paste Special Excel option: Select the numbers stored as text with errors, right-mouse click and choose Copy. Figure 4. Copy numbers stored as text. Right-mouse click again and choose Paste Special. In Paste Special choose Values and Add and press OK. Figure 5.

15-05-2019

To convert text to number we can use error message that appears in the upper left corner of the cell, simple Copy-Paste 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.

Convert Text to Number Using Error Message

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:

  • When we select the numbers stored as text, the Status bar will display count of the cells. However, for numbers it will appear as sum and average of the cells
  • The Number stored as the text has left alignment in the cell while the number has the right
  • Numbers stored as text have the leading apostrophe in the formula bar

Figure 2. convert number to text

To convert string to number, follow the steps:

  • Select the numbers formatted as text
  • Click on the error message and choose Convert to Number
  • Excel converts text to numbers in all selected cells

Figure 3. Convert text to number

Change Text to Number with Copy and Paste Special

Another way to convert text to numbers is to use Copy, Paste Special Excel option:

  • Select the numbers stored as text with errors, right-mouse click and choose Copy

Figure 4. Copy numbers stored as text

  • Right-mouse click again and choose Paste Special
  • In Paste Special choose Values and Add and press OK

Figure 5. Change text to number

  • As a result, Excel converts text to numbers

Figure 6. change the text to number with Copy, Paste Special

Convert String to Number with VALUE Function

Another way to change the text to a number is to use VALUE function:

  • Select the cell C3 and insert the formula =VALUE(B3)
  • Drag the formula down to the other cells in the column by clicking and dragging the little “ ” icon at the bottom-right of the cell.

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.

Instant Connection to an Excel Expert

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.

How to Convert a Month Name to a Number in Excel

11-04-2022 · 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.

11-04-2022

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.

The Wrong Data Type

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. 

Published date showing month and year.

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.

Using Excel MONTH Function

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 month-name-conversion.xlsx. You can find it at the bottom of the page.

  1. In Excel, click the cell where you want your results.
  2. From the Formulas tab, click Insert Function.
  3. On the Insert Function dialog, select the Date & Time Category from the drop-down list.
  4. In the Select a function list box, select MONTH.
  5. Click OK.
  6. In the Function Arguments dialog, enter your cell reference followed by &1 in the Serial_number textbox.

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.

.

Convert Month to Number Didn’t Work

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.

Excel Practice Worksheet

Download Excel Practice File

Hand-Picked Tutorials

How to convert text to number in Excel

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.

How to find numbers stored as text?

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 -

  1. Sometimes, you see a green color small triangle at the left corner of the cell. This is because of when the number is converted to the text format by placing an apostrophe before the number.
    How to convert text to number in Excel?
  2. Sometimes, you don't see a green color triangle at the left corner of the cell and still numbers are in text format. You will notice that if the number is in text format, they are aligned on the right side of the cell.
    How to convert text to number in Excel?
  3. When the number is stored as text, they are default aligned to the right in the cell. Whereas, generally the number is aligned to the left in the cell.

Difference between number and text format

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.

Methods to convert text to number

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 -

  1. Using Convert to Number method
  2. Using Paste Special
  3. Using Text to Column
  4. Change the format to text to General/Number

Convert text to number using Convert to Number method

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.

How to convert text to number in Excel?

Step 2: Click the yellow diamond icon that appears near the selected cells (at the top right).

How to convert text to number in Excel?

Step 3: Now, select and click the Convert to Number option.

How to convert text to number in Excel?

Step 4: It will immediately convert the number stored in text format, to the number format.

How to convert text to number in Excel?

Convert text to number using Paste Special

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.

How to convert text to number in Excel?

Step 2: Set the format to General inside number group.

How to convert text to number in Excel?

Step 3: Now, copy a blank cell using the Ctrl C shortcut copy key.

How to convert text to number in Excel?

Step 4: One more time, select the text formatted cells for conversion and right-click on the selected cell then choose the Paste Special.

How to convert text to number in Excel?

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.

How to convert text to number in Excel?

Step 6: If you have performed all steps correctly, your data is converted from text to number.

How to convert text to number in Excel?

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.

Convert text to number using text to column

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 formula-free 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.

How to convert text to number in Excel?

Step 2: Go to the Data tab in the Excel ribbon and click Text to Column button inside the Data Tools group.

How to convert text to number in Excel?

Step 3: A wizard will open where select Delimited radio button and click Next.

How to convert text to number in Excel?

Step 4: On the next wizard, keep the options default and click Next.

How to convert text to number in Excel?

Step 5: Select the column data format to General and click the Finish button to end the process.

How to convert text to number in Excel?

Step 6: Selected number stored as text is now converted to number format.

How to convert text to number in Excel?

Convert text to number using by changing cell 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.

How to convert text to number in Excel?

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.

How to convert text to number in Excel?

Note: Do not select column header.

Step 2: Inside the Home tab, you will see that the selected data is stored as text in the Number section.

How to convert text to number in Excel?

Step 3: Here, change the format from Text to General or Number.

How to convert text to number in Excel?

Step 4: Text alignment will automatically change from right to left when the number is changed to the general or number format.

How to convert text to number in Excel?

You can now perform any arithmetic or numeric operation on it.

Next TopicHow to remove dropdown in Excel

How to change or convert number to text 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

Convert number to text with Text function

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").

doc convert number to text 1

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:

doc convert number to text 2

Note: As the data is formula, you can change the formulas to values. Copy the cells and right-click 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:

doc convert number to text 3

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:

doc convert number to text 4

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 "00-00000", 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 seventy-five cents".

Convert number to text with Format Cells command

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:

doc convert number to text 5

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:

doc convert number to text 6

4. Then numbers in selected range are converted into text.

Convert number to text with Kutools for Excel

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:

doc convert number to text 8

Click to Download Kutools for Excel and free trial Now!

  Demo

Related article:

How to change or convert text to number in Excel?

3 easy ways to convert text to numbers

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

 

  1. Using Excel’s built-in help.

(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. 

 

officepowerups.com

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 …

exceltable.com

The «Text-number» 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.

before the number.

There are several methods of converting text to a number. So let`s consider the most simple and convenient methods.

  1. Use the button «Error» in the menu. When you highlight any cell with an error on the left, the corresponding icon appears. That`s what it is the button «Error». If you hover over it, the drop-down menu will appear (the black triangle). You need to select the column with numbers in text format. We open the menu of the button «Error» and click «Convert to Number».
  2. Convert to Number.
  3. You can apply any mathematical actions now. The simplest operations, that do not change the result (multiplication / division by one, adding / subtracting zero, raising to the first power, etc.) will do.
  4. simplest operations.
  5. You need to add to the special insert. You can also put into practice the simple arithmetic operation, but you do not need to create an auxiliary column here, but you do not need to create an auxiliary column. In the separate cell, to write the number 1 and to copy the cell in the clipboard (using the «Copy» button or the keyboard shortcut Ctrl + C). To highlight to the column with editable numbers. In the context menu of the «Paste» button, you need to click «Paste Special» and in the window that has opened to check the box next to «Multiply». After clicking OK, the text format is converted to a numeric format.
  6. Paste Special.
  7. Removing of non-printable symbols. Sometimes the numeric format is not recognized by the program because of invisible characters. We delete its using the formula that we enter into the auxiliary column. The CLEAN function removes to the unprintable characters, TRIM – are extra spaces, and the VALUE function converts a text format to a numeric one.
  8. CLEAN function.
  9. Using the «Text by Columns» tool, you need to select the column with the text arguments, that you want to convert in numbers. On the «DATA» tab we find the «Data Tools»-«Text by Columns» button, and after the « Convert Text to Columns Wizard» window opening, you need to click «Next». In the step-3, we pay attention to the column data format.
step-3.

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:

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:

DEVELOPER.

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
Macros.

The symbols moved to the right.

symbols to 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.

domiciliotrieste.it

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. …

iditect.com

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 ...

exceljet.net

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.

Excel Shortcut - Convert Text to Numbers - Excel Tips ...

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 Shortcut - Convert Text to Numbers

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.

Watch Video

Video Transcript

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

Convert Text to Numbers in Microsoft Excel

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.

img1

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:

  • Enter apostrophe ( ‘ ) in front of the number in cell A2
  • In cell B2, we have shown 1 by taking a cell reference

img2

To convert the text into number, we will use the Value Function.

img3

  • We can also make out the cell that contains text rather than number
  • When we click on cell A2, we will see

img4

  • Click on the pointer that pops up

img5

img6

  • 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
  • Select Values & Add button

img7

img8

VBA Convert Text String to Number

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)

Convert String to Integer

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:
Using the CInt Function in VBA

The following code uses the CLng function to convert a string to an integer:

The result is:

Using the CLng Function in VBA

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"

Using the Immediate Window
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)

Convert String to Decimal

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:

Converting a String to a Double Decimal Type

The following code would convert a string to a decimal data type:

MsgBox CDec("13.57") CDec("13.4")

The result is:

Converting a string to a decimal data type in VBA

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:

Using Debug.Print to Display values

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.

Convert String to Currency

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:

Converting a String to the Currency Data Type

vba-free-addin

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 add-in.

(No installation required!)

Free Download

Return to VBA Code Examples

Convert Text to numbers in excel for Mac?

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 paste-special-value 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 file-sharing 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 file-sharing 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 remaining

Was 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.

stackoverflow.com

01-12-2014 · 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...

01-12-2014Array
Excel Convert Number to Text with Leading Zeros: 10 ...

28-06-2021 · In this article, I am going to discuss the 10 effective ways of converting numbers to text with leading zeros including real life examples

28-06-2021

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.

Introduction of

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.

Download the Practice Workbook

 Convert Number to Text with Leading Zeros

Excel Convert Number 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.

i. Using Text Format

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:

  1. Select a blank cell or cell range where you want to use the format
  2. Click on the Format Dropdown selection in the Numbers command from the Home tab
  3. Chose the Text option from the lists

Using Text Format for Converting Number to Text with Leading Zeros

Now, the cells are ready to show leading zeros. After inputting the postcode manually, we will get the following output.

Using Text Format for Converting Number to Text with Leading Zeros

ii. Custom Format using Format Cells

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:

  1. Copy the ZIP codes to a new cell range and select the cell range
  2. Click on the Number command
  3. Choose the Custom option from Format Cells 
  4. Type ‘00000’ as the zip code consists of five digits.
  5. Press Ok

Custom Format using Format Cells for Converting Number to Text with Leading Zeros

Thereafter, you will get the following

Custom Format using Format Cells for Converting Number to Text with Leading Zeros

iii. Adding Apostrophe

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.

Adding Apostrophe for Converting Number to Text with Leading Zeros

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.

iv. TEXT Function

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:

  1. Select the blank cell e.g. D5
  2. Type the formula like =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.
  3. Press Enter

TEXT Function for Converting Number to Text with Leading Zeros

v. RIGHT Function

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:

  1. Select the blank cell e.g. D5
  2. Type the formula like =RIGHT("00000"&C5,5) where C5 is the value of a ZIP Code, “00000” is the desired formatting and 5 is the number of characters
  3. Press Enter

RIGHT Function for Converting Number to Text with Leading Zeros

vi. BASE Function

The 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:

  1. Select the blank cell e.g. D5
  2. Type the formula like =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.
  3. Press Enter

BASE Function for Converting Number to Text with Leading Zeros

vii. CONCATENATE function

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:

  1. Select the blank cell e.g. D5
  2. Type the formula like =CONCATENATE("00", C5) where “00” is the first item & C5 is the second item, mainly the number of a ZIP code.
  3. Press Enter

CONCATENATE function for Converting Number to Text with Leading Zeros

Note: Using CONCATENATE function you can add leading zero but you can not pad leading zeros.

viii. REPT and LEN function

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:

  1. Select a blank cell e.g. E5
  2. Type the formula like =REPT(0,5) where 0 is the item to repeat and 5 is the times to repeat
  3. Press Ok

REPT and LEN function for Converting Number to Text with Leading Zeros

  1. You see that all values are zeros. So that we have to merge the Column C and Column D. For this, select the cell E5 or a new blank cell in a new worksheet.
  2. Type the formula like =REPT(0,5)&C5
  3. Press Ok 

REPT and LEN function for Converting Number to Text with Leading Zeros

  1. Again you see the outputs are not padded (added leading zeros) that means all the outputs are not 5 digit text with leading zeros. That’s why we need to use the LEN function. For this,select the previous cell E5 or a blank cell
  2. Type the formula like =REPT(0,5-LEN(C5))&C5 where C5 is the number of ZIP codes.
  3. Press Ok

REPT and LEN function for Converting Number to Text with Leading Zeros

ix. Text.PadStart Function

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:

  1. Select the cell range B4:B13
  2. Move into Data tab
  3. Choose the option From Table
  4. Check the selected table
  5. Press Ok

Text.PadStart Function for Converting Number to Text with Leading Zeros

Now we have to convert the ZIP code (number) into the text as the formula says.

  1. Pick the cursor to the upper left corner
  2. Select the Text option for transforming the number into the text

Text.PadStart Function for Converting Number to Text with Leading Zeros

At this moment, we have to use the function. Thus, we need to add a new column.

  1. Click on the Add Column tab
  2. Select Custom Column command
  3. Write the new name of the column-like Text with Leading Zeros (Padded)
  4. Type the formula like =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 pad
  5. Press Ok

Text.PadStart Function for Converting Number to Text with Leading Zeros

  1. Click on the Close & Load command
  2. Pick the Close & Load To option

Text.PadStart Function for Converting Number to Text with Leading Zeros

  1. Choose the loading option as Table
  2. Select the cell C4 of Existing Working Sheet 
  3. Press Load

Text.PadStart Function for Converting Number to Text with Leading Zeros

The final output you’ll get like the following:

Text.PadStart Function for Converting Number to Text with Leading Zeros

x. Power Pivot

Power Pivot is an Excel add-in 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 add-in Power Pivot. You can do this task by simply search Power Pivot in Excel and choose the option Enable Add-In. Please visit the link Start the Power Pivot add-in for Excel – Excel (microsoft.com) for a detailed guide.

  1. Select the cell range that you want to convert
  2. Click on Add to Data Model command from the Power Pivot tab

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select a blank cell and type the formula like =FORMAT([ZIP Code (Number)], “00000”) where ZIP Code (Number) is the text to convert and “00000” is the formatting to apply

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select the PivotTable option

Power Pivot to Convert Number to Text with Leading Zeros

  1. Pick Existing Worksheet 
  2. Fix the Location
  3. Select the cell C4
  4. Press Ok

Power Pivot to Convert Number to Text with Leading Zeros

  1. Select the Text with Leading Zeros (Padded)
  2. Move the Text with Leading Zeros (Padded) into Rows
  3. The output is at Row Labels

Power Pivot to Convert Number to Text with Leading Zeros

  1. Click the cursor on the Design tab
  2. Select the Report Layout
  3. Choose the Show in Tabular Form option

Power Pivot to Convert Number to Text with Leading Zeros

The final output of conversion of number to text is like the following-

Power Pivot to Convert Number to Text with Leading Zeros

Things To Keep in Mind

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.

Conclusion

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.

Further Readings