Map text to numeric rankings

    Hi,


    I have a set of ratings by Moody's and S&P, but I will need to rank them in order to run some filters/pivots. I can't do a simple VLOOKUP as the raw data is unclean.


    See Sheet 1: Is there a way to modify the raw data in columns B and D (either using formula or coding), such that it can translate into the clean data in columns C and E, eventually arriving at the final ranking in column F?


    Thank you!
    Clari

    Re: Map text to numeric rankings


    Is that a sample, or a complete definitive listing?
    This formula "=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4,"u",""),"(P)",""),"e",""),IFERROR(SEARCH(" ",B4)-1,999))" Translates a raw in b4 to a clean.

    Re: Map text to numeric rankings


    this is a sample and there are more potential variations for every rating :?. Perhaps it should reference to the clean column to check for the closest match? I'm not sure if that is possible or will work..?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login