Forum Discussion
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]
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
- parry2kSuper User
Anonymous your requested result doesn't match with your sample dataset. can you double check and rewrite your post.
- parry2kSuper User
Anonymous is this looks correct?
- AnonymousNot applicable
- v-chuncz-msftCommunity Support
Anonymous
You may also try the following code.
Text.Split([Column1], " "){5}