To make a set of numbers of varying length into strings with the same number of characters, try using the TEXT formula. This formula won’t handle strings, so if you need to add leading zeros to an alphanumeric identifier you might have to try modifying the VBA formula pasted at the end.
Here’s an example: =TEXT([Number to add trailing 0’s],”Put a 0 to match the desired length”)
So if you’re trying to make “1” six digits long, you would put six zeros in a row like: “000000”.
If you’re trying to do the same thing on an alphanumeric string, try adding this custom formula into your workbook. After you paste the code into a VBA module, you can call it by typing addZeros([the Alphanumeric string], [desired length of string])
So for example: =ADDZEROS(“AA”,4) will result in 00AA
Here’s the code:
Public Function addZeros(makeLonger As String, numberDigits) As String Dim x 'Adds 0's to the front of the number until it's the correct length 'Change "0" to another character if you need a different character than 0 For x = 1 To (numberDigits - Len(makeLonger)) makeLonger = "0" & makeLonger Next x addZeros = makeLonger End Function