SOLVED

Need Excel to Stop Reformatting Numbers

Copper Contributor

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.

8 Replies
best response confirmed by Casey-Cayce (Copper Contributor)
Solution
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.

@Jan Karel Pieterse 

 

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.

Glad I could teach you something new! :)

@Jakmart 

 

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

@Jan Karel Pieterse 

 

You did, and thank you!

@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.
1 best response

Accepted Solutions
best response confirmed by Casey-Cayce (Copper Contributor)
Solution
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.

View solution in original post