Skip to main content
  • EN

Hi all,

I was unable to find an answer I needed so I thought I would post.

I have been using the StringReplacer and am trying to learn the regex commands to no avail.

I have a column with values that are mostly numeric, some alphabetical, some blank and some number with decimals. I would like to ensure that every number that does not have a decimal gets a decimal added to it.

Example of value range:

000625.224

512566254

662512566

751256.335

TLL

003640.123

CODD

My ask is how can I use the StringReplace (or any other transformer) to take the numeric values here. And insert a period in between the 6th and 7th characters. Leaving 6 before the decimal and 3 after.

To note. Every numeric value that has a decimal is 10 characters. 6 before the decimal, one decimal and 3 following. Every numeric value that does not have a decimal is 9 characters. I want to keep all Alphabetical and Null values untouched.

Thank you for your help!

I would use the StringReplacer in Regex mode with the Text to Replace set to ^([0-9]{6})([0-9]{3})$ and the Replacement Text as \\1.\\2.

I would use the StringReplacer in Regex mode with the Text to Replace set to ^([0-9]{6})([0-9]{3})$ and the Replacement Text as \\1.\\2.

Okay! yes that seems to have done the trick for that exact question. Thank you for your quick response.

 

 

Okay! yes that seems to have done the trick for that exact question. Thank you for your quick response.

 

 

To explain the regex, the () create parts that you can reference in the replacement text. \\0 is the entire matched text, \\1 is everything inside the first set of parenthesis, \\2 the second etc.

 

/] is match any one of the characters inside the brackets, in this case the numeric range 0-9

 

{n} is match the preceding character (in this case any of 0-9) exactly n times

 

^is the start of the line

 

$ is the end of the line

 

these make sure we don't have something like ABC123456789

 

 

To explain the regex, the () create parts that you can reference in the replacement text. \\0 is the entire matched text, \\1 is everything inside the first set of parenthesis, \\2 the second etc.

 

>] is match any one of the characters inside the brackets, in this case the numeric range 0-9

 

{n} is match the preceding character (in this case any of 0-9) exactly n times

 

^is the start of the line

 

$ is the end of the line

 

these make sure we don't have something like ABC123456789

 

 

awesome! that explanation is extremely helpful

 

 

Reply


Community Stats

30,194
Posts
114,613
Replies
37,856
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.