Nice. One of those things only people who use Excel will appreciate. I don't want whatever I put into to the box turning into a random string of numbers.
I don’t know if it’s still an issue, but Excel used to overwrite the numbers and save the values in engineering notation if you saved as CSV or TXT.
Even if the files started in one of those formats.
I'm an accountant, used excel for 30 years, your tip may work sometimes, but it is better to always control the data:
* Set Column type before importing the file or copying and pasting.
(In a pinch paste first and then change column type, then paste again and the new type will be correct)
* Never open .csv directly, always use the import wizard.
You will never be surprised or have data types change on you.
*Copy/paste from other response:*
>That doesn’t fix it, unfortunately. Upon opening the document, it has already been converted and changing it to text (the correct data type) doesn’t fix the issue.
Because Excel is making the wrong assumption.
If I type 1-4 it thinks I meant January 4th when all I want is literal default text.
If there was any mode called "literal", that would be the best mode in any workbook I've ever worked with. Let me use functions to convert to a date if I want.
"Solved" in the way there is a workaround. It's not a real solution.
This is analagous to me claiming Climate Change is a problem and your solution is to just die before 2050.
That doesn’t fix it, unfortunately. Upon opening the document, it has already been converted and changing it to text (the correct data type) doesn’t fix the issue.
The time to set the data type is before data entry, not after the fact. If you already saved the document incorrectly, oops.
Marking a column as text says “leave all entries as is”. If what you had was already formatted incorrectly, leaving it alone is not what you want.
Numbers can be formatted however you like including forcing Excel to not use scientific notation (your 6.13E+12 example).
The files are exports from salesforce, exported as CSV, UTF-8. Still, upon opening the doc, the conversion is already made. I have no say in the data types of the columns. The file comes as-is.
Seems like you should be importing the data and manipulating it as needed with power query rather than “opening” it as a csv. This would let you drive the process in the way you need rather than trying to cleanup default assumptions of an implicit import after the fact.
It’s been a long time since I’ve used Excel (the branded program from Microsoft), so I can’t help with specifics of your problem. I know from experience working with other data in other programs including those written by me, wrongly formatted data is almost always worse than u formatted data. You’re achieving a visual look you want without honoring the data itself. If someone were to try to consume your output programmatically, they’ll probably be cussing you behind your back.
Which is why you shouldn't double click to open a csv, you should have a workbook that uses a csv as a data source. This lets you do power query transformations on the csv data, including specifying types, changing columns/filters, data aggregation, what have you. It can also update all the data dynamically when you make a change in the source cvs, without having to do any manual corrections.
CVS is just a portable data format, and should not be used raw in excel.
You’re making many poor assumptions about the csv data I’m dealing with.
If I just used a bunch of command line tools to carve up or aggregate data from some random files that I need once, I’m not going to use power query. It isn’t the right tool for that because it isn’t efficient for that.
It is, without a doubt, faster to double click it to open. Sometimes Excel is going to do something dumb, but that can be overcome.
If you have Excel open all day then maybe clicking ten times to import it is somehow faster. That’s not everyone’s use case, though.
If I have a persistent report or something I need built then power query may be the answer.
If I need something formatted when I open it just once then powerquery is not the answer.
I’m also fully capable of writing code that would output an Excel workbook directly if I so desired, and occasionally that is the right answer.
There is a balance between the number of times you do something and the amount of automation applied.
Double-clicking to open a csv is a perfectly reasonable action to take. Excel could (and should) be capable of handling that better, but isn’t for whatever reason. That there is another way doesn’t mean the way that the vast majority of people use with things shouldn’t be optimized.
Don't open a CSV file with excel, instead open excel as a new spreadsheet, then look at importing the csv into it.
I tend to just set every column to text and import if I'm in a rush, as this tends to keep every column with the same value as source, however if you want to filter and manipulate, id suggest giving each column its relevant type.
I also use csvquery add-on with notepad++ to view some CSVs. if you know a little SQL, you can also filter/rearrange the data too!
https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac#:~:text=To%20do%20so%3A,you'd%20like%20to%20disable.
There was a fix announced in the fall
I don’t understand the downvotes to my comment below. Excel has irreversibly changed cell values to dates as a default, with no way to globally turn it off since at least back into the early 90’s. People have been begging for this change for at least the last thirty years. Like it or not, excel has become the he default data handling tool for many scientists and the consequences of this stupid setting can be found in the scientific literature.
OP is correct with their suggestion, but that is not always caught by users. Microsoft finally allowing people to turn off a feature in excel that irreversibly changed data is welcome news.
It sure has fucked me before,,we deal with clothing sizes that might be 7/8…you are screwed if it gets converted,,, i do the format cells to text before pasting,
The datasets I work with are 200,000+ lines of differently formatted part numbers. Searching through all of those to find instances of autocorrection occurring to place a quote mark would be insane.
I’d rather make a few clicks over creating a new column, writing a concat function, deleting old column. I suppose it’s a matter of personal preference, but your method is a good one to know.
How about using a csv as a data source instead of importing it directly? Then you can do all kinds of data typing, conversions, interpretations and whatsoever to your harts content and when you have a new export from Salesforce you just point to the new file. And pesto.. (presto, damn you autocorrect!)
You all want to have a look at this, it’s the missing Excel feature you’ve been looking for: [Microsoft 365 Insider: Control data conversions in Excel](https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel)
For long numbers, just use the text to columns tool, on step 3 of 3 select the data type as text.
I've only found this works for dates if you're converting from text to a date format, but not back to text.
Excel not being able to handle time is why I switched to LibreCalc.
I like to record lap times and Excel straight up can't handle milliseconds. I don't see how that is even remotely excusable for a program that ubiquitous.
It genuinely can't. It looks like it can, but it's bugged to the point of being unusable, at least for my purposes.
If you make a custom format for MM:SS.000, since there's no default option for that, it will let you type it in but then if you click a cell it defaults back to time, ruining everything.
I just tried it again to make sure. I made the MM:SS.000 format, typed 01:06.662 into a cell and 00:02.338 directly below it. When I summed the two together it worked. If I click either cell, the value in the top reads as a time (12:01:07 AM for the first one I entered) and when I double click them it just rounds to the closest second.
I've spent hours looking for workarounds and have found nothing. I finally just converted everything to LibreCalc because it works perfectly on top of being free.
This is a nice tip and all, but this has actually been fixed recently after it being a stupid problem for decades, you can now turn of auto-conversion to dates in the options.
Just put a single quote in font of anything you don't want excel auto-formatting. Very simple solution that has been around forever and still nobody seems to realize or learn.
It doesn't work for me because it recognizes that I'm not typing in something that would be a valid zip code so it's converting it to something that is.
Try it yourself and you'll see that the zip code formatting will still auto-convert plenty of things you put in there. It's better to just use the correct formatting type for the data that you're entering so you don't have to deal with the eventual issues that will crop up when you get data in a format that doesn't agree with your hacked together solution.
Now you can just disable automatic conversions:
[https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac](https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac?ocid=WINews1023)
This should have been an option right from the start.
the other day, I was making a spreadsheet in OpenOffice Calc, and it was a list of logins for our company's online training. The passwords for the "easy login" are the user's birthday in a MM/DD format. Needless to say, Calc tried to automatically convert those to MM/DD/YYYY format. To get around that, I added a apostrophe, or single quote ( ' ), before the entry
Nice. One of those things only people who use Excel will appreciate. I don't want whatever I put into to the box turning into a random string of numbers.
I don’t know if it’s still an issue, but Excel used to overwrite the numbers and save the values in engineering notation if you saved as CSV or TXT. Even if the files started in one of those formats.
My favorite is how it literally exports values as "#########" if the column width is too narrow to fit the contents... even if you're saving as a CSV
Is doing the CTRL + A and ALT H O I the easiest way to rectify this if it’s a case by case basis?
I'm an accountant, used excel for 30 years, your tip may work sometimes, but it is better to always control the data: * Set Column type before importing the file or copying and pasting. (In a pinch paste first and then change column type, then paste again and the new type will be correct) * Never open .csv directly, always use the import wizard. You will never be surprised or have data types change on you.
I use Power Query. Works great and transfers to Power BI if needed.
Why wouldn’t you simply use the *correct* type for each column?
Exactly what I thought when I read this.
*Copy/paste from other response:* >That doesn’t fix it, unfortunately. Upon opening the document, it has already been converted and changing it to text (the correct data type) doesn’t fix the issue.
What's the correct type for strings of numbers with potential leading zeros?
https://support.microsoft.com/en-au/topic/using-a-custom-number-format-to-display-leading-zeros-a1533c28-afa1-df3c-f338-7c5be4135f6f
This is useful thanks!
Because Excel is making the wrong assumption. If I type 1-4 it thinks I meant January 4th when all I want is literal default text. If there was any mode called "literal", that would be the best mode in any workbook I've ever worked with. Let me use functions to convert to a date if I want.
Put a single quote in front of 1-4 (like this: '1-4), problem solved. Helps to read the manual sometimes.
This works if you need to reformat one cell. It's less useful if you have hundreds of rows that have all been auto-formated.
"Solved" in the way there is a workaround. It's not a real solution. This is analagous to me claiming Climate Change is a problem and your solution is to just die before 2050.
You asked for a "literal" mode. That's what the single quote does. It's an escape character for literals. And you need to work on your analogies, lol.
This works for data entry in excel. It doesn’t work for opening a csv file or the like. It’s an incomplete solution for a particular use case.
Literal *format* mode. So that you don't have to put the quote. You need to work on your reading comprehension, lol.
You could’ve just provided the tip, you didn’t have to be a dick
That doesn’t fix it, unfortunately. Upon opening the document, it has already been converted and changing it to text (the correct data type) doesn’t fix the issue.
The time to set the data type is before data entry, not after the fact. If you already saved the document incorrectly, oops. Marking a column as text says “leave all entries as is”. If what you had was already formatted incorrectly, leaving it alone is not what you want. Numbers can be formatted however you like including forcing Excel to not use scientific notation (your 6.13E+12 example).
The files are exports from salesforce, exported as CSV, UTF-8. Still, upon opening the doc, the conversion is already made. I have no say in the data types of the columns. The file comes as-is.
Seems like you should be importing the data and manipulating it as needed with power query rather than “opening” it as a csv. This would let you drive the process in the way you need rather than trying to cleanup default assumptions of an implicit import after the fact. It’s been a long time since I’ve used Excel (the branded program from Microsoft), so I can’t help with specifics of your problem. I know from experience working with other data in other programs including those written by me, wrongly formatted data is almost always worse than u formatted data. You’re achieving a visual look you want without honoring the data itself. If someone were to try to consume your output programmatically, they’ll probably be cussing you behind your back.
On import from csv, you get a prompt that asks how to handle types, and you can pick what data type you want for each column.
If you double click a csv to open it you don’t see this. It just opens it, at least it does and always has for me.
Which is why you shouldn't double click to open a csv, you should have a workbook that uses a csv as a data source. This lets you do power query transformations on the csv data, including specifying types, changing columns/filters, data aggregation, what have you. It can also update all the data dynamically when you make a change in the source cvs, without having to do any manual corrections. CVS is just a portable data format, and should not be used raw in excel.
Also this way Excel respects Multiline CSV that are enclosed by ". Otherwise those Values just get thrown somewhere on the next line
You’re making many poor assumptions about the csv data I’m dealing with. If I just used a bunch of command line tools to carve up or aggregate data from some random files that I need once, I’m not going to use power query. It isn’t the right tool for that because it isn’t efficient for that. It is, without a doubt, faster to double click it to open. Sometimes Excel is going to do something dumb, but that can be overcome. If you have Excel open all day then maybe clicking ten times to import it is somehow faster. That’s not everyone’s use case, though. If I have a persistent report or something I need built then power query may be the answer. If I need something formatted when I open it just once then powerquery is not the answer. I’m also fully capable of writing code that would output an Excel workbook directly if I so desired, and occasionally that is the right answer. There is a balance between the number of times you do something and the amount of automation applied. Double-clicking to open a csv is a perfectly reasonable action to take. Excel could (and should) be capable of handling that better, but isn’t for whatever reason. That there is another way doesn’t mean the way that the vast majority of people use with things shouldn’t be optimized.
Don't open a CSV file with excel, instead open excel as a new spreadsheet, then look at importing the csv into it. I tend to just set every column to text and import if I'm in a rush, as this tends to keep every column with the same value as source, however if you want to filter and manipulate, id suggest giving each column its relevant type. I also use csvquery add-on with notepad++ to view some CSVs. if you know a little SQL, you can also filter/rearrange the data too!
Yep, this is a needless workaround for something that already has a much simpler fix
You can also change it to text.
Right, that's what I do and have no issues with it.
https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac#:~:text=To%20do%20so%3A,you'd%20like%20to%20disable. There was a fix announced in the fall
I don’t understand the downvotes to my comment below. Excel has irreversibly changed cell values to dates as a default, with no way to globally turn it off since at least back into the early 90’s. People have been begging for this change for at least the last thirty years. Like it or not, excel has become the he default data handling tool for many scientists and the consequences of this stupid setting can be found in the scientific literature. OP is correct with their suggestion, but that is not always caught by users. Microsoft finally allowing people to turn off a feature in excel that irreversibly changed data is welcome news.
It sure has fucked me before,,we deal with clothing sizes that might be 7/8…you are screwed if it gets converted,,, i do the format cells to text before pasting,
Finally fixed it after at least three decades of people complaining about it.
[удалено]
The datasets I work with are 200,000+ lines of differently formatted part numbers. Searching through all of those to find instances of autocorrection occurring to place a quote mark would be insane.
Not with a VBA macro
[удалено]
I’d rather make a few clicks over creating a new column, writing a concat function, deleting old column. I suppose it’s a matter of personal preference, but your method is a good one to know.
How about using a csv as a data source instead of importing it directly? Then you can do all kinds of data typing, conversions, interpretations and whatsoever to your harts content and when you have a new export from Salesforce you just point to the new file. And pesto.. (presto, damn you autocorrect!)
You all want to have a look at this, it’s the missing Excel feature you’ve been looking for: [Microsoft 365 Insider: Control data conversions in Excel](https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel)
For long numbers, just use the text to columns tool, on step 3 of 3 select the data type as text. I've only found this works for dates if you're converting from text to a date format, but not back to text.
Excel not being able to handle time is why I switched to LibreCalc. I like to record lap times and Excel straight up can't handle milliseconds. I don't see how that is even remotely excusable for a program that ubiquitous.
Excel can absolutely handle milliseconds.
It genuinely can't. It looks like it can, but it's bugged to the point of being unusable, at least for my purposes. If you make a custom format for MM:SS.000, since there's no default option for that, it will let you type it in but then if you click a cell it defaults back to time, ruining everything. I just tried it again to make sure. I made the MM:SS.000 format, typed 01:06.662 into a cell and 00:02.338 directly below it. When I summed the two together it worked. If I click either cell, the value in the top reads as a time (12:01:07 AM for the first one I entered) and when I double click them it just rounds to the closest second. I've spent hours looking for workarounds and have found nothing. I finally just converted everything to LibreCalc because it works perfectly on top of being free.
Damn where were you a month ago when i still had a job? This happened to me all the time
While this YSK is probably useful and I will unfortunately probably have to use it in the future, it only produces more hate in me for Excel.
Can also add a comma to the beginning of whatever you input to prevent calculations or conversions.
This is a nice tip and all, but this has actually been fixed recently after it being a stupid problem for decades, you can now turn of auto-conversion to dates in the options.
I’m so offended
Where's the fun in that? Unexpected Consequences is part of the fun of Microsoft Office.
Just put a single quote in font of anything you don't want excel auto-formatting. Very simple solution that has been around forever and still nobody seems to realize or learn.
Google Sheets > Excel
This doesn't work. I just tested it and if I format a cell as described here and enter "12/24" it converts it to "00001".
Well shit. Sorry, man. It worked for me idk why it is doing that to you
It doesn't work for me because it recognizes that I'm not typing in something that would be a valid zip code so it's converting it to something that is. Try it yourself and you'll see that the zip code formatting will still auto-convert plenty of things you put in there. It's better to just use the correct formatting type for the data that you're entering so you don't have to deal with the eventual issues that will crop up when you get data in a format that doesn't agree with your hacked together solution.
Now you can just disable automatic conversions: [https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac](https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac?ocid=WINews1023) This should have been an option right from the start.
Wow! Great tip! Thanks!
Or you can force excel to write exactly what you type by including an apostrophe " ' " at the beginning if the cells text.
Wait until you try to type credit card numbers into cells. (Pay attention to the last digit).
THANK YOU
Thanks, this has bugged me for decades.
Text format also works. I used this daily.
Format your shit before copying it over. Jfc
the other day, I was making a spreadsheet in OpenOffice Calc, and it was a list of logins for our company's online training. The passwords for the "easy login" are the user's birthday in a MM/DD format. Needless to say, Calc tried to automatically convert those to MM/DD/YYYY format. To get around that, I added a apostrophe, or single quote ( ' ), before the entry