# Nesting functions with LEN, RIGHT, LEFT, Filter

1. ## Nesting functions with LEN, RIGHT, LEFT, Filter

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.

2. ## Re: Nesting functions with LEN, RIGHT, LEFT

Maybe this:

=LET(a,A5:A1743,b,SUBSTITUTE(LEFT(a,FIND(" ",a)+2)," ","-"),FILTER(b,ISERROR(SEARCH(b,K2:K362)),""))

It seems that you have O365 (you used FILTER). So, please amend your profile to show O365 as your Excel version... NOW.

3. ## Re: Nesting functions with LEN, RIGHT, LEFT

If not post a SMALL sample 10-20 rows carefully chosen.... to facilitate manual verification. It's not easy to check 1700+ rows.

4. ## Re: Nesting functions with LEN, RIGHT, LEFT

Thank you so much. How do I also just change the bloomberg tickers to match factset, so from Column A to be able to have a dash between the ticker of any length and US and CN and drop the word equiity.

5. ## Re: Nesting functions with LEN, RIGHT, LEFT

Thank you, I have amended my original thread.

6. ## Re: Nesting functions with LEN, RIGHT, LEFT

Originally Posted by dianaCatz
Thank you so much. How do I also just change the bloomberg tickers to match factset, so from Column A to be able to have a dash between the ticker of any length and US and CN and drop the word equiity.
I don't understand the words.... show me. On a sheet.

7. ## Re: Nesting functions with LEN, RIGHT, LEFT

You haven't changed you profile yet. Please do so NOW.

8. ## Re: Nesting functions with LEN, RIGHT, LEFT

Formula to return column N. ALL the bloomberg tickers reformatted.

9. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

I will look at theis as soon as you change your profile. Please do not ignore moderator's requests.

NO HELP to be offered until the profile is updated to O365

10. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

In case you don't know how to...

11. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

Updated now, I honestly didn't know what you were referring to. Thanks for sending the picture.

12. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

I have it done and will post when I get back from dinner.

13. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

OK. It was easy... just pick out one bit from the formula in Post 2... but I couldn't get the right bits selected from my phone...

=LET(a,A5:A17,SUBSTITUTE(LEFT(a,FIND(" ",a)+2)," ","-"))

14. ## Re: Nesting functions with LEN, RIGHT, LEFT, Filter

We only allow one thread per topic here (see the rules)

https://www.excelforum.com/forum-rul...rum-rules.html

It avoids timewasting if different helpers are looking at the same thread in different places. The correct protocol, if a formula fails to work is to go BACK to the thread, mark it as uNSOLVED and continue from there. There's a better chance that the original helper, who's familiar with your problem, will pick it up, too.

So, I see the problem with your dataset and I have fixed it.

=LET(a,A5:A1743,b,SUBSTITUTE(LEFT(a,FIND(" ",a)+2)," ","-"),FILTER(b,ISNA(MATCH(b,K2:K1834,0)),""))

The verification cells in columns O & P show that it's OK (I hope).

If it's still wonky... reply here. If it's OK, then you're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1