Expanded utility for TEXT()
Expand the application of TEXT() past numbers only. Where the number code currently goes one could instead enter a "mask" that could apply to alphanumeric characters, not just numbers.
For instance, one might have a data cell with contents "1313mockingbirdlane" and in another cell enter =TEXT(A1,"####[ ]Aaaaaaaax") and the output would be "1313 Mockingb". ( posit inserted items going inside [ ], the capital "A" meaning uppercase for that position, the lowercase "a" being lowercase for those positions, and the "x" meaning drop that position combining with the "" to mean truncate at this position. One could do many other things including marking positions for colors or changing to specified characters.
It could even simplify number strings that include extraneous (to the spreadsheet's maker!) characters. 4321-2314 could be made to display as 43212314 with a character drop at the known position or by specifying characters, perhaps at start of end of the mask string, to drop no matter where they occur. All the "-" characters in credit card numbers without worrying about where they occur in different numbering schemes. Or one might specify character codes or ranges of codes that are either kept, others being dropped, or dropped with anything else being kept. Don't like dealing with people entering their as ¢athy or $teve? Put character conversions into the mask string.
Or better, set it up so one could create (then copy-and-paste it forever) a table such things so dropping all characters with codes below 32, between 121 and 152, code 172, and over 185 might be given a shorthand code of "M2" and THAT would be entered into the mask string. Why not? MS would only have to write the code once and it works forever. Hard once for the coding pros, then easy forever for the daily user.
In any case, an extra utility of this would be that one could often reduce the muddled input data one gets into a much more limited array of unfortunate extraneous material making the task of then dealing with the unexpected much easier/simpler. And if one had a Masking Table, one could see a new agravation and just add it to the table, then see the happy result immediately, and permanently.
All without any potentially problematic physical changes to the source data. And without creating, using, and maintaining (or recreating each time) interim data sets in which physical changes are made to cleanse the data. Source stays intact, no work is done to it nor needs maintenance as new data is brought in, and new choices in the resultant data set simply need a table entry, not labor to clean the existing data of the old choice and to apply the new choice.
Kenneth Barber commented
I think that you are crossing into regular expression territory.
Extending the TEXT function might actually be better than having regular expression formulas like what everyone seems to be suggesting.