Hello I'm working with strings of numbers that can vary by length, but I need to trim the last digit and then pad with leading zeroes to make the string 14 digits in the end. I thought I could do this with two formulas but only one seems to be applying.
Thanks
Hi @anthony123
Use the below formula
PadLeft(Left(tostring([Field1]),Length(tostring([Field1]))-1), 14, "0")
Workflow:
Hope this helps 🙂
If this post helps you please mark it as solution. And give a like if you dont mind 😀👍
Hi @anthony123 ,
you could use the formula:
PadLeft(Left(ToString([Field]), Length(ToString([Field])) - 1), 14, '0')
ToString([Field]) converts the number to a string
The "Left" function removes the last digit (Length() - 1)
PadLeft fills to 14 characters using '0'
Let me know if it works for you.
Best,
Roland
Thank you all. Can someone also explain to me what this Regex script actually does: subString(REGEX_Replace(Trim([field]),'[^\d]',''),0,15)
Hi @anthony123
Is it for the same above case ?
Does this formula give the same exact expected output ?
Not the same output. I just learned the data I'm working with is first transformed using that script. I don't have much REGEX knowledge so I was interested in knowing what it does and how I can adjust my formula to accommodate it
Hi @anthony123
Its a completely different scenario. I will breakdown from the inside.
subString(REGEX_Replace(Trim([field]),'[^\d]',''),0,15)
Trim([field]) : actually isnt doing anything the formula should be Trim([field]," ") where " " is the character you want to remove.
REGEX_Replace([field],'[^\d]','') : [^\d] denotes all not number characters. And the formula is replacing all non numbers to "" (I.e its removing them)
subString([field],0,15) : output part of the string staring from 0 of length 15.
Hope this helps 🙂
Hi @anthony123
The formula strips out anything that isn't a number.
[^] these brackets and symbol are a negated set
\d signifies looking for digits
So combining them together and using a REGEX_Replace is looking to strip out anything that isn't a number.
The SubString returns the substring of a String starting at start and stopping at length, if provided. So your formula starts at 0 and ends at 15.
I hope that all makes sense.
Thanks!
Phil
This is a hybrid approach:
Left(
PadLeft(ToString([Input]), 15, "0")
, 14)
I think that it performs with the fewest hoops for the engine.
Cheers,
Mark