Client has Items in the format of XX/XXXXXXXXXXXXXX (2 segments, 2 & 14 characters).
The Items are entered in various lengths.
Some of the Items may appear with as few a 5 characters total (e.g. AP/W20), while others will be longer, up to using the full length (e.g. AP/12345678901234).

We have created a Gridview that reports details on the Items. We send this View as a spreadsheet to their Supplier.
So far, no problem.

Here is the issue:
The Supplier wants us to generate the spreadsheet so that the column with the Item number will always be 26 characters long (for whatever reason).

How do we do this?

Hi Samuel:

In GridView you could try a Calculated Field like: MID(TRIM([Item number fieldname])&" ", 1, 26) where the spaces after the ampersand pad out the length to at least 26 characters (have to be at least 25 spaces, though it doesn't appear that way on in this post).

Most languages have a PAD() function but GridView doesn't so we have to make our own.

Thanks Bruce.
This did not provide the solution we hoped for, It only added one more character to the field.

But you got me thinking again and after playing around, I came up with the formula. Here it is:
{Item #} & Repeat(" ", 26 - Length({Item #}))

The Repeat function allows us to tell GV to repeat a string a number of times.
As you may see in the formula, I was able to get GV to calculate how many characters each Item # is, using the Length function.
Finally, we ask GV to extend a bunch of additional spaces to pad the field to 26 characters.

It works!

When typing formulas on the Forum for people to read, I would suggest using the {code} style which indents and switches to a monospaced font so it's easy to see significant spaces. You need square brackets "[style name]" for a style to be recognized.

If you want all strings to be a specific length, then appending that number of spaces and taking the leftmost characters is probably the most efficient way of doing this:where "n" is the required length of the string.

This will evaluate slightly faster than calculating the number of spaces needed each time.

I think Sam missed Bruce's comment that the his formula (which uses "MID" rather than "LEFT" so needs two parameters) required 25 spaces to be specified. The reformatting of the reply for the Forum removes the duplicate spaces unless the {code} format is used.

Bruce's formula would display as:

Andrew:



Now that I've learned this, I'll have to remember because the [code] style-name doesn't appear to be one of the buttons.