Trim last digit and then pad up to 14 digits

[anthony123]

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

[atcodedog05]

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 😀👍

[echuong1]

You should be able to combine the two - you can use a right() formula to grab the last digit, and then a padleft() to add the leading zeros. I added a tostring() around the digit field to apply the right() formula.

padleft(right(tostring([Field1]),1),14,"0")

[RolandSchubert]

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

[anthony123]

Thank you all. Can someone also explain to me what this Regex script actually does: subString(REGEX_Replace(Trim([field]),'[^\d]',''),0,15)

[atcodedog05]

Hi @anthony123 

Is it for the same above case ?

Does this formula give the same exact expected output ?

[anthony123]

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

[atcodedog05]

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 🙂 

[Maskell_Rascal]

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

[MarqueeCrew]

@anthony123 ,

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