3

I have data like this:

  • lm-sample prod
  • lm sample prod
  • lm-exit nonprod-shared
  • lm- value dev

I want to extract just the last value after the first space from right. So in this example:

  • prod
  • prod
  • nonprod-shared
  • dev

I tried:

Env = 
Var FirstSpace = FIND(" ", 'AWS Reservations'[Account])
Return RIGHT('AWS Reservations'[Account],FirstSpace - 1)

But that is giving me odd results. Would appreciate some help on solving this. Thanks.

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
Joe
  • 51
  • 2
  • 2
  • 8

3 Answers3

12

The lack of options for FIND or SEARCH to search from the end of the string makes this quite tricky.

You can use:

Env = TRIM(RIGHT(SUBSTITUTE('AWS Reservations'[Account], " ", REPT(" ", LEN('AWS Reservations'[Account]))), LEN('AWS Reservations'[Account])))

Or break it down for better understanding:

Env = 
VAR string_length = LEN('AWS Reservations'[Account])
RETURN
TRIM(
    RIGHT(
        SUBSTITUTE(
            'AWS Reservations'[Account],
            " ",
            REPT(" ", string_length)
        ),
        string_length
    )
)

Take lm-sample prod as an example.

First, we use REPT(" ", string_length) to create a string of 

"              "
which has the same length as the value lm-sample prod.

Then, we substitute all the occurrences of " " with this extra long 

"              "
and the string will become 
lm-sample              prod

After that, we'll be comfortable getting the substring of string_length from the right, i.e. 

"          prod"

Finally, we trim the result to get what we want, prod.

Results:

results

Reference

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
  • Wasn't sure how to give two answers credit. This one and the one below are both good options. Thanks! – Joe Aug 30 '18 at 19:06
  • 2
    @Joe `FIND(" ", 'AWS Reservations'[Account])` gives you the first occurrence of space, not last, so the other answer is actually wrong. https://i.stack.imgur.com/VYYry.png – Foxan Ng Aug 31 '18 at 00:12
0

You were passing the start character index rather than desired character count to the RIGHT() function.

Right(Text, NumberOfCharacters)

Try this:

Env = 
Var FirstSpace = FIND(" ", 'AWS Reservations'[Account])
Return RIGHT('AWS Reservations'[Account], LEN('AWS Reservations'[Account]) - FirstSpace)
user5226582
  • 1,946
  • 1
  • 22
  • 37
0

A minor clarification that might help others: if you're searching for the last instance of a string part separated by other than space, say period (.), e.g. to get "third" from "first.second.third" you'd need to substitute period for space in the first " ", but keep the second " ", as that's the "extra long" string, TRIM'ed out ...

JohnD
  • 187
  • 11