Converts Text to Numbers in Excel (8 Different Ways)

Introduction

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn about 8 different methods to convert numbers stored as text to actual numbers in Excel. Let’s get started.

Sample Data

We have a size-able sample data in the caption below. In column B, there is Sales Amount stored as text. Essentially, we want to use 8 different ways to convert the text number to actual numbers in Excel.

Flash Fill

Flash Fill is a power tool that allows users  to automatically fill in values in cells based on the first example provided in the starting cell

  • In cell C2, type in 5403 (which is value in cell B2 to the left)
  • In the Editing group of the Home tab, click on Fill and select Flash Fill from the drop-down or
  • Deploy CTRL + E (after tying 5403 and pressed Enter on the keyboard)

In the caption below, we have actual numbers in column C.

Smart Tags

Smart tags are tags located left of the cell formatted as text. 

  • Select range B2:B20 and click on the drop-down in cell A1
  • Select Convert to number as seen in the caption below

In the caption below, we have actual numbers in column B

VALUE Function

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

Paste Special Empty Cell

  • Copy any empty cell using CTRL + C
  • Select cells B2:B20
  • In the Clipboard group of the Home tab, click on Paste drop-down

  • Click on Paste Special.
  • In the Paste Special dialogue box, Under Operation, check Add 
  • Click OK

That converts the texts to numbers as seen in the caption below

Paste Special using Divide Operation

  • Type in 1 in cell C2
  • Copy cell C2 using CTRL + C
  • Select cells B2:B20
  • In the Clipboard group of the Home tab, click on Paste drop-down
  • Click on Paste Special.
  • In the Paste Special dialogue box, Under Operation, check Divide, Multiply or Subtract 

  • Click OK

That converts the texts to numbers as seen in the caption below

Text To Columns

Text to columns split a single column of text into multiple columns

  • Select B2:B20
  • In the Data Tools group of the Data tab, click on Text to Columns

  • Click on Finish.

The text are converted to actual numbers so easily. See below

NotePad

  • Open an empty NotePad
  • Copy cells B2:B20 using CTRL + C
  • Press ALT + Tab to return to Notepad
  • CTRL + V to Paste

  • CTRL + A to select and CTRL + C to copy all the data inside the notepad
  • Press ALT + Tab to switch to Excel
  • CTRL + V to Paste. 

All the text are now showing as actual numbers

Power Query

  • Click inside the Dataset and execute CTRL + T to format as an Excel table
  • In the Create Table dialogue box, click OK
  • In the Get & Transform group of the Data tab, click on From Table/Range

This will open the power query editor as seen below

Power query automatically converted the numbers stored as text to number (Whole Number data type)

  • Optionally, we selected Date Data type
  • Click on Close & Load To
  • In the Import Data dialogue box, Table is fine. Select Existing worksheet
  • Select cell D1

  • Click OK.

In the caption below, we have the numbers

Conclusion

We have seen eight different ways in which we can convert text to numbers in Excel

Posted in Blog and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *