Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

extract a string with a column

Hi

 

I have a column in my dataset that looks like the below. What I want to do is create a new column that extracts the R & L number sequence. For example on the first rows it would look like R1050819, second row L2050819. Ive done the split by delimiter which works ok but its a bit messy. Looking for a way to look for the L or R within the string and return the number as above.

 

Thanks in advance all!

Natasha 

 

Non Stock Purchase - X3744298 L2120819 3 X 20 2 X 40 [X3744298 L2120819 3 X 20 2 X 4]
Non Stock Purchase - X3744658 R1130819 6 X 20 16 X 40 [X3744658 R1130819 6 X 20 16 X]
Non Stock Purchase - X3744508 L1130819 14 X 20 8 X 40 [X3744508 L1130819 14 X 20 8 X]
Non Stock Purchase - X3744820 L2130819 1 X 40 [X3744820 L2130819 1 X 40]
Non Stock Purchase - X3743991 L1120819 15 X 40 [X3743991 L1120819 15 X 40]

  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous ad d new custom column in edit query using following M code

     

    Change [Data] column with your actual column name

     

     Text.BetweenDelimiters([Data], " ", " ", 4, 0)

5 Replies

  • Anonymous your requested result doesn't match with your sample dataset. can you double check and rewrite your post.

      • Anonymous's avatar
        Anonymous
        Not applicable

        Yes parry2k  thats perfect! How did you do that?

         

        Thanks :)

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support

    Anonymous 

     

    You may also try the following code.

    Text.Split([Column1], " "){5}