+ Reply to Thread
Results 1 to 14 of 14

Nesting functions with LEN, RIGHT, LEFT, Filter

  1. #1
    Forum Contributor
    Join Date
    01-16-2019
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    111

    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.
    Attached Files Attached Files
    Last edited by dianaCatz; 01-15-2022 at 02:42 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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. #4
    Forum Contributor
    Join Date
    01-16-2019
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    111

    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. #5
    Forum Contributor
    Join Date
    01-16-2019
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    111

    Re: Nesting functions with LEN, RIGHT, LEFT

    Thank you, I have amended my original thread.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Nesting functions with LEN, RIGHT, LEFT

    Quote Originally Posted by dianaCatz View Post
    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. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Nesting functions with LEN, RIGHT, LEFT

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

  8. #8
    Forum Contributor
    Join Date
    01-16-2019
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    111

    Re: Nesting functions with LEN, RIGHT, LEFT

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

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Nesting functions with LEN, RIGHT, LEFT, Filter

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

  11. #11
    Forum Contributor
    Join Date
    01-16-2019
    Location
    NY
    MS-Off Ver
    Office 365
    Posts
    111

    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. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Nesting functions with LEN, RIGHT, LEFT, Filter

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

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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)," ","-"))
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 01-15-2022 at 02:54 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help nesting SUMIF and LEFT or SUMPRODUCT LEFT & AND
    By JMS1206 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-14-2022, 03:11 PM
  2. Nesting Vlookup Functions with IF Functions and possibly more
    By Anitarizzo in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-27-2014, 12:50 AM
  3. [SOLVED] nesting len , right and left functions.
    By hopalong in forum Excel General
    Replies: 2
    Last Post: 06-04-2012, 07:47 AM
  4. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  6. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-10-2005, 06:05 PM

Bookmarks

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