How can I replace character 14 - 20?

[imarijan]

Hi all,

I am new to Alteryx and struggling to do the following, trying to replace the characters 14 - 20 (marked below) in a column with 0000000.

Sample:

CA003.170024.1004167.80401.208.CA011
CA003.170017.1004167.80401.600.US085
CA003.170017.1001700.80401.208.FR012
CA003.170017.1001700.80401.208.FR012
CA003.170017.1003782.80401.208.CH051

Any help is very much appreciated.

Thank you all, Irena

[jdunkerley79 · Accepted solution]

Probably the easiest way is:

Left([Field1],13)+'0000000'+Substring([Field1],20)

Have attached a quick sample 

[atcodedog05 · Accepted solution]

Hi @imarijan ,

Here is a way to do it.

Using below in the formula

Left([Text],13)+'0000000'+Substring([Text],20) 

Output

Another formula 

Left([Text], 13)++'0000000'+Right([Text], Length([Text])-20)

Hope this helps : )

If this helps please mark this post as solution

[atcodedog05]

Haha... looks like @jdunkerley79 already got here : )

[atcodedog05]

Hi @jdunkerley79 ,

I like your way of inserting code sample.

I will also follow it. It looks neat : )

Really enjoy this learning experience : )

[jdunkerley79]

Hi @atcodedog05 ,

makes it easier to read - be nice if it had syntax highlighting but will let them off!

[vizAlter · Accepted solution]

Hi @imarijan — Try this solution... you can also solve like this:

1) Use "RegEx" tool for Replace output method:

(.{13})(.{1}[0-9]{6})(.*)

1st Group ( ) reads 1st to 13th position,

then 2nd Group ( ) reads from 14th position to 20th which is actually 1st to 6th position for this Group,

and 3rd Group ( ) is for remaining text, till end.

And, put this expression in the Replacement Text section of your RegEx tool:

$1$'0000000$3

If this solves your issue , if not let me know!

[imarijan]

Thank you very much for the quick response! Amazing

[atcodedog05]

Happy to help : ) @imarijan