Set the Length of the Cell

[carl]

I am looking for a formula that can take a value in a given cell and
depending on how many characters in the cell, add "spaces" so that the
totl length is 5 characters.

For example.

I have these value in Col A:

A
AA
AAP
AAPL
AAPL1

I would like a formula for Col B that woudl produce this result:

A____
AA___
AAP__
AAPL_
AAPL1

Where the "underscore" represent a "blank space".

Thank you in advance.

 

[Pete_UK]

Try this:

=IF(LEN(A1)>5,LEFT(A1,5),A1&REPT(" ",5-LEN(A1)))

This will truncate the string to 5 characters if it is longer.

Hope this helps.

Pete

 

[Ron Rosenfeld]

I am looking for a formula that can take a value in a given cell and
depending on how many characters in the cell, add "spaces" so that the
totl length is 5 characters.

For example.

I have these value in Col A:

A
AA
AAP
AAPL
AAPL1

I would like a formula for Col B that woudl produce this result:

A____
AA___
AAP__
AAPL_
AAPL1

Where the "underscore" represent a "blank space".

Thank you in advance.

If you want to truncate strings that are longer than five characters, then:

=LEFT(A1&REPT(" ",5),5)

If you don't want to truncate longer strings then:

=LEFT(A1&REPT(" ",5),MAX(5,LEN(A1)))