Concerned about the latest vaccine mandate? Visit Pryor's free training resource page to learn more about the latest rulings on the national 100+ vaccine mandate.

Spotlight Excel Formula: Convert Text to Numbers

Numbers stored as text can present problems for lookup formulas and regular calculations. There are a few ways to convert Text to Numbers in Excel using a formula. We’ll look at three separate scenarios and see how we can use a formula to make things work the way they should.

Download ConvertTexttoNumbers.xlsx and try out some of these solutions.

Using Value in a VLookUp

In the example below, our formula should have returned Blue, but instead it gave us an #N/A.

Even though the entry in D1 appears right justified, it is a text value (numbers preceded by an apostrophe). The entries in A2 through A7 are actual numbers (values). Rather than manually fix the alphanumeric lookup values, we can simply enclose it in the Value function, which converts a number stored as text to value data.

Combo Cell with Left, Right or Mid

We can apply a similar solution when using Left, Right and Mid to extract data from a combined cell. For example, here we have used these functions to extract numbers in specific positions from the string, 32-ABCD1234V99. We did get the right numbers. But, since Left, Right and Mid are text functions, they return a text value.

If we simply wrap these formulas in the value function, we can get it to return actual values. Notice that the results were automatically right justified.

Simple Math: This multiplied by 1

If you have to convert a whole column of numbers stored as text to values, create a formula that multiples the cell contents by 1. Then, just copy it down. In some cases, you may need to perform a copy/paste values to actually change the value in the cell from a formula. Check out the Multiply by 1 worksheet in the sample workbook.

Finishing Touches

You may still need to format your results in the number format that makes the most sense. These methods may not return the right format. On the positive side, since it is possible that you will have mixed cells, some formatted as text and some formatted as numbers, none of the methods above will “break” a cell already formatted as a value.

Check out ConvertTexttoNumbers.xlsx-solution to see how you did!


Choose a Seminar and Save $10:

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small> Basics

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Jan. 31

Central Time Zone

Feb. 2

Central Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 8

Eastern Time Zone

Feb. 10

Central Time Zone

Feb. 14

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 22

Central Time Zone

Feb. 24

Pacific Time Zone

Mar. 1

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Central Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 16

Central Time Zone

Mar. 17

Eastern Time Zone

Mar. 22

Central Time Zone

Mar. 24

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 29

Central Time Zone

Mar. 31

Pacific Time Zone

Apr. 4

Eastern Time Zone

Apr. 5

Central Time Zone

Apr. 7

Central Time Zone

Apr. 11

Central Time Zone

Apr. 12

Pacific Time Zone

Apr. 13

Eastern Time Zone

Apr. 19

Central Time Zone

Apr. 20

Central Time Zone

Apr. 21

Eastern Time Zone

Apr. 25

Eastern Time Zone

Apr. 27

Central Time Zone

Apr. 28

Pacific Time Zone

May. 2

Eastern Time Zone

May. 4

Central Time Zone

May. 5

Pacific Time Zone

May. 9

Eastern Time Zone

May. 10

Central Time Zone

May. 12

Central Time Zone

May. 16

Central Time Zone

May. 17

Pacific Time Zone

May. 18

Central Time Zone

May. 19

Eastern Time Zone

May. 24

Eastern Time Zone

May. 25

Central Time Zone

Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>: Beyond the Basics

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Feb. 1

Central Time Zone

Feb. 3

Central Time Zone

Feb. 4

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 9

Eastern Time Zone

Feb. 11

Central Time Zone

Feb. 15

Central Time Zone

Feb. 17

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 23

Central Time Zone

Feb. 25

Pacific Time Zone

Mar. 2

Central Time Zone

Mar. 3

Eastern Time Zone

Mar. 8

Central Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 17

Central Time Zone

Mar. 18

Eastern Time Zone

Mar. 23

Central Time Zone

Mar. 25

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 30

Central Time Zone

Apr. 1

Pacific Time Zone

Apr. 5

Eastern Time Zone

Apr. 6

Central Time Zone

Apr. 8

Central Time Zone

Apr. 12

Central Time Zone

Apr. 13

Pacific Time Zone

Apr. 14

Eastern Time Zone

Apr. 20

Central Time Zone

Apr. 21

Central Time Zone

Apr. 22

Eastern Time Zone

Apr. 26

Eastern Time Zone

Apr. 28

Central Time Zone

Apr. 29

Pacific Time Zone

May. 3

Eastern Time Zone

May. 5

Central Time Zone

May. 6

Pacific Time Zone

May. 10

Eastern Time Zone

May. 11

Central Time Zone

May. 13

Central Time Zone

May. 17

Central Time Zone

May. 18

Pacific Time Zone

May. 19

Central Time Zone

May. 20

Eastern Time Zone

May. 25

Eastern Time Zone

May. 26

Central Time Zone

Advanced Microsoft<small><sup>®</sup></small> Excel<small><sup>®</sup></small>-Macros, PivotTables, Charts and More

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Feb. 2

Eastern Time Zone

Feb. 4

Central Time Zone

Feb. 8

Pacific Time Zone

Feb. 9

Central Time Zone

Feb. 15

Eastern Time Zone

Feb. 18

Central Time Zone

Feb. 24

Central Time Zone

Mar. 3

Central Time Zone

Mar. 8

Eastern Time Zone

Mar. 11

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 21

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 31

Central Time Zone

Apr. 4

Central Time Zone

Apr. 6

Eastern Time Zone

Apr. 12

Eastern Time Zone

Apr. 14

Central Time Zone

Apr. 21

Eastern Time Zone

Apr. 22

Central Time Zone

Apr. 26

Central Time Zone

Apr. 27

Pacific Time Zone

May. 4

Central Time Zone

May. 5

Eastern Time Zone

May. 10

Central Time Zone

May. 12

Pacific Time Zone

May. 17

Eastern Time Zone

May. 20

Central Time Zone

May. 23

Central Time Zone

Related Excel® Articles

PRYOR+ 7-DAY FREE TRIAL


Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.

Categories