How to find number of the character reading right to left ?

[Anonymous]

I want to find the starting position of one text string within another text string, reading right to left.

Below is the value from which I want retrieve the color name (Silver).

Contoso Optical Wheel OEM PS/2 Mouse E60 Silver

I tried using Search function but unfortunatly it did'nt work for me because it starts reading the text from leff to right.

RIGHT([Product Name],SEARCH(" ",[Product Name],1,BLANK()))

Please suggest a function or approch using which I can get the desired result.

[TomasAndersson]

Hi!

You can combine LEN() and SEARCH() to mimic right-to-left reading.

I haven't double-checked this code, but something like below should work:

RIGHT([Product Name],LEN([Product Name])-(SEARCH(" ",[Product Name],1,BLANK()))

Let me know if it doesn't and I'll take a closer look!

[Anonymous]

Hi @TomasAndersson Unfortunatly, It didn't work. 

[TomasAndersson · Accepted solution]

Sorry, I misread the question! It's a bit more tricky. I got the below to work. It depends on identifying the position of the last space in the string before using the first method again

Column =
VAR __NumberOfSpaces =
LEN('Table'[Product Name])-len(SUBSTITUTE('Table'[Product Name]," ","")) //Find how many spaces are in the string
return
RIGHT( //The same basic structure: RIGHT(), LEN() ans SEARCH()
    [Product Name],
    LEN([Product Name]) - 
    SEARCH("@", //Search for "@" (any character that you
                //choose that do not exist in the strings beforehand)
        SUBSTITUTE('Table'[Product Name]," ","@",__NumberOfSpaces) 
            //Use SUBSTITUTE() to replace space with "@" only on 
            //the last (__NumberOfSpaces) occurence of space.
        ,1,
        BLANK()
    )
)

[Anonymous]

@TomasAndersson Thank you so much ! It worked 

[TomasAndersson]

There also seems to be a working method using PowerQuery: 
Solved: Search from right to left - Microsoft Power BI Community