Excel String Handling

lr1277

Hi. I am looking for help with the following as I have been going around in circles. In column A I might have something like the following:

Astrazeneca (AZN)

Mustang Energy (MUST)

In column B, I would like to see just the stock ticker of AZN or MUST, respectively.

I have been trying mid, left, right, find, if.

How would you extract the stock ticker from the above strings?

Many thanks in advance.

KingL2

=FIND("(",A1) will find the position of the first (
add one to that to find the first letter of the tracker string, (call that y, for now)
assuming that the string is always the first three letters, then =(Mid(a1,y,3) returns the string
to combine all that in one formula
=MID(A1,((FIND("(",A1))+1),3)

KingL2

sorry, just re read it
if the string in the brackets isn't always three characters
use =find again to find the location of the )
subtract the position of the ( from the position of the )  to get the length of what's inside the brackets (actually, subtract another 1 from it)
then you're replacing the final 3 in the =MID statement in the post above with the actual length of the string.
or all combined:
=MID(A1,((FIND("(",A1))+1),(((FIND(")",A1))-(FIND("(",A1))))-1)

lr1277

KingL2 said:

sorry, just re read it
if the string in the brackets isn't always three characters
use =find again to find the location of the )
subtract the position of the ( from the position of the )  to get the length of what's inside the brackets (actually, subtract another 1 from it)
then you're replacing the final 3 in the =MID statement in the post above with the actual length of the string.
or all combined:
=MID(A1,((FIND("(",A1))+1),(((FIND(")",A1))-(FIND("(",A1))))-1)

Thank you so much. That worked a treat. I started but then got confused on finding whether the stock code was 3 or 4 characters.

KingL2

yeah, if it's not clear from the above, the
Length is [position of closing bracket]-[position of opening bracket]-1
HTH

lr1277

Ahh, thank you. I understand.