Link to home
Start Free TrialLog in
Avatar of svillardi

asked on

Custom Formatting Excel with spaces


I have a report which must go out with a specific amount of spaces per column.

For example:
The first column would be "lastname, firstname" and there would be 46 total characters until the next column.

I tried using the special formatting, but it doesn't seem to work with spaces.  Only characters.  Unless I am missing something.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of svillardi


Please explain how to use this.

If I am filling in this formula, how am I supposed to use the same field for the actual data?
Here is one way to do it.

You will need an extra column to store the values padded with spaces.

You will either have to live with the revised data being in a different column, or:

1) Use the formula to get revised results into a new column

2) Copy that column, and use Paste Special / Values to paste over the original column

3) Delete the new column that held the formula
Or use a macro to update the data in place:

Sub AddTheSpaces()

    Dim arr As Variant
    Dim r As Long
    Dim LastR As Long

    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Range("a2", "a" & LastR).Value
        For r = 1 To UBound(arr, 1)
            arr(r) = Left(arr(r) & String(46, " "), 46)
        .Range("a2", "a" & LastR).Value = arr
    End With

    MsgBox "Done"

End Sub

Open in new window

Thanks for the responses.  It looks like this will work, but I'm trying to understand what each part of the formula means.

for example:

=LEFT(A2&REPT(" ",46),46)

I would guess LEFT means "from the left to the right"?
" " means "a space", I know that
What about REPT, etc...

So can you EXPLAIN the formula so I can customize it?

Thanks.  This is going to work....
LEFT takes the first n characters starting from the left of the string.

REPT repeats a given character a specified number of times.

Both functions are described quite well in the help file.
Another option:
=A2&REPT(" ",46-LEN(A2))