cutting words with Len and Left and vlookup

  • 1) In column L, I would like a formula that will change the ticker list in column A from MVIS US EQUITY to MVIS-US or BIGG CN Equity to BIGG-CN. Or GBI US EQUITY to GBI-US .

    I tried this formula: =LEFT(A28,LEN(A28)-FIND("US",A28)) But dont know how to adjust it to US or CN but the other issue was that sometimes it brought back the letter E when the ticker had 3 characters instead of four.

    2) Ultimately I would like to compare the list of tickers in K and L and see which tickers did not show up in K but did in L

    Files

    • compare.xlsx

      (180.75 kB, downloaded 6 times, last: )
  • I tried this formula in another cell on your formula to add the hyphen; however, ther ewas a space before the hyphen and it iddnt work when there were 3 letters in ticker:

    =IF(LEN(M2)=7,LEFT(M2,5)&"-"&RIGHT(M2,2),IF(LEN(M2)=7,LEFT(M2,10)&"-"&RIGHT(M2,2),""))

  • If you modify the formula to substitute the first space " " with "-" then you will get

    MVIS-US
    EHTH-US


    =SUBSTITUTE(LEFT(A5,FIND(" ",A5)+2)," ","-",1)

    and copy down:)