# Rant: Text Functions



## hrlngrv (Feb 5, 2012)

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 (Feb 5, 2012)

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 (Feb 6, 2012)

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 (Feb 6, 2012)

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 (Feb 6, 2012)

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 (Feb 6, 2012)

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 (Feb 6, 2012)

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

Just thought I'd add my tuppence worth


----------



## Scott Huish (Feb 6, 2012)

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


----------



## Jon von der Heyden (Feb 6, 2012)

hrlngrv said:


> 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 (Feb 6, 2012)

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


----------



## T. Valko (Feb 6, 2012)

Excel sorely needs an EFFICIENT function or array of functions for dealing with uniques!

I'm sure that we could come up with dozens of useful functions that Excel is missing.

Why is there a "width" argument to the CELL function but there is no "height" argument?


----------



## Jon von der Heyden (Feb 7, 2012)

T. Valko said:


> Why is there a "width" argument to the CELL function but there is no "height" argument?



Well there are still the XLM functions that can achieve these sorts of things.  Anyone have any idea when these will actually be dropped?  I purposely avoid XLM functions but I do feel that it would be a mistake to drop them until such time as there are suitable alternatives for some of the many useful features...


----------



## hrlngrv (Feb 7, 2012)

CELL is nearly useless because it's frozen in the past, compatible with Lotus 123 Release 2.2 from 23 years ago. Lotus added a lot of useful items to @CELL/@CELLPOINTER and @INFO in 123 Release 3, but MSFT didn't touch Excel's CELL or INFO. They've become two of the most worthless functions in Excel even if they are used more often than the Bessel functions or BAHTTEXT.


----------



## hrlngrv (Feb 7, 2012)

Raising the question why MSFT hasn't made the information XLM functions like GET.CELL available in standard worksheets. And I hope MSFT doesn't drop XLM macro/udf support. You can still do things with XLM you can't do with VBA. See

http://fastexcel.wordpress.com/2012...ted-value-from-the-calling-cells/#comment-396


----------



## Jon von der Heyden (Feb 7, 2012)

And there are bits where the XLM method is more superior than the VBA equivalent.  Ok so I can only think of one  but I'm sure there are more.  The one I can think of is the PAGE.SETUP function.

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

So I know this is going away from formula, but my 2c on why I too don't look forward to the day that XLM is removed.


----------



## Michael M (Feb 7, 2012)

Hmm, a function in Excel that allows the user to create a Regex ....can you imagine how well microsoft could make that almost impossible to understand, let alone actually work.
I'm still having trouble with Espresso !!!
But I'd love to see it !!


----------



## RoryA (Feb 7, 2012)

The PAGE.SETUP problem is alleviated in 2010 to a large degree.


----------



## arkusM (Feb 7, 2012)

Jon von der Heyden said:


> And there are bits where the XLM method is more superior than the VBA equivalent. Ok so I can only think of one  but I'm sure there are more. The one I can think of is the PAGE.SETUP function.
> 
> http://www.mcgimpsey.com/excel/udfs/pagesetup.html
> 
> So I know this is going away from formula, but my 2c on why I too don't look forward to the day that XLM is removed.


 
This Post helps with the syntax in the above link's code


----------



## hrlngrv (Feb 7, 2012)

Excel's developers would only need to borrow the code from the VBScript Regular Expressions library. Heck, they could even start with Word's Find and Replace wildcards.


----------

