@amit dani You're welcome!
@Somendra Misra Thanks! Glad you like it!
@Asheesh I'll try!
Let's take an abbreviated example to make the explanation easier to follow, i.e. the following string in A2:
Payment : 111531002246 Ref 00724101400019
This part:
MID(A2,ROW(INDIRECT("1:"&LEN(A2)-13)),14)
as you probably know, creates an array of all substrings of length 14 (I'll explain why 14, not 12, further down) from the string in A2, beginning with the first, second, third, etc. characters in that string, i.e.:
{"Payment : 1115";"ayment : 11153";"yment : 111531";"ment : 1115310";"ent : 11153100";"nt : 111531002";"t : 1115310022";" : 11153100224";": 111531002246";" 111531002246 ";"111531002246 R";"11531002246 Re";"1531002246 Ref";"531002246 Ref ";"31002246 Ref 0";"1002246 Ref 00";"002246 Ref 007";"02246 Ref 0072";"2246 Ref 00724";"246 Ref 007241";"46 Ref 0072410";"6 Ref 00724101";" Ref 007241014";"Ref 0072410140";"ef 00724101400";"f 007241014000";" 0072410140001";"00724101400019"}
The second MID then extracts, for each of the strings in the above array, each of the 14 individual characters within that string, so that:
MID(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-13)),14),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},1)
gives:
{"P","a","y","m","e","n","t"," ",":"," ","1","1","1","5";"a","y","m","e","n","t"," ",":"," ","1","1","1","5","3";"y","m","e","n","t"," ",":"," ","1","1","1","5","3","1";"m","e","n","t"," ",":"," ","1","1","1","5","3","1","0";"e","n","t"," ",":"," ","1","1","1","5","3","1","0","0";"n","t"," ",":"," ","1","1","1","5","3","1","0","0","2";"t"," ",":"," ","1","1","1","5","3","1","0","0","2","2";" ",":"," ","1","1","1","5","3","1","0","0","2","2","4";":"," ","1","1","1","5","3","1","0","0","2","2","4","6";" ","1","1","1","5","3","1","0","0","2","2","4","6"," ";"1","1","1","5","3","1","0","0","2","2","4","6"," ","R";"1","1","5","3","1","0","0","2","2","4","6"," ","R","e";"1","5","3","1","0","0","2","2","4","6"," ","R","e","f";"5","3","1","0","0","2","2","4","6"," ","R","e","f"," ";"3","1","0","0","2","2","4","6"," ","R","e","f"," ","0";"1","0","0","2","2","4","6"," ","R","e","f"," ","0","0";"0","0","2","2","4","6"," ","R","e","f"," ","0","0","7";"0","2","2","4","6"," ","R","e","f"," ","0","0","7","2";"2","2","4","6"," ","R","e","f"," ","0","0","7","2","4";"2","4","6"," ","R","e","f"," ","0","0","7","2","4","1";"4","6"," ","R","e","f"," ","0","0","7","2","4","1","0";"6"," ","R","e","f"," ","0","0","7","2","4","1","0","1";" ","R","e","f"," ","0","0","7","2","4","1","0","1","4";"R","e","f"," ","0","0","7","2","4","1","0","1","4","0";"e","f"," ","0","0","7","2","4","1","0","1","4","0","0";"f"," ","0","0","7","2","4","1","0","1","4","0","0","0";" ","0","0","7","2","4","1","0","1","4","0","0","0","1";"0","0","7","2","4","1","0","1","4","0","0","0","1","9"}
etc.
We then add zero to each of these, which will convert to numericals where appropriate, and then pass to ISNUMBER, giving:
{FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
Since we are interested in a string of 12 numbers, we need to ensure that we actually consider strings of 14 characters in length, in which the first and last characters are not numericals, and the rest are. Were we to only consider strings of length 12, we could not guarantee that any resulting strings of numericals did not in fact form part of a larger string of numericals.
One way to do this is to subtract the array constant:
{1,0,0,0,0,0,0,0,0,0,0,0,0,1}
from the above array, which, taking advantage of the fact that:
FALSE - 1 = -1
and
TRUE - 0 = 1
will ensure that, after taking the absolute of these resulting values,
only a string which consists of non-numericals in the first and last position and numericals everywhere else will result in an array of 14 1s, i.e.:
{1,1,1,1,1,1,1,1,1,1,1,1,1,1}
After these operations, i.e. performing:
ABS(ISNUMBER(0+MID(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-13)),14),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},1))-{1,0,0,0,0,0,0,0,0,0,0,0,0,1})
we have:
{1,0,0,0,0,0,0,0,0,0,1,1,1,0;1,0,0,0,0,0,0,0,0,1,1,1,1,0;1,0,0,0,0,0,0,0,1,1,1,1,1,0;1,0,0,0,0,0,0,1,1,1,1,1,1,0;1,0,0,0,0,0,1,1,1,1,1,1,1,0;1,0,0,0,0,1,1,1,1,1,1,1,1,0;1,0,0,0,1,1,1,1,1,1,1,1,1,0;1,0,0,1,1,1,1,1,1,1,1,1,1,0;1,0,1,1,1,1,1,1,1,1,1,1,1,0;1,1,1,1,1,1,1,1,1,1,1,1,1,1;0,1,1,1,1,1,1,1,1,1,1,1,0,1;0,1,1,1,1,1,1,1,1,1,1,0,0,1;0,1,1,1,1,1,1,1,1,1,0,0,0,1;0,1,1,1,1,1,1,1,1,0,0,0,0,1;0,1,1,1,1,1,1,1,0,0,0,0,0,0;0,1,1,1,1,1,1,0,0,0,0,0,1,0;0,1,1,1,1,1,0,0,0,0,0,1,1,0;0,1,1,1,1,0,0,0,0,0,1,1,1,0;0,1,1,1,0,0,0,0,0,1,1,1,1,0;0,1,1,0,0,0,0,0,1,1,1,1,1,0;0,1,0,0,0,0,0,1,1,1,1,1,1,0;0,0,0,0,0,0,1,1,1,1,1,1,1,0;1,0,0,0,0,1,1,1,1,1,1,1,1,0;1,0,0,0,1,1,1,1,1,1,1,1,1,0;1,0,0,1,1,1,1,1,1,1,1,1,1,0;1,0,1,1,1,1,1,1,1,1,1,1,1,0;1,1,1,1,1,1,1,1,1,1,1,1,1,0;0,1,1,1,1,1,1,1,1,1,1,1,1,0}
In order to locate the position of the 14 1s (which I've highlighted) we can use MMULT, so that:
MMULT(ABS(ISNUMBER(0+MID(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-13)),14),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},1))-{1,0,0,0,0,0,0,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1;1;1;1;1;1;1})
gives:
{4;5;6;7;8;9;10;11;12;14;12;11;10;9;7;7;7;7;7;7;7;7;9;10;11;12;13;12}
and so then we can simply locate the 14 in this array.
I haven't got time or space here to go into a technical explanation as to how matrix multiplication functions (which is precisely what MMULT does), though I've been meaning to write an article on this function over at my website for some time, so I'll try to do that over the next few weeks.
Hope that at least helps a bit!
Regards