Hellow guys, Welcome to my website, and you are watching Excel - Some Part Numbers in CSV get Converted to Date: Episode 1412. and this vIdeo is uploaded by MrExcel.com at 2011-08-23T08:01:21-07:00. We are pramote this video only for entertainment and educational perpose only. So, I hop you like our website.
| Name | Excel - Some Part Numbers in CSV get Converted to Date: Episode 1412 |
| Video Uploader | Video From MrExcel.com |
| Upload Date | This Video Uploaded At 23-08-2011 15:01:21 |
| Video Discription | Microsoft Excel Tutorial: When Opening CSV, some part numbers change to dates. Welcome back to the MrExcel netcast. In this episode, we will be discussing a common issue that many Excel users face when working with data from SAP. Our viewer, David from California, has encountered a problem where some of his data is being converted to dates instead of remaining as text. Let's dive in and find a solution to this issue. Upon opening the CSV file from SAP in Excel, David noticed that some of the part numbers were not left justified like the others. Upon further investigation, he found that these part numbers were being converted to dates. This can be confirmed by using the Ctrl ~ shortcut to view the underlying value. This can be a major problem, especially if the dates fall outside of the acceptable range. So, what can we do to fix this issue? One solution would be to use the TEXT function to convert the dates back to text. However, this can be a tedious and time-consuming process, especially if there are a mix of valid and invalid dates. Another option would be to use an IF statement to check if the value is a number and then convert it to text if it is. However, this can also be a cumbersome task, especially if the part numbers are not in consecutive rows. Another workaround would be to change the file extension from .CSV to .txt and then use the "Text to Columns" wizard in Excel to properly format the data. This will ensure that the part numbers are treated as text and not converted to dates. However, this can be a hassle, especially if you have a large amount of data. In conclusion, there are a few different ways to handle this issue, but none of them are ideal. It's frustrating that SAP is sending the data in this format, but with the solutions we've discussed, David should be able to work around this problem. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Date Coincidence (00:16) Data from SAP (00:26) Part Number Problem (00:36) Solution using TEXT function (00:46) Date Conversion Issue (00:57) Alternative Solutions (01:31) Frustration with IF IS function (02:00) Possible Macro Solution (02:10) Alternative Solution: Renaming File (02:41) Using "Text to Columns" Wizard (03:25) Annoyance with SAP's File Format (03:37) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Control Panel CSV file Episode 2222: Commas Stop Working in Formulas Excel questions Function arguments Learn Excel for MrExcel Podcast List separator Regional Language settings Text import wizard VLOOKUP formula Windows 7 and Windows 10 settings Workbook download and YouTube comments Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154051/ 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. |
| Category | Education |
| Tags | Excel | business | accounting | spreadsheets | tutorial | technology | MrExcel | Date | CSV | Learn Excel 2010 | Formula | Bill Jelen | Learn Excel | Excel Tutorial | Power Excel | Microsoft Excel | Excel Tips | Data Set | Excel Help | Learn Excel with MrExcel | Excel Tips and Tricks | MrExcel Podcast | Excel 2010 | ecel basics | excel guru | excel video | excel podcast | excel forum. | looks like a date | يبدو وكأنه موعد |
