Alteryx: How can I add a period into a long string?

Category:
Question ID: 109956
2
0

I have a long series of digits (15) and I need to add three periods- one after the 5th character, one after the 9th, and one after the 11th. The field I want to update has the same number of characters every time and I need the periods and the same place every time. So I need:

Before: 123456789012345

After: 12345.6789.01.2345

Normally I would use LEFT() and RIGHT() but that seems difficult with this problem.

Marked as spam
Posted by (Questions: 17, Answers: 0)
Asked on November 5, 2020 9:54 am
7 views
Answers (1)
2
Private answer

You actually can accomplish this using LEFT() and RIGHT(), but you will have to nest some of the functions:

Left([Field1], 5)  +  '. '+ Right(Left([Field1], 9), 4)  +  '.'  + Left(Right([Field1], 6), 2)  +  '.'  + Right([Field1], 4)

If you don't want to deal with nested functions, you can get the same result using substrings:

Substring([Field1],0,5)  +  '.'  + Substring([Field1],5,4)  +  '.'  + Substring([Field1],9,2)  + '.' + Substring([Field1], 11,5 )

Hope that helps!

Marked as spam
Posted by (Questions: 0, Answers: 17)
Answered on November 5, 2020 10:48 am