Rant: Text Functions

[hrlngrv]

How many newsgroup/help forum questions would be much simpler to answer if Excel provided functions to do the following.

1. Search from the end to the beginning.
2. Reverse the characters in a string.
3. Select the n_th instance of a substring match (like SUBSTITUTE's 4th argument), either forward or backward.
4. Supported regular expressions.

Excel is a poor tool to use for text parsing and transformation, but the frequency of questions posted on these topics makes it clear many people use Excel to do this (because they have no alternatives other than batch files or VBScript, and fewer than 1% even know about these alternatives).

I have no expectation MSFT will ever address this. Pity!

 

[RoryA]

I agree, especially about RegEx. (though I suspect the number of people that understand Regex is lower than even the 1% who have heard of VBS).

 

[Jon von der Heyden]

Yep regex would be nice. Whilst specific functions for the above would be nice too, the same can still be achieved using formulae.

And besides, if MS cover all these wonderful things then these forums wouldn't make us appear half as clever.

 

[hrlngrv]

Yes, we can do lots with formulas, but there are already a lot of redundant functions. COUNTIF, SUMIF and AVERAGEIF could all be replaced by SUMPRODUCT formulas. Then there's my own favorites, the Bessel functions. In the NNTP Excel newsgroups of yore, I recall only one question about them, and that was whether they accepted noninteger order parameters (they don't in Excel). Even if a tiny fraction of Excel users would find end-to-beginning searching useful, that fraction would be several orders of magnitude greater than the fraction who use the Bessel functions.

 

[Jon von der Heyden]

COUNTIF(S), SUMIF(S) and AVERAGEIF(S) cannot be replaced by SUMPRODUCT. SUMPRODUCT is a completely different beast.

Sure you can achieve the same results with SUMPRODUCT but the way they are processed is different. Generally COUNTIF(S), SUMIF(S) and AVERAGEIF(S) will be much quicker, but will not update where referencing closed workbooks. SUMPRODUCT is an array formula function, and although fantastic (and incredibly versatile) it will generally be slower (my experience suggests). You can feed a whole column/row (or even just an unnecessarily oversized range) to COUNTIF(S), AVERAGEIF(S) and SUMIF(S) and get away with it, but do the same for SUMPRODUCT and you're slowing your model right down. In fact in 2003 and earlier one cannot pass whole columns/rows to array formula / functions.

 

[hrlngrv]

You're the one who wrote: 'the same can still be achieved using formulae.' So true of SUMPRODUCT as a replacement for the *IF functions. I agree SUMPRODUCT is much slower, just as the typical crude hacks needed to search for n_th instances or back-to-front searching are much slower than 1st instance front-to-back searching.

BTW, Excel 2003 and prior only choke on entire columns as arrays. Excel 2003 and prior more than happily accept entire rows as arrays. Not unlikely this is due to having used unsigned 16-bit integers for array dimensions, which would explain why 65,535 row ranges are OK as arrays but 65,536 row ranges aren't.

 

[Firefly2012]

I'd love a native Excel function to permit use of RegEx.

Just thought I'd add my tuppence worth

 

[Scott Huish]

I wish you could concatenate the text results of an array formula with a native function.

 

[Jon von der Heyden]

You're the one who wrote: 'the same can still be achieved using formulae.' So true of SUMPRODUCT as a replacement for the *IF functions. I agree SUMPRODUCT is much slower, just as the typical crude hacks needed to search for n_th instances or back-to-front searching are much slower than 1st instance front-to-back searching.

Ok Harlan, I get your point. It's just that I felt 'redundant' a tad harsh.
I do agree with your original suggestion that the functions library lacks some basic text parsing and manipulation functions, so really we are on the same page. And ditto to HOTPEPPERS comment above! That would really come in handy (at least for a number of forum questions).

 

[hrlngrv]

Yes, worksheet equivalents of VBA's Split and Join functions.