• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Explore the text formula

sachar

Member
Dear all,

Please, can explore the text in column "B" sample file attached?
 

Attachments

  • string extract_formula.xlsx
    11.1 KB · Views: 0
Dear Deepak,

My requirement is to know how to work/logic in step by step?

=MID(A3,MATCH(TRUE,ISNUMBER(1*MID(A3,ROW($A$2:$A$101),1)),0),999)
 
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 .:DD:DD
  1. Why ROW($A$1:$A$101) used why not ROW($A$1:$A$50) or ROW($A$1:$A$500)
  2. Why 999 used why not 99,299 or something like that.

 
Back
Top