| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| import access data into excel | Excel Discussion (Misc queries) | |||
| import data from access to excel | Excel Worksheet Functions | |||
| Dynamic Data Import From Access | Charts and Charting in Excel | |||
| Data import issue from Access | New Users to Excel | |||
| Import Charts and data to Access | Charts and Charting in Excel | |||
Hybrid Mode
