A slightly light-hearted post this, as you may have guessed from the title, though readers might find it moderately interesting, and hopefully some may even contribute to my rather esoteric collection of pandigital numbers in Excel.

This began as a result of seeing (I don’t recall where now, unfortunately) an alternative version of the tried-and-tested construction for returning a number from the end of a mixed string. For example, given the following in A1:

**ABCDEF123456**

the now-ubiquitous solution:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

will correctly return 123456.

For those who don’t already know, the concatenation of A1 with the string “0123456789” is necessary so as to prevent MIN from returning an error. Since, if A1 did not contain all of the digits from 0-9, then at least one of the returns from the FIND function would be an error. And since MIN is not capable of ignoring error values the whole construction would therefore also result in an error.

By first catenating “0123456789” to the end of the string in A1, we both ensure that A1 now contains *at least one* of each of the digits from 0-9 (so that all 10 returns from FIND will be non-error values), whilst ensuring, by virtue of having placed this string at the *end* of A1, that we don’t affect the required output for the MIN function.

Anyway, when I first stumbled upon this variation:

=0+MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&7^18)),LEN(A1))

I was at first bemused. Then, when it dawned on me what was happening here, I myself started to prefer the use of 7^18 in this construction in place of the automatic “0123456789”.

It wasn’t long before I switched – thanks to shg at over at EF – to using 5^19 instead (a smaller pandigital number!). And, once I’d grown tired of that one, I thought that I may as well see how many other mathematical operations I could find which resulted in a “pandigital” number.

Of course, there are an infinite number of such operations, but the caveats were that the total number of characters within the operation be as short as possible and, of course, that it be reproduceable in Excel.

Here’s my current list:

My “check” formula in C2 is:

=SUMPRODUCT(0+ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},B2)))

The last two are certainly pushing the criterion of being as short as possible, though never having used WEIBULL in all the time I’ve been working in Excel, I thought that I could at least remedy that by using it here. Not that WEIBULL was designed with such constructions in mind, but hey! (I have yet to find a way to produce a pandigital number using BAHTTEXT, by the way. ðŸ™‚ )

Anyone wishing to add to my list, please do!

sorry no BAHTTEXT proposal, neither any that will be as short or reasonably acceptable within a formula, but just for the fun of it:

=9.9066^2

=2/19

=HARMEAN(PI(),32)

=FISHER(0.23)

=GAMMA.DIST(7,0.1,1,)

=BESSELI(1,5)

=DB(5,2,3,4,1)

Good stuff, Cyrilbrd!

Didn’t realise 2/19 was also an option. I also like the DB(5,2,3,4,1) as there’s something satisfying about having the integers 1-5 like that. Just a shame they’re not in order as well!

And ok, you didn’t get a BAHHTEXT in – but FISHER has to be up near the top of the “least likely to ever use” list. Unless you’re a statistician, that is.

Thanks a lot. Will add these to my list!

Most welcome, had a Kurt() and a few convert() too but still baffling on that Thai text format that seems impervious to any extraction… Excellent blog, excellent reading material and at the academic level what you show is exactly what I am looking for.

Using the integers 1,2 and 3 with BAHTTEXT…

=3/LEN(BAHTTEXT(21))

“

Excellent blog, excellent reading material and at the academic level what you show is exactly what I am looking for.“That’s very kind of you, and I’m glad that at least someone appreciates what I’m trying to do here.

“Academic” is probably quite a pertinent choice of word, and something which I’ve been conscious of recently, in the sense that part of me has wondered whether my ramblings here are of very little practical importance beyond the theoretical.

Still, practical or not, that’s what interests and fascinates me about Excel. I can’t just memorize formula constructions without trying to delve further into the workings behind those constructions, and seeing what else may be possible using worksheet formulas alone (a lot more than people realise, I honestly believe).

Thanks again.

“

Using the integers 1,2 and 3 with BAHTTEXTâ€¦”=3/LEN(BAHTTEXT(21))

ðŸ™‚ ðŸ™‚

I think based on my modest experience that there are two convergent currents, one is aiming to provide the accurate, efficient and robust solution to a problem, while the other allows itself to think out of the box and try to fit a solution within a forma rather than a vba per example…Whenever I post (and ask your opinion) it is merely to see what others may come up with. Taking a step backward is important in my trade, observing and trying not to conform too much brings new ideas and oportunities. Please do continue your ‘rambling’…As far as I am concerned those are thoughtfully elaborated solutions that anyone interested in Excel should at least try to understand.

Excuse any typos, written from my phone.

A commendable work philosophy. Indeed, a commendable

lifephilosophy.And refreshing it is to hear an opinion which differs from the near-ubiquitous one that touts practicality, efficiency and “result-getting” as the sole measures against which a system’s success should be judged.

Here’s to non-conformism and to observation!

Cheers

I kind of like 3^45 simply because of the number progression (if you use =3^35 in a worksheet cell, you will need to format the number to 13 decimal places in order to see all ten digits).