Add prefix

[Peterino]

I am trying to automate a list of stock tickers to include the stock exchange prefix. For example General Electric ticker is (GE) and it trades on the New York Stock Exchange (NYSE). If I list GE in one cell I want the next cell to then show NYSE:GE. There are different prefixes for different stock exchanges. I have a legend and a group of stocks showing on the attached sheet with instructions.

I would appreciate any assistance on this.

Thanks.

Peter

[Special-K]

There doesnt seem to be a standard for your table.
In some cases ticker can be replaced by the Stock Ticker, in other cases -P is removed, but not always.
There's a lack of consistency.

This gets you close but doesn't match your output exactly.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(B10,B$3:C$7,2,0),"ticker",C10),".","-"),"-P","")

[Peterino]

Yes, I realize I had incorrect information in the legend which I have adjusted. You have done exactly what I was hoping for. There is just one adjustment needed - listed on the sheet.

Peter

[mohan.r1980]

Hi Peterino

Try

Please Login or Register  to view this content.

[Peterino]

That did it! Thank you for your help on the last bit.
Peter

[AliGW]

This has nothing to do with VBA as far as I can see, so I'm moving the thread to the correct section.

[mohan.r1980]

That did it! Thank you for your help on the last bit.
Peter

Your Welcome and thanks for the rep

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Peterino]

Is there a way to have Col E work without Col D? So, if C14 is RY.PR.C then E14 is TSE:RY-C. I could then delete Col D.

Much appreciated.

Peter

[AliGW]

Try this:

=LEFT(VLOOKUP(B11,$B$3:$C$9,2,0),FIND(":",VLOOKUP(B11,$B$3:$C$9,2,0)))&IFERROR(LEFT(C11,FIND(".",C11)-1)&"-"&RIGHT(C11,1),C11)

[AliGW]

Can we mark this as solved yet?

[Peterino]

That did it. I am not sure how many times I should be adding additional items to my initial request but...

Sometimes there are lines without data so it would be good to have the formula carry down but not show anything on that blank line. I tried adding an =IF but not sure how to do that here. Sheet attached.

t RY TSE:RY
tp RY.PR.C TSE:RY-C
#N/A
#N/A
t EXE TSE:EXE

[AliGW]

That's easy to resolve:

=IF(C11="","",LEFT(VLOOKUP(B11,$B$3:$C$9,2,0),FIND(":",VLOOKUP(B11,$B$3:$C$9,2,0)))&IFERROR(LEFT(C11,FIND(".",C11)-1)&"-"&RIGHT(C11,1),C11))

In terms of adding things to threads: what we would prefer is for you to state all the variables at the outset, because, as you have found, extra details can significantly change the approach needed.

Now, if we are there, would you please mark the thread as solved? Thanks.

[Peterino]

That got it. Many thanks to all three for your help. I will make sure I have all variables up front!

Peter

[AliGW]

You’re welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.