Text Formatting

[Ark68]

I need some help ...

I have a column of text that must always maintain a set # of characters (40) regardless of how short the text value is.
Is there a way to force spaces to be added ... ie formatting a cell that ensures there are always 40 characyers? Something like entering a number 23.45 into a cell formated as 000.0000 gives you 023.4500?

 

[Todd Bardoni]

Have you tried Data Validation?

 

[Derek]

Hi there RobK, welcome to the Board,

Paste this formula in B1 and scroll down:

=A1&REPT(" ",40-LEN(A1))

This will add spaces to anything in column A so that the total number of characters equals 40

Hope this gives you some ideas

regards
Derek

 

[Yogi Anand]

Hi Robk:

Just to add to Derek's contribution, you may add the space character (or for that matter any other character), before or after the actual word to make it of any specific length (in our case here that is 40).

 

[Ark68]

This is fantastic!!! Thanks for your help everyone.
Yogi .... how would I place spaces in front of the text, rather than after?

 

[brucesw]

Not to steal anyone's thunder but swap the formula around.

=REPT(...)&A1

The & is a shortcut for concatenate. It joins two text strings together.