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

Question

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 · Accepted Answer

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 Answer

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 Answer

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