Hello,
I have an application, which exports data into excel.
The data contains strings such 01-11 or 3-09 etc.
The moment, the spreadsheet loads, it automatically converts those strings to a date like Jan-11 or Mar-9.
I am wondering if there is something in the settings, I could change to avoid this? If not, how can I convert those dates back into the original string (to preserve also the "zeros" within the string.
Could you please advise?
Thanks
Have you tried formatting the destination columns as text before importing?
Pepe Le Mokko,
I can not format the columns because when the data is being exported from the APP, it generates a new excel file and the data lands in it before even I could make a change.
Now when the excel file opens, first it shows the format I want but the moment I start doing anything within the spreadsheet, it automatically converts to date.
FYI
Thanks
unsure if there is a way to fix this in the settings. As for a formula though. if your data starts in A1 then:
Formula:
Please Login or Register to view this content.
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
How is the spreadsheet being loaded into Excel? Is it a .csv file? If so, attach a desensitised copy here.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
AliGW,
Please see attached Excel file.
The file which comes from the App is a CSV file.
FYI
Thanks
Yes, however how are you importing it? What steps are taken, please?
I need you to attach the .csv file, please. BEFORE imported into Excel.
To my knowledge, there is no setting in Excel for "stop auto detecting dates."
Excel uses your operating system's regional and language settings for deciding what a "date" looks like. If you are willing (or allowed) to tinker with the system wide settings in your OS, you might try different date format defaults to see if you can give the system a default date format that looks nothing like the text you are trying to import and see if Excel stops detecting these as dates.
That said, I suspect that the easiest place to stop this behavior is in this (unknown to us) external app. Does this app have a setting that allows you to decide if it auto-opens Excel? Can you change anything about the text output (like adding a leading apostrophe or other character) to these values so Excel will not auto-detect them as dates? Are there any settings in the app for controlling how it exports data?
Originally Posted by shg
AliGW,
See attached CSV file.
Thanks for looking into this!
MrShorty,
Thanks for your response.
I will address this to the App Support.
This copy of the file has been saved from an Excel file and the strings already converted to dates.
I need to see a copy of it BEFORE it has been anywhere near Excel.
ok the fix you need to do is to open a blank workbook. In this workbook open the csv file extract. This will bring up the Text to Column wizard.
Select DELIMITED then hit Next.
Put a Check mark next to comma and remove the check mark from tab. Then hit Next
Finally highlight the second column which has your items that keep turning into dates. Once highlighted select Text at the top to convert column into Text.
Then hit finish
Thank you dosydos!.
I can use this solution.
Glad it will work. Thanks for the Rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks