Adding trailing blanks

[friend11_6]

Hi.

Does anyone know how to set the format of a cell such that it will automatically add trailing blanks to the data entered into that cell while fixing the number of characters that could be entered into that cell?

Thanks.
Friend

[Palmetto]

Not sure about the trailing spaces, but to control length use Data Validation, Text Length option.

[Shijesh Kumar]

There are several ways to achieve this.. one way is by formula..
Assuming that if the length of text is less than 8 then you want space to make the length 8 you can use the below formula



Enter the below formula

Please Login or Register  to view this content.

After applying the formula you can do copy -> paste special -> values..

[DonkeyOte]

You can't alter the physical content of a cell using a Custom Format - you can alter it's appearance however, ie:

@*.

would "appear" to fill up the remainder of a cell up with full stops though underlying value remains just the initial text (the . would print however).

If you want to physically alter the content of the cell you would need to use either a formula to correct in another cell or a VBA change event to alter the cell upon entry... so say you wanted A1:A10 to be a max of 8 characters with surplus characters filled with a space:

Please Login or Register  to view this content.

As Palmetto has said you could use Data Validation to restrict length but given you need to alter the value with VBA you may as well validate there also - Validation can on occasion be circumvented (copy & paste etc...)