# Is it possible to read numbers stored as text with a formula?

#### excelos

##### Well-known Member
Hello!

Is there a formula that will read the numbers from a column that contains numbers but stored as text?

Thanks!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### DanteAmor

##### Well-known Member
If you have cell B3 this: '123.
In cell C3 put the formula: =--B3

It will give you the number 123

#### Joe4

You can also convert the whole column to "numbers entered as numbers and not text" all at once by highlighting the column, selecting "Text to Columns" from the "Data" menu, and clicking "Finish".

#### Joe4

Can that be applied to the whole column at once, or do you need to go cell-by-cell?

#### DanteAmor

##### Well-known Member
Can that be applied to the whole column at once, or do you need to go cell-by-cell?

Yes, select all cells Access to error checking box and Convert to Number

#### Joe4

Yes, select all cells Access to error checking box and Convert to Number
Cool! I never knew you could do that on multiple cells at once.
I learned a new trick today!
Thank you!

#### DanteAmor

##### Well-known Member
Cool! I never knew you could do that on multiple cells at once.
I learned a new trick today!
Thank you!

A friend from the office showed it to me . It is always possible to learn something new every day.

#### Joe4

It is always possible to learn something new every day.
Yes, especially with Excel.
You never stop learning!

#### excelos

##### Well-known Member
I need to refer to a column that has numbers stored as text for a COUNTIFS.

Can I use e.g. --\$A:\$A to refer to the column A and use it as numbers?

Replies
3
Views
153
Replies
14
Views
168
Replies
0
Views
143
Replies
8
Views
199
Replies
14
Views
223

1,195,924
Messages
6,012,339
Members
441,691
Latest member
starlightmuse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back