Excel - Some Part Numbers in CSV get Converted to Date: Episode 1412

998 views · Published 23 August 2011 · 3:47 · Indexed 8 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

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.

More from this channel