Extracting text from a cell based on a rule

[ks1987]

Hi,

I have text in a cell, a portion of which I want to extract, the form of the text is as follows:

ABCD > EFGH (1234)

From this I need to extract EFGH, i.e. the text after after > and before (

Please note that the above is a generic example and I have a lot of cells with different values for ABCD, EFGH and 1234.

Looking forward to getting some answers on this.

Thanks

 

[VoG]

Try

=MID(A1,FIND(">",A1)+2,FIND("(",A1)-FIND(">",A1)-3)

 

[ks1987]

Perfecto!! Thankus Muchus!!

 

[ks1987]

I am amazed by the elegance of your formula. I am interested in how you approached this problem and how you found a solution to it. Would you care to outline the same please?

Thanks

 

[VoG]

MID(cell,S,L) extracts the text starting at S and with length L.

FIND(">",A1)+2 finds the location of > and adds 2 to it so we start at the right place.

FIND("(",A1)-FIND(">",A1)-3 finds the length between > and (, subtracting 3 to account for spaces.

 

[ks1987]

That is very well thought out, thanks again!!

 

[markmzz]

I know that problem is solved, but I have another way:

=TRIM(MID(REPLACE(A1,FIND("(",A1),999,""),FIND(">",A1)+1,999))

Markmzz

 

[ks1987]

This one works as well!!