Excel-Command needed for putting extra spaces in a cell. 1

[Davefeet]

I'm making this ASCII file for the IRS, they require that each column has a certain number of spaces regardless of the actual size of the data within that cell.

Exp. They require 40 spaces in the "name" column, so let's say we have the name "Smith, Jack" that only takes up 11 spaces so we would have to add after it 40-11=29 spaces, so that the column took up 40 spaces.

So I'm looking for some kind of formual or command that will count how many spaces are in the cell and add the appropraite amount of spaces after it. I have about 3000 of these to do so a command or formula would be great.

Thanks, even if you could guide me in any kind of direction. Thanks

 

[acron]

For a text entry in A1 the following formula will pad it with enough spaces to bring the length to 40 characters.

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

Does that do what you want ?

AC

 

[Davefeet]

How do you learn these things, what would be a really good book to learn this stuff, the excel help file really leave me with alot of wuestions. Thanks it worked great. Hopw would I do it if I wanted the spaces infront of the cell data.

But it worked great, thanks.

 

[Davefeet]

What if I wanted it to put just the value of that formula there. So when I go to edit that cell it doesnt just show the formula, but always the value.

It may not be possible.

 

[euskadi]

After you enter the formula, fill down to do all of the cells, the copy and do paste special, values.

 

[acron]

To inlude the spaces before the name use

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

REPT(" ",40-LEN(A1)) calculates and generates the number of spaces, so you can put the text (A1) before or after

As euskadi suggested, just copy the results of the formula and Paste Special Values.

AC