Att: RagDyeR

[Hankjam]

Hello RD

You were kind enough to help me out on a string finding exercise.

The example was something like "Plate Plan for Daughterboard 286338
(05_jul08_0036) for XXXX(h) at 100.0 ATP Concentration"

and you came up with:

=MID(SUBSTITUTE(A4,"for","^^",2),FIND("^^",SUBSTITUTE(A4,"for","^^",2))+3,
FIND("(h)",A4)-FIND("^^",SUBSTITUTE(A4,"for","^^",2)))

which in nearly all worked a complete treat.

I have there kinases which have a ", activated" after the
kinase_name(h) and the string only finds the first part.

"Plate Plan for Daughterboard 286338 (05_jul08_0036) for XXXX(h),
activated at 100.0 ATP Concentration"

Two questions:

Can you explain to me how the above works?

and

can it be adapted for a kinase name with a space in it?

Many thanks for the time you've already spent on this.

Yours

Aj

 

[Ragdyer]

I see only one comma when "activated" is present.

Does this work for you:

=TRIM(MID(SUBSTITUTE(A1,"for","^^",2),FIND("^^",SUBSTITUTE(A1,"for","^^",2))
+3,
FIND("(h)",A1)-FIND("^^",SUBSTITUTE(A1,"for","^^",2)))&IF(ISNUMBER(FIND(",",
A1))," activated",""))

 

[Hankjam]

I see only one comma when "activated" is present.

Does this work for you:

=TRIM(MID(SUBSTITUTE(A1,"for","^^",2),FIND("^^",SUBSTITUTE(A1,"for","^^",2))
+3,
FIND("(h)",A1)-FIND("^^",SUBSTITUTE(A1,"for","^^",2)))&IF(ISNUMBER(FIND(",",
A1))," activated",""))

Hi

It surely did.

I've figured out how most of this works.

Could you explain the use of ISNUMBER?

Many thanks again

Aj

 

[Ragdyer]

The Find() function returns a number for the location of the character(s)
we're looking for.
In this case, it's the comma.
If that character is not found, Find() returns an error (#VALUE!).

Isnumber() evaluates the return of the Find() function ...
TRUE if it's a number (character found),
FALSE if it's an error (character *not* found).

SO, the return of Isnumber() designates which argument of that final IF()
formula will fire ...
the value-if-true, the text ", activated" ...
or, the value-if-false, a blank ( "" ).

Finally, try out this shorter version of the parsing formula.
It should do the same job as the original:

=MID(A1,SEARCH(") for",A1)+6,(FIND("(h)",A1)+2)-(SEARCH(") for",A1)+5))
&IF(ISNUMBER(FIND(",",A1)),", activated","")