return a number in place of a text string

[pe ken]

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!

 

[Joe4]

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?

 

[pe ken]

=if(b1=i1,j1,k1)

 

[Aladin Akyurek]

Or run...

=20&LEFT(A1,2)+0

 

[a massive zebra]

Replace your formula with:

=VALUE(20&LEFT(A1,2))

or

=(20&LEFT(A1,2))*1

 

[a massive zebra]

Or run...

=20&LEFT(A1,2)+0

Hi Aladin,

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

Thanks

 

[pe ken]

The Value one worked. That's pretty basic; I must've used the wrong words in my "help" search. Thanks so much!

 

[Aladin Akyurek]

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.