To get the numbers if they are in the beginning of the string we can use this:
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
as our base formula, This will find the end of the number and return that as the end of the MID() Function.
There is a lot going on here:
SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))
As this part iterates through the numbers it is replacing the last instance of each number with }}}
.
The third criterion of SUBSTITUTE is the instance. We find the number of instances with the LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))
. It iterates through the numbers and replaces each one at a time with nothing. It then finds the difference in length of the original string and the new one.
So in the case of A2 when it iterates to the 2
it finds 2 and the outer Substitute replaces the last one with }}}
. This is just a temprorary place holder.
The Aggregate function is a multi function function. The 14 tells the funtions we are using the Large()
function. The 6
tells the function to ignore errors. This is important in that many of the iteration will not find anything and return an error.
With the 1
at the end it tells the function we want the highest return from the Search function which searches for those temporary }}}
that are placed through iteration on the last instance of each number.
So the Aggregate returns the max number found. Which we pass to the length criterion in the Mid function.
So we now have found the number at the front of the string.
So we can multiply two of these together to get the desired output(Any math function will turn the returned string into a number):
=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))
![enter image description here](../../I/static/images/2aff41b126bffc7bfd0df7611c13a2a2da84f6c037e120223f1cb27f38149552.png)
One Caveat The Aggregate function was introduced in Excel 2010. It may not work with older versions.
If you have an older version you will need to use this longer formula:
=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))
It does roughly the same as the one above accept it must test for the errors first before finding the max.
The ideal solution, of course, is to not mix numbers and text in a cell that you want to use for arithmetic. Is splitting the first two columns out of the question? – fixer1234 – 2016-06-18T19:15:01.407
By splitting, you mean dividing the numbers and text? Well it is certainly an option (maybe the elegant one :)) another one could be using comments to store text info, but I still wonder if I could handle this programmatically. – jeff – 2016-06-18T19:55:07.493