Need Formula to Add Trailing Spaces in Excel

[kanin247]

My question relates to a solution posted by mbarron in a previous forum topic: "Add a leading zero to CSV file"

=======FORUM POST IN QUESTION=============
mbarron (TechnicalUser) Nov 22, 2004
Assuming you want you want your number to be 6 characters long:
=RIGHT(REPT("0",6)&A1,6)

Change the 6s to the number of characters you want your results.

Mike
=========================================

Note: I am creating a file in excel and converting it to a CSV file.

By using the above formula, you are able to add leading-spaces to your text. However, I need to be able to add trailing-spaces to my text. Is there another function I need to use to do this? I tried LEFT() thinking it moves the space from leading to trailing but it does not.

Here's an example of what I am trying to do:
=Excel File= *L=Length
L=10 | L=10 | L=3 | L=9 |
Jones | Sammuel | USA | 111111111 |
Warner | Jim | USA | 222222222 |

=CSV File=
Jones ,Sammuel ,USA,111111111
Warner ,Jim ,USA,222222222

 

[DaleWatson123321]

I believe this should work...

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

and of course modify the length as required for each field.

Hope this helps.

Regards, Dale Watson

 

[SkipVought]

Hi,

Assuming that you have a number of rows that ALL need to be padded identically, the padding for each column depends on the MAX(LEN(columnrange)) as an ARRAY FORMULA.

Then use that value in your formula

[tt]
=A2&REPT(" ",A$1-LEN(A2))
[/tt]
assuming that A1 contains the MAX formula. You can copy across and down.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[kanin247]

Actually, before I even went back to check on any replies I answered my question. Here's the formula I used:

=LEFT(B3&REPT(" ",28),27)

So, the formula pads the cell with (28) spaces following my text and only display (27) characters.

Thanks though to those that replied with your formulas.