Hi,
I did a small correction in the formula so that it's now..
=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($A$
1:$A$101),1)),0),999)
First
ROW($A$1:$A$101)
it will make a array of
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;............;99}
Second
MID(A2,ROW($A$1:$A$101),1)
It will breakup each char from that string
{"S";"U";"R";"U";"B";" ";"P";"A";"L";" ";"C";"A";"P";" ";"I";"N";"E";"C";"A";"T";" ";"3";",";"5";"*";"5";"0";"";"";"";""....................................;""}
As we have to find first numeric so that we will multiply it with 1
{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;3;#VALUE!;5;#VALUE!;5;0.......................;#VALUE!;#VALUE!}
& then check that with
ISNUMBER
So it will look as ....
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FAL................................................LSE;FALSE;FALSE;FALSE;FALSE}
Now, Match(true, upper string,0)
So that we will get
22 as first match position of true.
Lastly
mid(A2,22,999) will extract the string like as
3,5*50
That formula is an array fn where Ctrl+Shift+Enter is required but
=MID(A2,MATCH(TRUE,INDEX(ISNUMBER(1*MID(A2,ROW($A$1:$A$101),1)),,),0),999)
this is a non-array where CSE is not required.
Hope!! you achieved your goal.
If yes then two homework for you .
- Why ROW($A$1:$A$101) used why not ROW($A$1:$A$50) or ROW($A$1:$A$500)
- Why 999 used why not 99,299 or something like that.