• #### 科普

SCIENCE

#### 英语

ENGLISH

#### 科技

TECHNOLOGY

MOVIE

FOOD

#### 励志

INSPIRATIONS

#### 社会

SOCIETY

TRAVEL

#### 动物

ANIMALS

KIDS

#### 卡通

CARTOON

#### 计算机

COMPUTER

#### 心理

PSYCHOLOGY

#### 教育

EDUCATION

#### 手工

HANDCRAFTS

#### 趣闻

MYSTERIES

CAREER

GEEKS

#### 时尚

FASHION

• 精品课
• 公开课
• 欢迎下载我们在各应用市场备受好评的APP

点击下载Android最新版本

点击下载iOS最新版本

扫码下载译学馆APP

#### 乔恩的表格校园——如何转换表格中的数字

Excel Convert Text to Numbers Keyboard Shortcuts

Welcom to ExcelCampus.

My name is Jon, and today I’m going to show you some quick tips

on how to convert numbers stored as text.

A lot of times when you get data out ofa GL software

or ERP system.

The text that’s inside the cells arenumbers.

But they actually stored in the worksheet as text.

And you can tell when they have that

small green arrow on the top left corner of the cell.

Thoes numbers are actually stored as text.

And there’s a little error box there

that shows you that this number isstored as text.
Excel有个内置功能
So there’s built-in function in Excel

to just convert that cell to a number.

You can click that it’ll do that this cell actually becomes a number.

And obviously if you have values like sales dollar values

in product codes and thoes types of things.

You’ll want to convert thoes to numbers.

So you can do calculation on them.

This is also useful if you’re doing v-lookups,

and some of your lookup or reference are stored as numbers,

and then these new values you have restored as text

that cause error in your lookups.

So there’s a lot of good reasons to convert these to numbers.

However sometimes when you have a really large sheet of data,

it becomes cubersome,

because you have to select all the cells

in the column or maybe all the cells in the worksheet,

before you can then convert them.

So you can select mutiple cells and use that function.

It just becomes tough to select them all

and sometimes you’ll loose this box all together.

So I’m gonna show you some quick tips on how to actually do that a little faster.

If you…as long as you have a cell selected

that has this box appearing to the left of it.

You can use some keyboard shortcuts to then select all the cells in your worksheet.

If you click Ctrl+A,

you’ll select all the cells in the range.

And if you hit Ctrl+A again, you’ll select all the cells in the worksheet.

So Ctrl+A once will select all the cells in the contiguous range.

If you have a blank column like in this example,

then if you hit Ctrl+A again, it’ll select all the cells in the entire worksheet.

Then that same error box should be in the top left corner of the worksheet,

and you can use the Convert to Number function

to convert all of your numbers stored as text to numbers.

Another option if you just want to highlight all the cells in this column,

it’s in column B.

And you can click Ctrl+Spacebar on the keyboard, it’s Ctrl+Spacebar.

And that’ll highlight the entire column.

And the error box should be at the top of the column.

And you can do the same thing Convert to Number.

That’ll convert the entire column to numbers.

That’s same feature also works by using

Shift+Spacebar to select the entire row.

And you’ll see again that the error box is over there on the left side.

If you want to do the conversion on more than one column,

and you could first click Ctrl+Spacebar

to select this column D.

And if you hold down the Shift key and use the Left Right arrows,

you can select mutiple columns.

So if we just want to convert these two columns to numbers,

we could have those selected and then run the error box up here.

And click the Convert to Number.

And then we just only select in those two columns to do the conversion.

It works the same with the rows as well.

If you hold Shift+Spacebar, you’ll select the entire row.

And then you can hold down the Shift key, and use the Up and Down arrows

to select more than one row.

Once you have the row selected you could then see the error box on the left side,

and click Convert to Number.

So that’s a quick way to select all your cells

in a column or in a range,

and then convert them to number

using the built-in function in Excel.

There are other ways to do this

using the Text to Columns functions on the data menu as well.

And you can also use formulas, you could use a paste special,

and do a multiplication on the text in the cell itself.

There’s a lot of different ways to do this.

But I find that the fastest and the easiest is

to use the built-in feature in Excel.

Just a matter of knowing how to select the entire range that you want it to convert.

I hope this helps. If you have any questions,

Thank you.

AF