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.
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!