Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

extracting values from mid strings - varying character length

Hello, 

 

I have a column of various values in the following format: 

 

XX_123rgdt_XXXXX

XX_343dsbceurfvr_XXXX

XXXX_323d_XXXXX

 

and would like to extract into separate column values between _ and _ from above. I thought of using MID() formula but these values have different length in the middle so it would not work for all cases. does anyone know how to do this?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI Anonymous,

    If the fact table records are similar to the sample data that you shared, you can use the following formula(path function) to create a new calculated colunmn to extract the specific part of strings:

    New Column= PATHITEM ( SUBSTITUTE ( [Column], "_", "|" ), 2 )

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Anonymous,

    If the fact table records are similar to the sample data that you shared, you can use the following formula(path function) to create a new calculated colunmn to extract the specific part of strings:

    New Column= PATHITEM ( SUBSTITUTE ( [Column], "_", "|" ), 2 )

    Regards,

    Xiaoxin Sheng