Forum Discussion
Removing a decimal from a string when it occurs
I am working with version numbers that are represented by a string of numbers, separated by a few decimals - this can vary from version to version. I only need the first 3 numbers of each version. For most versions this involves 5 characters, but I have a few where the third number is a double digit number (i.e., 11, 12, etc.) which means I need to leave 6 characters as opposed to 5. So, I started off trimming (left) by 6 characters and am looking for some way to remove the trailing demical (.) on the versions where I only need 5 characters. Listed below is my source data before trimming it, after trimming it and what I am looking to achieve. Does anyone know how to do this?
| Data before trimming | Data after trimming | Goal |
| 1.2.11.1.2.3 | 1.2.11 | 1.2.11 |
| 1.2.1.0.1.2 | 1.2.1. | 1.2.1 |
Hi krfaughnan,
Text.BeforeDelimiter([Data before trimming], ".", 2)
On the query editor add a new column with the following formula.
As you can see in the documentation the first parameter is the text you want to trim, second parameter the character that you want to trim from, and the last parameter is the position of that character in this case is your second.
Regards,
MFelix
2 Replies
Hi krfaughnan,
Text.BeforeDelimiter([Data before trimming], ".", 2)
On the query editor add a new column with the following formula.
As you can see in the documentation the first parameter is the text you want to trim, second parameter the character that you want to trim from, and the last parameter is the position of that character in this case is your second.
Regards,
MFelix
Perfect! Thank you - that worked!!
