There are several different ticker symbol lengths in my spreadsheet from bloomberg in column A. I need to get all of these to the Factset format which is ticker and then a dash and eithe rUS or CN. I tried to do an If statement with LEN, LEFT, and Right but I can only seem to fit two different permutations in the formula. I tried the OR statement but could not get it to work.
The 7 different permutations are:
1) four letters + US or CN so that would look like: EHTH US
2) three letters + US or CN looking like: LEE US
3) two letters + US or CN looking like PD US
4) can have a slash so CSW/A CN or RET/A CN or TVA/B CN or TPX/A CN or VWE/U CN or SPS/A CN or ADW/A CN or CJR/B CN
5) five letters like AMSWA US
6) Can look like this: ACRG/A/U CN Equity
7) one letter: F CN
First I did a formula in L: =LEFT(A6,FIND(" ",A6)+2)
Then to make it look like column K the factset tickers I did this formula which works for only two permutations:
The formula I tried: =IF(LEN(L7)=7,LEFT(L7,5)&"-"&RIGHT(L7,2),IF(LEN(L7)=6,LEFT(L7,4)&"-"&RIGHT(L7,2),""))
In column N i used this formula to get rid of the space before the hyphen:
=SUBSTITUTE(M2," ","")
Perhaps there is a more elegant way to do this? And most important, how to get all 7 permutations in the formula.
In column O I am trying to bring back all the ticker sin Bloomberg (column N) not in factset (column K)
Please refer to attachment. Sample set has less rows to work with to test the different permutations only.
Bookmarks