Import data from Access and doesnt recognize numbers in left func.
 

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in left func.
 

Try

=(IF(VALUE(LEFT(B2,2))<11,20&LEFT(B2,2),19&LEFT(B2 ,2)))*1

Mike

"tpeter" wrote:

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in leftfunc.
 

Try it like this:

=IF(LEFT(B2,2)*1<11,2000+LEFT(B2,2),1900+LEFT(B2,2 ))

This will give you proper numbers, rather than text values that look
like numbers.

Hope this helps.

Pete


On Aug 12, 10:15*pm, tpeter wrote:
I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 *(would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in left f
 

WOW,

That was perfect, thank you, what is the *1 for?

"Mike H" wrote:

Try

=(IF(VALUE(LEFT(B2,2))<11,20&LEFT(B2,2),19&LEFT(B2 ,2)))*1

Mike

"tpeter" wrote:

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in left f
 

hi,

Your formula returned a text string, multiplying by 1 made it a number.
leaving it as text 'might' have created issues if you were doing lookup
comparisons.

Glad I could help

Mike

"tpeter" wrote:

WOW,

That was perfect, thank you, what is the *1 for?

"Mike H" wrote:

Try

=(IF(VALUE(LEFT(B2,2))<11,20&LEFT(B2,2),19&LEFT(B2 ,2)))*1

Mike

"tpeter" wrote:

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in left func.
 

Try this:

=IF(--LEFT(A1,2)<11,2000,1900)+LEFT(A1,2)


"tpeter" wrote:

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.

Import data from Access and doesnt recognize numbers in left func.
 

Another...

=(--LEFT(A1,2)<11)*100+1900+LEFT(A1,2)


"tpeter" wrote:

I have a database that is sorted and imported into excel for charting
purposes. I have all the data there but need to pull out the first 2 numbers
of a field to create a year. Here is an example of the data I am importing:

93h00151615 (would be 1993)
10j56465665 (would be 2010)

I am using this function to accomplish this in another colume:

if(left(b2,2)<11,20&left(b2,2),19&left(b2,2)

This always uses the default because it doesn't reconize the first two
characters as letters. I have reformated the cells but it doesn't work.

Thank you for your help.