How to check for number of characters in string....including trailing spaces?

[houghton17]

Hello Everyone, 

I'm looking for a solution to check an inbound file that requires 161 characters for each record (header, detail and trailer).  We've had situations where 160 or 162 (or more) characters have been provided for a record.  We need a solution to validate all records in the inbound file contains 160 characters....which includes trailing spaces at the end. 

Please let me know your thoughts.  Thank you so much!!!

[KellyMiller]

You can find the length of the field with the Length(String) function. If a field is too long, you can use some sort of trim function (for example, Right(String,160)). If it is too long, you can use one of the Pad(String) functions.

[OTrieger]

@houghton17  as @KellyMiller wrote you can use Length([Field]) And you can do it also in that way Length([Field])=160 and set the field to Bool data type and then you will get True or False flags indicating if the string is equal to 160 or not. 

[houghton17]

Thank you for your suggestions!  I've actually tried to utilize the Length([Field]) formula, but it doesn't appear to include trailing spaces.  

[KellyMiller · Accepted solution]

I tried a simple example, and I was able to replicate what you are seeing. I think your data is being trimmed as it is being read -- in other words, the trailing spaces are not being included part of the value of the string field. In this case, you can use the PadRight function to put the extra spaces back. You can test this out by outputting your data as a csv file. Make sure that the Quote Output Fields option (Option 5) is set to "Never".

[Ankur_BDO · Accepted solution]

Here is a cleaver approach I tried and it is working!

Add a formula to replace all spaces with a special character (Example Asterisk). This will fill all the blank spaces with a valid character. Now use the Length function to calculate the length. You also add am IF condition to check if length is greater than 160 or not. See the image below or check the working solution in the attachment.

If this was useful, please mark it as Solution!

[houghton17 · Accepted solution]

Thank you very much for the suggestions!  This is what I was looking for!