Set the Length of the Cell

C

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.
 
P

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
 
R

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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top