Forum Discussion
Extract numbers from a string (before 0)
Hello,
I have a column with strings like:
11.2.0.353
11.2.0.4543
11.5.0.599393
What I am trying to do is to extract the numbers before "0". Meaning:
11.2
11.3
11.4
11.5
Is there a way I can do it? I initially wanted to use the "replace" function in query and found out that I had to put in every single specific number which is too much.
Alternatively, you can extract text before a delimiter (I would use ".0.", just in case there are 0's in the part you want to keep):
3 Replies
Hey,
yes there is a way.
In the Query Editor mark the column and choose "Split Column" from the context menu. Be aware that this function replacess the source column with the result of this function. And also the charater(s) that is used will not be present in neither of the resulting columns any longer. If you need this column in your data model first duplicate this column and use the duplicated column instead for the steps I describe.
Then I chooes this setting (be aware that I choose ".0" as a separator.
After this step your table looks like this (of course in my example there has been just one column
Doing its magic the Split column automatically added an additional step "Changed Type", and suddenly the both columns of the split are of datatype number. To change this just mark the this step and you will see this M function in the Formula Bar (Menu View: Enable Taskbar if not)
= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SomeStrings.1", type number}, {"SomeStrings.2", type number}})To fix this change the type to text like so
= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SomeStrings.1", type text}, {"SomeStrings.2", type text}})This results to the following
And there you are, now you can remove the column you don't need anymore
Hope this is what you are looking for
Regards
Tom
Alternatively, you can extract text before a delimiter (I would use ".0.", just in case there are 0's in the part you want to keep):
Hey okiedokie2017,
the solution of MarcelBeug is much shorter (and hence smarter) than mine, I always forget about the "Extract" function.
Regards
Tom
