return a number in place of a text string

pe ken

New Member
Joined
Mar 12, 2008
Messages
17
I am getting a column of data from another code and trying to convert it to the 4 digit year number. The data is in a format such that the left to characters are the 2 digit year number, example 150813 is 2015. I copy the column from the other code and place it in column A in excel. In column B, I use this formula: =20&LEFT(A1,2). Downstream formulas don't work unless I copy, paste values, then correct the "number stored as text" error. Is there an easier way? ( I already made sure the cell format was "number.") Thanks!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Downstream formulas don't work unless I copy, paste values, then correct the "number stored as text" error.
Can you post one of these "downstream formulas" which aren't working unless you do this?
 
Upvote 0
The Value one worked. That's pretty basic; I must've used the wrong words in my "help" search. Thanks so much!
 
Upvote 0
Hi Aladin,

Is there any benefit to using +0 rather than the VALUE function?

Thanks

Calling a function (like VALUE) costs a tad more than a simple text to number coercion (like with rxpression+0 or with 0+expression). I don't know why expression*1 or 1*expression still persist.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top