Skip to main content
Solved

String Replacer with number, strings and decimals

  • November 28, 2017
  • 4 replies
  • 183 views

Forum|alt.badge.img

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!

Best answer by jdh

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.

View original
Did this help you find an answer to your question?

4 replies

jdh
Contributor
Forum|alt.badge.img+28
  • Contributor
  • Best Answer
  • November 28, 2017

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.


Forum|alt.badge.img
  • Author
  • November 28, 2017
jdh wrote:

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.

 

 


jdh
Contributor
Forum|alt.badge.img+28
  • Contributor
  • November 28, 2017
francism wrote:
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

 

 


Forum|alt.badge.img
  • Author
  • November 28, 2017
jdh wrote:
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

 

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings