Forum Discussion
Need Excel to Stop Reformatting Numbers
- Precisely how are you importing the data? Have a look at this page: https://jkp-ads.com/articles/importtext.asp I know it is for WIndows Excel, but this uses very old technology which has been around for decades so I expect Mac Excel has this feature too.
is that little bugger 01-1 changing to 1-Jan, you gotta identify each cell that converts to date 1st. Use the column next to date to help with transformation then, what I did was select all, format cells. The little bugger converted to a date type text string "43466" Then continue to transform data by using the filter function to filter just the buggers that want to force into a date format, then manually type in 01-1 in those cells to get back to "clean" data for your purpose. Hope this helps you Casey-Cayce not really a good solution but a transformation solution work around.
Thanks, Jakmart...
I tried your workaround and it did work. Problem is, this worksheet has nearly 1800 rows of data—which will grow to well over 2,000—and up to 75 columns, all of which stand to have the same anomalies. So, even with such an effective and handy workaround, the bulk of my time would go to applying it to what might be hundreds of instances. Check out Jan's solution, if I could share that, though I feel a bit dumb for not knowing of it sooner
I'm keeping yours in my Excel cookbook, though, for those isolated instances where it will definitely come in handy.
Thanks again
Casey-Cayce Yes, that was a "kluge" solution, as I continue to learn, I look at your use case similar to a process followed by many people in my organization. So being an advanced user, since we decided to abandon Quatrro Pro (early 90's), I will share the import method which shows up as Power Query in today's O365 environment. Those old dialog screen shots are OBE (Obsolete by Events) when you have upgraded to Excel - O365. So best scenario is to copy paste into a text file (NorePad) or CSV (Excel Save As) and then use the import file function under the Data menu - Get and Transform ribbon section, from Tect/CSV file and then you are in the Power Query dialog window not the older (still good method) of import from Excel prior to O365. I'll share this out in my org and produce a video to solve the problem that repeats over and over base on the user doing the work, that is how I define an Agile Transformation! You now will see a query definition under the Data menu - ribbon area "Query and Connections".
- Yes, PQ is great, but in case of importing text files the old method is more transparent with regards to choosing things like the decimal separator and the date order.