Slash Boxes

SoylentNews is people

posted by martyb on Monday November 26 2018, @02:32PM   Printer-friendly
from the putting-it-all-together dept.

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


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.

Original Submission

This discussion has been archived. No new comments can be posted.
Display Options Threshold/Breakthrough Mark All as Read Mark All as Unread
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
  • (Score: 5, Informative) by digitalaudiorock on Monday November 26 2018, @05:52PM (2 children)

    by digitalaudiorock (688) on Monday November 26 2018, @05:52PM (#766515)

    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.

    Starting Score:    1  point
    Moderation   +3  
       Insightful=1, Informative=2, Total=3
    Extra 'Informative' Modifier   0  
    Karma-Bonus Modifier   +1  

    Total Score:   5  
  • (Score: 0) by Anonymous Coward on Tuesday November 27 2018, @12:20PM (1 child)

    by Anonymous Coward on Tuesday November 27 2018, @12:20PM (#766892)

    "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

      by digitalaudiorock (688) on Tuesday November 27 2018, @09:53PM (#767058)

      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.