Simons gets an accounting file from the company system. When he opens the accounting file, some G/L [General Ledger] codes are converted from the N-NN-NNNN Format to Dates, by Excel. Today, how to prevent this from happening is the subject Bill works with in Episode #1506.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1506: G/L Codes Import As Dates.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today's question is sent in by Simon.
Simon is getting a G/L file downloaded to him every day, and when he imports that G/L file, he's very frustrated that several of the G/L codes are not coming in correctly.
You see they're all the hash signs here, the # signs, or number signs, or whatever you call it, and when we make this wider, we see that they are actually coming in as dates, [ and we’ll get – 00:34 ] CONTROL+`, you know.
So, Excel has converted them to the date serial number on the way in.
Really, really frustrating, and, of course, we could go back and try and extract this with the year function, the month function, the day function, and concatenate it back to a -, but that's just a frustrating, frustrating set steps that you would have to do every day.
There's probably a faster way to go.
So, let's go here and take a look at the file.
See, it comes in as a .CSV file.
When it comes in as .CSV, you're not given the chance to go through the data import wizard, and that's a little bit frustrating.
Let's take a look at the data here.
In notepad, we’ll just copy it over.
Alright.
So, 9-35, well, that's obviously not a date because there isn't a 35th date of September, so that comes in as a G/L code, but this one, 7-27, there is a 27th of July, so that's interpreted as a date and it comes flying in incorrectly.
So, the trick to this is to go out to just Windows explorer here, right-click, RENAME, and rename this from a .CSV to a .txt.
Now, it's going to say, hey, are you sure?
You know, it might become unusable.
Yeah.
I'm really sure.
I know what I'm doing here.
Click yes, and, of course, if you can't see the extensions, that is -- where is that -- that's TOOLS, FOLDER OPTIONS, VIEW, HIDE EXTENSIONS FOR KNOWN FILE TYPES.
I've turned that off on my computer because I want to be able to see the extensions.
So, now that we've changed this to a .txt file, when we come into Excel and we open that, so we’ll go to ZIP102 and open it, they now bring us through the text to columns wizard.
So, we can say, hey, yes, it's DELIMITED by a COMMA, and then, in step 3, we get to say that this field is a TEXT field and that tells Excel, don't try and do anything, don't lose the [ leading- 02:31 ] 0s, don't try and convert this to dates, just bring it in exactly as you see it, and our G/L codes are now perfect.
In real life, I would probably create a little one line macro that did that so that way I wouldn't have to go through and hit all 3 steps of the text to columns wizard, but certainly a lot faster than going through and trying to reconcatenate the various pieces of the G/L code that became a date.
Okay.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1506: G/L Codes Import As Dates.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today's question is sent in by Simon.
Simon is getting a G/L file downloaded to him every day, and when he imports that G/L file, he's very frustrated that several of the G/L codes are not coming in correctly.
You see they're all the hash signs here, the # signs, or number signs, or whatever you call it, and when we make this wider, we see that they are actually coming in as dates, [ and we’ll get – 00:34 ] CONTROL+`, you know.
So, Excel has converted them to the date serial number on the way in.
Really, really frustrating, and, of course, we could go back and try and extract this with the year function, the month function, the day function, and concatenate it back to a -, but that's just a frustrating, frustrating set steps that you would have to do every day.
There's probably a faster way to go.
So, let's go here and take a look at the file.
See, it comes in as a .CSV file.
When it comes in as .CSV, you're not given the chance to go through the data import wizard, and that's a little bit frustrating.
Let's take a look at the data here.
In notepad, we’ll just copy it over.
Alright.
So, 9-35, well, that's obviously not a date because there isn't a 35th date of September, so that comes in as a G/L code, but this one, 7-27, there is a 27th of July, so that's interpreted as a date and it comes flying in incorrectly.
So, the trick to this is to go out to just Windows explorer here, right-click, RENAME, and rename this from a .CSV to a .txt.
Now, it's going to say, hey, are you sure?
You know, it might become unusable.
Yeah.
I'm really sure.
I know what I'm doing here.
Click yes, and, of course, if you can't see the extensions, that is -- where is that -- that's TOOLS, FOLDER OPTIONS, VIEW, HIDE EXTENSIONS FOR KNOWN FILE TYPES.
I've turned that off on my computer because I want to be able to see the extensions.
So, now that we've changed this to a .txt file, when we come into Excel and we open that, so we’ll go to ZIP102 and open it, they now bring us through the text to columns wizard.
So, we can say, hey, yes, it's DELIMITED by a COMMA, and then, in step 3, we get to say that this field is a TEXT field and that tells Excel, don't try and do anything, don't lose the [ leading- 02:31 ] 0s, don't try and convert this to dates, just bring it in exactly as you see it, and our G/L codes are now perfect.
In real life, I would probably create a little one line macro that did that so that way I wouldn't have to go through and hit all 3 steps of the text to columns wizard, but certainly a lot faster than going through and trying to reconcatenate the various pieces of the G/L code that became a date.
Okay.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
