Jul 24 2019 04:53 AM - edited Jul 24 2019 04:54 AM
I can't get Excel to leave my numbers alone. I've set my column to "Text" in the format settings, but nothing I do in the number or other format settings is having any effect. It's been going on for a couple of days now, and I'm losing valuable time trying to get it to stop.
I'm importing 1700+ identifier numbers, with intermittent hyphens, for a range of books going into a database. I need to manage these numbers in a way that requires they stay exactly as they are: no leading zeros removed, none converted to dates, etc. They need to stay exactly as imported. This is a sample of what they look like before import:
00-4
000-9
01-1
001-7
002-5
003-3
3-536-3
Even when I format the column to Text, which is supposed to leave all numbers alone, the "01-1" in this list will paste as the date "1-Jan", as will all others throughout this 1700+ that resemble a date structure. I really need to get this work done and can't if Excel won't allow me to use my numbers as basic numbers. I've wiped my preferences and reinstalled Excel, but it still does it.
I'm using Excel for Mac, Office 365 Subscription, Version 16.27. Hoping there's a solution for this.
Jul 24 2019 07:09 AM
SolutionJul 24 2019 07:25 AM
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.
Jul 25 2019 03:46 AM
Perfect... thank you. I've been using Excel for a couple of decades now and have never had it be so persistently obstinate with imported data. I've always set my import up in a text doc as plain text—not RTF—, simply copied and pasted, and have been able to make that work, with the occasional nuisance to correct. After all these years, I've never used the data import function you pointed out, allowing me to specify import parameters. I guess it's a lesson in "never too late to learn".
Thanks again, Jan.
Jul 25 2019 03:51 AM
Jul 25 2019 03:53 AM
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
Jul 25 2019 03:55 AM
Jul 25 2019 07:32 AM
@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".
Jul 25 2019 09:49 AM
Jul 24 2019 07:09 AM
Solution