hash function for large strings

[Harlan Grove]

this is what the final part of the string gets converted into:

[Business_Type(¡¦£,¡¦_Equip¢, ¡¦_RE¢,¡§£, ¡§_Equip¢, ]

....

OK, I screwed up. Sorry.

This might shorten the OP's records enough, or it might not. Also, if there
just happen to be more than 128 such common phrases, you'd need to alter
your approach. I still believe a macro approach would be better than a udf
approach, and a macro approach could prompt for selection of a different
range containing a table of common susbtrings rather than hard coding them
into a udf.

 

[Harlan Grove]

Pete_UK wrote...

Well, you could wait for Excel 12 later in the year, but if you want to

....

Maybe you could wait for Excel 13 or 14, but this particular problem
won't be fixed in Excel 12. David Gainer confirmed in the Excel 12 blog
that VLOOKUP will remain limited to searching up to but no more than
255 chars in Excel 12.

FWIW, Lotus 123 97 Edition (almost 10 years old) handles lookup values

255 characters, as does the Windows port of Gnumeric 1.6.0. I'd be surprised if Excel wasn't the only arguably high-end spreadsheet that can't handle such strings. Microsoft has a very bad tendency to leave matters of actual functionality unchanged version to version over decades but spend thousands of developers' hours on interface and other eyewash issues.

Excel 12 is Microsoft's latest attempt to prove their assertion that
they're years ahead of the competition. Then stuff like this comes
along an proves that when low-level backwaters of functionality are at
issue, it's Excel that's a decade or so behind the competition. And a
long as the lemmings dutifully upgrade, there'll be no incentive for
them to fix this.

 

[Guest]

Regarding the array solution that you proposed - I'm assuming that the large
string gets placed at the location of "x" in the following array formula:

=INDEX(B1:B6,MATCH(TRUE,(A1:A6=x),0))

Was that your intent? - when I tried that, I got a "formula too long" error.