SoylentNews is people
SoylentNews
If you've ever tried to use the CONCATENATE function in Microsoft Excel to merge the values in a range of cells, you know it doesn't work unless you add each cell to the function, one by one.
You might have noticed the following message in the support article for CONCATENATE:
Important: In Excel 2016, Excel Mobile, and Excel Online, this function has been replaced with the CONCAT function. Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.
Meet the alternatives: CONCAT and TEXTJOIN
for CONCAT and TEXTJOIN:
Note: This feature is not available in Excel 2016 unless you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.
While it is admirable that Microsoft is finally fixing some of the idiosyncrasies of its software, I fear the future will bring a level of fragmentation unseen since the office 2003 to 2007 switch.
(Score: 5, Disagree) by bzipitidoo on Monday November 26 2018, @04:52PM (15 children)
> LibreOffice Calc is a poor substitute for Excel for anything beyond relatively simple spreadsheets
I keep hearing this, and it sounds like perfect FUD. Microsoft shills are always saying vague, scary sounding stuff exactly like this. Where's the evidence? The bug reports? Admit it, it's M$ propaganda. There might even be a tiny bit of truth to it, but wildly exaggerated. For instance, if "relatively simple" means anything less than a billion cells, and LibreOffice can handle that, while Excel can handle a billion plus another few thousand, I could see M$ trumpeting that 0.0001% greater capacity in a deceptive and misleading manner. But, if a spreadsheet is that freaking huge, you're better off using a database anyway.
One thing we can count on is that LibreOffice will never force users onto a costly upgrade treadmill. CONCATENATE "may not be available in future versions", okay, there can be good reasons for that. But the replacement is not available unless you subscribe to their SAAS scam? WTF??
(Score: 0) by Anonymous Coward on Monday November 26 2018, @05:08PM (2 children)
The only problem I've had is that Libreoffice refuses to take information from a table from my browser and paste correctly. It insists upon making it a single column or putting too much into a single cell.
It's hardly an insurmountable problem, but it's really annoying to have to manually separate things into columns that should be automatically done.
(Score: 0) by Anonymous Coward on Tuesday November 27 2018, @05:51AM
I've noticed that with some tables you need to use paste special.
(Score: 2) by Osamabobama on Tuesday November 27 2018, @06:21PM
I have the same problem with Excel. I thought it was just part of the human condition.
Appended to the end of comments you post. Max: 120 chars.
(Score: 5, Informative) by digitalaudiorock on Monday November 26 2018, @05:52PM (2 children)
I can list several areas where Libreoffice works far better than excel. Some of them are insanely dumb things that Excel does with TSV format importing and the like:
1. If you associate the TSV extension with excel in order to automatically open them in excel, doing so does NOT bring up the same import dialog (for example to verify the column data types etc) that you get with File -> Open. When it gets that wrong it can do irreparable things to the data and you're screwed. For example I've seen ids that happened to be HEX strings loose all the leading zeros because they get treated as numbers. The only work around is to save it first. Libreoffice does NOT have this issue.
2. For whatever reason, if any data in a cell of a TSV file happens to have the text "<?xml" anywhere in the data, excel refuses to open the TSV saying that it's in some other format than the extension. I mean holy hell...the TSV format basically amounts to "columns are delimited by tabs...rows are delimited by newlines". You could write a parser for that in 5 minutes. What are they doing? Again, Libreoffice doesn't have this issue.
3. When exporting from excel when using the English U.S. locale, they do NOT offer the single most convenient date/time format...that is YYYY-MM-DD hh:mm:ss. The ONLY means of getting that option is to switch to some other locale...I think English GB has it for example. Again...not an issue with Libreoffce.
One I recall from many many years ago that may now be fixed(?) was that exporting an XLS format spread sheet to TSV in cases where cells of the XLS contained new lines, did NOT convert the newlines in the data to spaces (which the archaic OpenOffice of the time did) obviously resulting in a broken useless pile of crap with erroneous row delimiters.
I tend to think a lot of this sort of behavior is intentional, because it tends to be related to using the data with other...God forbid...possibly non-MS software. Excel is a flaming POS.
(Score: 0) by Anonymous Coward on Tuesday November 27 2018, @12:20PM (1 child)
"associate the TSV extension with excel"
How did you do this in Windows 10? So far I have completely failed to be able to do so. It shows TSV in the known file extension list but nothing can be associated with it.
(Score: 2) by digitalaudiorock on Tuesday November 27 2018, @09:53PM
Actually I haven't myself but I believe coworkers of mine have. I use almost nothing but Linux and don't even have office installed on my only Windows system. It should just be a matter of right-clicking a TSV file and choosing "open with" and selecting to use that as teh default. However I seem to read a LOT of posts where Excel simply isn't an option there with no resolution at all...typical MS and a reminder of why I use Linux frankly. If I find out any specifics I'll post back.
(Score: 0) by Anonymous Coward on Monday November 26 2018, @06:02PM (1 child)
> if a spreadsheet is that freaking huge, you're better off using a database anyway.
Exactly! And yet in practice instead of a database lots of us are stuck with excel.
(Score: 0) by Anonymous Coward on Tuesday November 27 2018, @06:30AM
Becaus PHBs (almost) understand spreadsheets.
(Score: 3, Informative) by DeathMonkey on Monday November 26 2018, @07:31PM (3 children)
> LibreOffice Calc is a poor substitute for Excel for anything beyond relatively simple spreadsheets
For instance, if "relatively simple" means anything less than a billion cells, and LibreOffice can handle that, while Excel can handle a billion plus another few thousand, ....
I think you've stumbled across another reason we don't see a lot of these Excel to Calc conversions. In a lot of these business cases, Excel is not the proper tool for the job in the first place.
So when you finally decide to replace these Excel solutions you realize a database is actually the proper tool and migrate to that instead of just another spreadsheet.
(Score: 4, Interesting) by krishnoid on Monday November 26 2018, @10:14PM (2 children)
*We* know that. You also know that business uses spreadsheets as a common denominator across multiple departments with a gradual learning curve. Maybe a database-backed spreadsheet would see some adoption?
(Score: 2) by DeathMonkey on Monday November 26 2018, @10:36PM (1 child)
Well yeah, cause "We" are the ones who get called in for the replacement when Excel finally becomes too painful to maintain.
I've replaced a LOT of spreadsheets in my career and it's never been with a different spreadsheet.
(Score: 2) by krishnoid on Tuesday November 27 2018, @12:33AM
I somehow overlooked the "finally replace" part in your text. That totally makes sense.
(Score: 3, Informative) by edIII on Monday November 26 2018, @09:29PM
Total FUD. I've been dealing with databases and data entry for damn near 30 years now. My first spreadsheet software was VisiCalc on an Apple II, and then on the Apple IIe. I remember getting a 286 PC and started using Lotus 123 in the early 80s (I still miss Lotus). I've been with Excel since it started in Windows, and then developed entire niche CRM platforms in Access when it came around in the mid 90's.
The super complicated stuff they're probably referring to is integration in Excel with other M$ products (like the aforementioned Access), and the light form of Visual Basic programming they had under the hood. ActiveX controls, and shit like that. It was with an ActiveX control that I authored an Excel spreadsheet capable of using a modem to dial out, call a line, retrieve some info using DTMF tones, and then update the spreadsheet for the suits. At that point, I'm hard pressed to believe it is still a spreadsheet and not some Frankenstein'd native app cobbled together using Excel like an IDE. If you are really using Excel like a database, then you are using lookup functions. Haven't tried the equivalent (because I can create an actual ACID compliant database in 5 mins) in LibreOffice, but I'm sure that it is there. Heck, there are functions in Excel that allowed you to look up data in M$ SQL servers. Yes, if you are using Excel like a front end for a different database, also based in M$, then LibreOffice may not be for you as you are too vendor locked (and therefore fucked). It's not a real apples to apples comparison.
Having extensively used Excel as a prep tool for data entry, I can with a high degree of confidence say that LibreOffice is actually better at it. I started with OpenOffice first too, and found I wanted to use it more than Excel. At that point, Excel was retired, and that was close to 10 years ago now? Damn time flies.
Copying and pasting data from different tables and fields, from browsers and other apps, into LibreOffice Calc causes it to pop-up dialog box asking how to parse it. Excel 2007 (latest I've used) required opening the document, and you had to go out of your way to get to where you could control the parsing. Excel also mangled data fields a lot. A lot. LibreOffice does it less. I think one time, just once, my problems with LibreOffice were so bad that I needed to use Excel 2007 instead. That's once out of hundreds of different data entry jobs.
The vast majority of spreadsheets that move beyond the "relatively simple" belong in a database anyways. Why they continue to be in a spreadsheet program is also very simply to explain; Spreadsheet programs are natively better front end tools for data than a transactional database manipulated with SQL statements. Which is the greatest reason I still use it for prep work, and that is front end tools for databases need to made, and not all of them are so great at importing data and manipulating it.
Technically, lunchtime is at any moment. It's just a wave function.
(Score: 2) by dwilson on Tuesday November 27 2018, @01:16AM (1 child)
By far, the biggest problems I've had with Libreoffice Calc vs Microsoft Excel is Calc's occasional, hit-and-miss incompatibility with Excel's VBA support and associated functions.
...which sort of makes sense, because they are two different programs. But not being able to take a VBA macro written for Excel and drop it in Calc and go, is a pretty big show-stopper to any organization with a LOT of time and effort invested in VBA macros.
- D
(Score: 5, Touché) by Hawkwind on Tuesday November 27 2018, @02:11AM
My condolences