Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Convert Text to Numbers in Excel? 5 Easy Methods

How to Convert Text to Numbers in Excel? 5 Easy Methods

(Note: This guide on how to convert text to numbers in Excel is suitable for all Excel versions including Office 365.)

While using Excel, you will be dealing with lots of data in text and number formats. Sometimes texts and numbers may get accidentally intertwined, making it hard for Excel to differentiate the numbers from the text.

Excel mistaking a text for a number happens because of some functions like STRING, which converts numbers into text. There is also the possibility that when numbers are copied or downloaded from databases, some numbers might be formatted as texts.

This causes some errors and issues when using functions like SUM and ROUND since they only work on numbers. Excel throws an error in case of such exceptions. To avoid this, you need to convert the numbers formatted as texts back to numbers. Are you wondering how to do that? Don’t worry, you have come to the right place.

In this guide, you’ll learn:

  • How Are Numbers Indicated as Text in Excel?
  • How to Convert Text to Numbers in Excel?
    • Using the Dropdown
    • Using Text to Column
    • Using Paste Special
    • Using Value Function
    • Using Arithmetic Operations

Watch our video on how to convert text to numbers in Excel

Related:

How to Insert Bullet Points in Excel? 5 Easy Methods

How to Insert a Page Break in Excel? (3 Simple Steps)

How to Combine Two Columns in Excel? 2 Easy Methods

How Are Numbers Indicated as Text in Excel?

In Excel, when numbers are formatted as texts, the cells have a small green flag at the top left of the cell.

Numbers indicated as text
Numbers indicated as text

If you click on the cell, they are shown with an exclamatory indicator along with a message saying “this number is formatted as a text or preceded by an apostrophe”.

Notification indicating number as text
A notification indicating number as text

Note: Numbers that are left-aligned to the cell are text.

Also Read:

The FORMULATEXT Excel Function – 2 Best Examples

3 Best Methods to Find Duplicates in Excel

How to Autofit Excel Cells? 3 Best Methods

How to Convert Text to Numbers in Excel?

In this guide, I will show you 5 simple and easy ways to convert text to numbers in Excel.

1. Using the Dropdown

When a number bears an apostrophe before it, it is considered a text. It can be seen with a green flag towards the left top of the cell along with an exclamation mark notification. See if the exclamatory notification is available to the right of the cell.

You can easily convert a text to a number by clicking on the dropdown to the right of the cell.

Click on the dropdown and select Convert to Number.

how to convert text to numbers in excel using dropdown
Convert text to number using the dropdown

This converts the text to a number.

Note: You can see the formatted number to the right of the cell.

2. Using Text to Column

This helps you convert multiple cells with numbers as texts. 

Select the column which contains the numbers as text.

Click on Data > and select Text to Column.

Convert text to number using Text to Columns option
Convert text to number using Text to Columns option

Click on Finish.

Setup Text to Columns Wizard
Setup Text to Columns Wizard

This converts your text to numbers.

Note: Excel has default values necessary to convert text to numbers. So, you can just click Finish. The text to columns setup is commonly used to split columns. To split columns, select the values based on your preferences, click Next and Finish.

You can change the format of the numbers by clicking on Home > Number. From the dropdown menu, select the preference for your numbers format.

Change formatting of numbers in Excel
Change formatting of numbers in Excel

3. Using Paste Special

This is an easy method to convert the text to numbers. 

Enter “1” in any of the cells.  

Right-click the cell and click Copy. Or,  you can use the keyboard shortcut by pressing Ctrl + C.

Now, select the cell. Right-click on the cell and select Paste Special.

Convert text to number using Paste Special option
Convert text to number using Paste Special option

This opens up a dialog box. 

In the Paste Special window, select All and select Multiply. Click OK.

Since we are multiplying the text by 1, the text gets converted to a number and returns the same value. 

Setup Paste Special dialog box
Setup Paste Special dialog box

This formats the text as a number.

Note: You can also copy the number “0”. In the dialog box, under Operation, click on Add. Click OK.

4. 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. To use the function, you have to specify the cell position inside the parenthesis. To convert the text in cell A2 to a number, we use the formula =VALUE(A2) in cell B2.

Convert text to number using Value function
Convert text to number using Value function

This converts the number into a new cell. 

You can use the drag handle to apply the function to other cells.

Now once they are converted. You can copy the formatted numbers and paste them to their dedicated places by clicking on Paste Special and selecting Values.

Note: When you download any data from databases or while copying huge data from one source to another, there might be some unknown values showing up. To avoid this, you can also use the Value, Clean and Trim functions to properly convert the data.

Convert text to number using value, trim and clean function
Convert text to number using value, trim and clean function

5. Using Arithmetic Operations

By using mathematical operations on a text, you can convert text to numbers.

Operate with “0” while adding, and operate with “1” while multiplying or dividing. 

To convert a text to a number using arithmetic operations. Enter the arithmetic operation in the destination cell.

For example, to convert the text in cell A3, multiplying any value by 1 gives the same value. This way, the values get converted from text to numbers.

Convert text to number using arithmetic operations
Convert text to number using arithmetic operations

Note: To replace the values in the original place, right-click and Copy the number. Right-click the destination cell. Under Paste Special, click on Paste Values. 

Suggested Reads:

How to Format Numbers in a Microsoft Excel 2016 Spreadsheet

Combine numbers and words in Excel 2013 using the VLOOKUP Function

How to Filter in Excel? A Step-by-Step Guide

Closing Thoughts

Knowing how to convert text to numbers in Excel is essential for using some functions in Excel. This will help you avoid unnecessary confusion and errors while handling huge data. In this article, we saw 5 easy ways to convert text to numbers. You can use any one of these methods to quickly convert text to numbers and save time.  

For more high-quality guides on Excel, kindly visit our resources section. 

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Use Cell Styles in Excel: A Step-by-Step Guide

How to Insert Hyperlinks in Visio 2010

How to Enter Data into a Microsoft Excel 2016 Spreadsheet

3 Rules for Professional Slides in PowerPoint 2013

How to Calculate Factorial in Excel? Along with 2 Easy Examples

Get the Most Out of Microsoft Teams (1 Hour Recorded Webinar)

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)