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.
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.