VLOOKUP between company names and stock tickers

[gilesgerman]

I have a number of company names in an excel spreadsheet like this.

Aceto CorpAZJ:BER

Petra Diamonds LtdPDL:LSE

OutokumpuOUT1V:HEX <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

I want to do a VLOOKUP to the following list

AZJ
PDL
OUT1V

Can someone provide me some code to do this?

Giles

 

[gilesgerman]

If the code is always 3 or 4 characters and preceded by a space, this should work:

=TRIM(RIGHT(LEFT(B11,FIND(":",B11)-1),4))

Where B11 is the cell with the full company name string

This works well. There are the odd instances where it doesn't work but it is good enough for what I am trying to do.

Finally I am also looking to create another column where I can leave the stock ticker plus the exchange so Yahoo finance will recognise it as follows: -

Adamis Pharmaceuticals Corp CY3B:FRA -----> CY3B.F <tbody> </tbody>

Ab Dynamics PLC ABDP:LSE ------> ABDP.L

<tbody>
</tbody>

Adler Real Estate AG ADLX:GER-----> ADL.DE AB Science SA AB:PAR----> AB.PA

<tbody>
</tbody>

 

[Aladin Akyurek]

So you don`t have a preset ticker list?

 

[JumpingCrab]

This works well. There are the odd instances where it doesn't work but it is good enough for what I am trying to do.

Finally I am also looking to create another column where I can leave the stock ticker plus the exchange so Yahoo finance will recognise it as follows: -

Adamis Pharmaceuticals Corp CY3B:FRA -----> CY3B.F <tbody> </tbody>

Ab Dynamics PLC ABDP:LSE ------> ABDP.L

<tbody>
</tbody>

Adler Real Estate AG ADLX:GER-----> ADL.DE AB Science SA AB:PAR----> AB.PA

<tbody>
</tbody>

What is the rule to change the exchange code to the short version shown in your example?
The first two examples take just the first letter, but the third one changes the code altogether and the fourth example leaves two letters.
If that is indeed what needs to happen, you may want to set up a separate lookup table.

 

[gilesgerman]

Yes I tried to give a range of examples. I will know the from to so I think what I need is a find and replace within the string for all the different examples for the end part tenprevious cide for the front end.

find GER replace DE find PAR replace PA etc.

 

[Aladin Akyurek]

Yes I tried to give a range of examples. I will know the from to so I think what I need is a find and replace within the string for all the different examples for the end part tenprevious cide for the front end.

find GER replace DE find PAR replace PA etc.

Can't you confirm whether you have a list of stickers or not?

 

[gilesgerman]

All I have is this information Adler Real Estate AG ADLX:GER for each company in one cell

 

[JumpingCrab]

I guess you could use this to find the front part:

=TRIM(RIGHT(LEFT(B2,FIND(":",B2)-1),4))

and this to find the aft part:

=RIGHT(B2,LEN(B2)-FIND(":",B2)+1)

You can then concatenate the strings. I'm not sure how you would do the replacements automatically without a cross-reference list.