need exactly 30 characters in a cell

**scottrotondo** · 01-12-2012, 12:32 PM

Hi there:
I am working on an excel file that I will later need to concatenate and save into a text file. As part of this, I need to get one column to have 30 character spaces in it, even if I have less than 30 characters of data in the cell. Anyone know how I can do that?

**Special-K** · 01-12-2012, 12:48 PM

Use a Helper column with this formula

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

Which means take A1 add 30 spaces to it, take the first 30 characters so the result will always have 30 characters

**scottrotondo** · 01-12-2012, 12:59 PM

Originally Posted by Special-K

Use a Helper column with this formula

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

Which means take A1 add 30 spaces to it, take the first 30 characters so the result will always have 30 characters

Thanks! I copied exactly that formula into the cell in the next column, no dice.

**dilipandey** · 01-12-2012, 01:06 PM

Hi Scottrontondo,

Welcome to the forum.

If you need 30 spaces in the cell not matter if the cell has something or not, use following formula:-

=B1&REPT(" ",30)

If you want all the characters of the cell and deficiency to make 30 chars to be filled by spaces, use following formula:-

=B1&REPT(" ",IF(LEN(B1)>30,0,30-LEN(B1)))

If you want just 30 characters only where deficit will be filled by zeros, use following formula:-

=IF(LEN(B1)<=30,B1&REPT(" ",30-LEN(B1)),LEFT(B1,30))

All above example assumes you want to append (padd) zeros on the right side of the text.
If you still have issues, post your sample file with little explanation. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**scottrotondo** · 01-12-2012, 01:26 PM

actually, I just need blank spaces on the right side of the text, to get to 30 character spaces total for the cell. So, if there's 15 characters of data, I will need 15 blank spaces after that, to get to 30.

**BigBas** · 01-12-2012, 01:35 PM

Scott,

Special-K's formula from above should work in your case. Just be sure to modify the formula correctly to suit. Did you correct the cell reference ( the A1 in his formula should match the cell you are trying to manipulate.