Thread: How do I tell Excel a Number is a Number

I have tried selecting cells that contain numerical entries but are being viewed as text and formating them as numbers but much of the time, this does not change how excel sees the entries.

There is nothing in the cell except numerical characters.

In this instance they came in from a code copying characters out of a numerical cell because some coding was changed from 4 digit to 5 digit and I'm trying to backtrack.

The Code that created the cells looks like this

=LEFT(A2,2)&RIGHT(A2,2)

where A2 has the numerical value 28058

and the result desired is the numerical value 2858.

Instead I'm getting a text value 2858, which doesn't surprise me but I can't figure out how to tell excel that this cell is a number.

Have 60 or so of these to do and I can do it by hand, but I've had this kind of problem in other settings and thought I'd ask since I'm on a roll.

Thanks

Brian

Originally Posted by Leith Ross

Hello Brian,

Use the Value function to convert the text into a number...
=VALUE(LEFT(A2,2)&RIGHT(A2,2))

Thank you Leith. Makes sense. Running out the door.
Will try later and then mark solved.

Brian

Originally Posted by Leith Ross

Hello Brian,

Use the Value function to convert the text into a number...
=VALUE(LEFT(A2,2)&RIGHT(A2,2))

Works like a charm. I wonder why formatting as a number doesn't do the same thing? On some occasions that seems to work for me when I have numerical values as text and on others it doesn't. I just haven't logically walked that back to the point of figuring out what is different when the formatting dialogue box works and does not.

Thanks

Brian