Explanation Needed - Extracting Numbers from a String
Good day
I came across a formula that can extract numbers from a mixed string on ExtendOffice.
https://www.extendoffice.com/documen...om-string.html
=SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)
Although the formula works, I can't make heads or tails of how it works.
If anyone can shed a little light on this I would really appreciate it.
Thanks
That is quite an impressive formula. I would never have thought of that in a million years.
SUMPRODUCT acts as a way to iterate through ranges and compute the product of corresponding elements in the ranges. For example, if you use a formula like this:
Formula:
Please Login or Register to view this content.
the result is A1*B1 + A2*B2 + A3*B3.
In this case, the range is a series of rows, starting at row 1 and continuing to the row that is identified as the number of characters in the string in A5.
Formula:Please Login or Register to view this content.
The tricky part here is that we don't really care about rows, but we care about the row numbers of those rows--it's a clever way to force SUMPRODUCT to iterate through every character in A5. For each character, if it is a digit (ISNUMBER), it multiplies that digit times the power of 10 corresponding to its position, then sums all those products up. I haven't quite sorted out how it determines the power of 10 to use, but that is the principle in play here.
Attachment 657286
A lot of that makes sense but this was still my face while I was reading it.
Thanks for taking the time to respond.
@ DavidFC
It has always been my understanding that this formula was created by Harlan Grove several years ago.
This was like seeing an old friend again. So thanks for posting. Like you I (self taught) recall spending many happy hours taking this apart and putting it back together in various ways so I could learn array formulas. I nearly wore out the F9 key and Evaluate Formula (Fx) doing so. LOL
So keep "plugging away". The time spent in understanding this one is well worth the effort.
Dave
First, I would point out that the formula posted in Message #1 does more than the website that was linked to shows. That formula will return all digits in a cell; for example for this...
ABC1DE23FGH456
it will return this...
123456
As for the OP's search, if there is always a space after the number being searched for (as in the examples at the website you provided a link for), then this shorter normally-entered formula could be used...
Formula:Please Login or Register to view this content.
Last edited by Rick Rothstein; 01-10-2020 at 05:11 PM.
If there is not always going to be a space after the number, then this shorter array-entered** formula could be used...Originally Posted by Rick Rothstein
Formula:Please Login or Register to view this content.
** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks