Skip to main content

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description

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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations