David gets data from SAP that he loads into Excel. Some part numbers in the import data just happen to look like dates and end up being converted to dates by Excel. Today, in Episode #1412, Bill discusses two ways to solve this problem.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn excel from MrExcel. Episode # 1412 Looks like a date coincidentally Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by David from California.
David is getting some data from SAP .
Now you didn't say that specifically by suspect, it's really coming down as a CSV file when he opens this file in excel. All right, so check that out.
That's a part number. They're up in cell A1. Alright good enough, but as he starts to scroll down through this data check out what happens down here in row 73 All right first half. Like the previous part numbers these are not left justified and when we actually look at the real value stored there, it's being converted to a date and you can see that for sure Ctrl ~ shows us a value so that part number is coming into text this part number is coming in as a serial number.
What a bad thing that is okay, so I started to think about this well, what could we do. We could we could use the text function so =TEXT of A73 in quotes mm-dd-yyyy Close quotes Close parenthesis and that will convert it back to text.
It'll look kind of like that one.
You know but here this date happen to fall in 1621 this date happen to fall in 2210.
This is a valid date. That one's not.
This one got converted that one didn't oh, what are we going to do now.
Well I mean since you're going to have a mix of these you kind have to say =IF(ISNUMBER(A73), then do that conversion otherwise, we just want A73 Scroll over here, so you can see that.
So there we go, and then you kind of have to copy that and what's really frustrating I dont know why IF IS would do this.
This is just probably one of the most annoying things I've ever seen is those part numbers only appear not on every row but on every other row so it's going to be a painful painful way to do this.
Hopefully you're hoping that David can have some sort of a macro to deal with these and put all these back together in a single row or something like that.
You know the other way to go would be to not pull it in as a CSV file. You'd have to go back to the original data and do that little trick in Windows explorer to say.
Hey, I want to see the extension even if it's a known item and then rename and rename it from .CSV to .txt Yep, okay, so then we will in excel pull that file in. Because it's a text file we now get to go through the "Text to Columns" wizard and so we can say that things are delimited by a comma and Oh by the way this first column here I want to treat that as text and then even if it happens to coincidentally look like a date, it's going to come in as a text field instead of a date just to prove it. Let's see up there and also Ctrl+T or Ctrl grab accent back and forth it continues to show everything the same way.
So a couple of different ways neither one is very friendly Kind of annoying that SAP is sending the file down in this manner.
If it was at least all in column A we kind of do a nice formula. That would solve it, so David that's why it's happening.
A couple of solutions, couple of workarounds. Neither one of those are things that I would enjoy doing but ofcourse better than going through and fixing them all manually, so there you have it.
Well hey, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
Learn excel from MrExcel. Episode # 1412 Looks like a date coincidentally Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Today's question sent in by David from California.
David is getting some data from SAP .
Now you didn't say that specifically by suspect, it's really coming down as a CSV file when he opens this file in excel. All right, so check that out.
That's a part number. They're up in cell A1. Alright good enough, but as he starts to scroll down through this data check out what happens down here in row 73 All right first half. Like the previous part numbers these are not left justified and when we actually look at the real value stored there, it's being converted to a date and you can see that for sure Ctrl ~ shows us a value so that part number is coming into text this part number is coming in as a serial number.
What a bad thing that is okay, so I started to think about this well, what could we do. We could we could use the text function so =TEXT of A73 in quotes mm-dd-yyyy Close quotes Close parenthesis and that will convert it back to text.
It'll look kind of like that one.
You know but here this date happen to fall in 1621 this date happen to fall in 2210.
This is a valid date. That one's not.
This one got converted that one didn't oh, what are we going to do now.
Well I mean since you're going to have a mix of these you kind have to say =IF(ISNUMBER(A73), then do that conversion otherwise, we just want A73 Scroll over here, so you can see that.
So there we go, and then you kind of have to copy that and what's really frustrating I dont know why IF IS would do this.
This is just probably one of the most annoying things I've ever seen is those part numbers only appear not on every row but on every other row so it's going to be a painful painful way to do this.
Hopefully you're hoping that David can have some sort of a macro to deal with these and put all these back together in a single row or something like that.
You know the other way to go would be to not pull it in as a CSV file. You'd have to go back to the original data and do that little trick in Windows explorer to say.
Hey, I want to see the extension even if it's a known item and then rename and rename it from .CSV to .txt Yep, okay, so then we will in excel pull that file in. Because it's a text file we now get to go through the "Text to Columns" wizard and so we can say that things are delimited by a comma and Oh by the way this first column here I want to treat that as text and then even if it happens to coincidentally look like a date, it's going to come in as a text field instead of a date just to prove it. Let's see up there and also Ctrl+T or Ctrl grab accent back and forth it continues to show everything the same way.
So a couple of different ways neither one is very friendly Kind of annoying that SAP is sending the file down in this manner.
If it was at least all in column A we kind of do a nice formula. That would solve it, so David that's why it's happening.
A couple of solutions, couple of workarounds. Neither one of those are things that I would enjoy doing but ofcourse better than going through and fixing them all manually, so there you have it.
Well hey, I want to thank you for stopping by. Will see you next time for another netcast from MrExcel.
