Convert text to number in Excel

The numbers stored as text format cannot be calculated normally in Excel, in this case, you should convert the text to general number. This article, I will talk about some functions for dealing with this job in Excel.


Convert text to number with VALUE function

The VALUE function is a common-used function to convert text to number, the generic syntax is:

=VALUE (text)
  • text: the text or cell containing the text to be converted to a number.

1. Enter or copy the following formula into a blank cell:

=VALUE(A2)

2. And then, copy and drag this formula to other cells that you want to apply this formula, now, you can see, all the numbers stored as text format have been converted to real numbers, see screenshot:


Convert text to number normal formula

Here is a simple formula also can help you to convert the text to numbers, please apply the below formula:

=A2+0


Convert text to number with Right Left or MID functions

If there are some other characters with the numbers in the cell, you should remove the characters before converting to numbers. In this case, the RIGHT, LEFT, or MID functions can help you to remove all non-numeric characters from a text value. The generic syntaxes are:

=LEFT (text, [num_chars])
=RIGHT(text, [num_chars])
=MID(text, start_num,num_chars)
  • text: the text string that you want to extract characters from right, left or middle.
  • num_chars: the number of characters to extract from right, left or middle.
  • start_num: the location of the first character that you want to extract.

Please apply the below formulas into the cells based on your need:

=RIGHT(A2,4)+0
=LEFT(A5,3)+0
=MID(A7,5,4)+0

Note: At the end of the formula, +0 is forced to convert the returned number text to real number.


Relative functions used:

  • VALUE:
  • The VALUE function helps to convert a text string that represents a number (such as number, date or time format) into a number.
  • RIGHT:
  • The RIGHT function extracts a specific number of characters from the right side of the text string
  • LEFT:
  • The LEFT function extracts the given number of characters from the left side of a supplied string.
  • MID:
  • To return the specific characters from the middle of text string.

More articles:

  • Convert numbers to text in Excel
  • Maybe, there are several methods for converting the numbers to text format in Excel worksheet, but, in this article, I will introduce some functions for deal with this task.
  • Extract multiple lines from a cell
  • If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and now, you want to extract these lines of text into multiple cells as below screenshot shown. How could you solve it with a formula in Excel?
  • Clean And Reformat Telephone Numbers In Excel
  • Supposing, you have a list of telephone numbers which are formatted with various formatting, now, you want to clean up all the phone number formatting and reformat them with a specific formatting as below screenshot shown. This article, I will talk about how to deal with this job in Excel by using a formula.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.