Creating a new column with only a portion of another

[peterknowlton]

For example, one column has

01r01

02r10

05r1

07r9

and I want to create a column that contains only the numbers after the r. So for example,

01

10

1

9

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please try the below for creating a calculated column for what you need. 

Simply put +1 at the very end of the DAX formula.

Expected column CC = 
VAR _length =
    LEN ( Data[ColumnA] )
VAR _locationofR =
    SEARCH ( "r", Data[ColumnA], 1 )
RETURN
    RIGHT ( Data[ColumnA], _length - _locationofR +1 )

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calculated column.

Expected column CC =
VAR _length =
    LEN ( Data[ColumnA] )
VAR _locationofR =
    SEARCH ( "r", Data[ColumnA], 1 )
RETURN
    RIGHT ( Data[ColumnA], _length - _locationofR )

[peterknowlton]

This is perfect, thank you! One last thing, if I want to include the "r" each time how do I do that?

So 

r01

r10

r1

r9

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please try the below for creating a calculated column for what you need. 

Simply put +1 at the very end of the DAX formula.

Expected column CC = 
VAR _length =
    LEN ( Data[ColumnA] )
VAR _locationofR =
    SEARCH ( "r", Data[ColumnA], 1 )
RETURN
    RIGHT ( Data[ColumnA], _length - _locationofR +1 )

[peterknowlton · Accepted solution]

Thank you, this is perfect!

[tamerj1]

Hi @peterknowlton 

please try

MID ( tablename[Column], 4, LEN ( tablename[Column] ) - 3 )

unless it is more complex than the examples.