Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Afternoon,
I have a field pulled for Sharepoint, that is configured as a numeric value. I am then attempting to take this value and make sure it is always formatted as a 5 digit number with preceding zeros. From that point, I need to then convert it to a text value to match the formatting from other tables and "link" up as the same field.
Num(title,'00000') as title,
text(title) as storelocation,
This is the arguement I am trying, but does not seem to work. When using a variation of if arguments, it blanket added zeros without considering it should only be a max value of 5 digits, and filling the preceding values with zero.
Any assistacne would be appreciated.
Perhaps:
Repeat('0',5-len(title)) & num(title)
Tried that solution, however it did not accomplish the intended goal. If you see, below I am essentially trying to convert or format the non-5 digit values into 5 digit values. Then convert that output into text to match the other storelocation field in other tables.
38 | |
0 | |
00002 | |
00003 | |
00009 | |
9 | |
00015 | |
00017 | |
17 | |
00018 | |
18 | |
00021 | |
00025 | |
00031 | |
00034 | |
00037 | |
37 | |
00038 | |
00039 | |
00042 | |
42 | |
00048 |
You'll have to explain what problem you're encountering, because the formula in question should pad any number shorter than 5 digits with enough leading 0s to make it five digits - which is your stated goal.