Custom Formatting Excel with spaces

svillardi used Ask the Experts™

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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
You'll need to use a formula.

For example:

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

Or, you could use a macro.


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.

Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Top Expert 2010

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
Top Expert 2010

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....
Top Expert 2010

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



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial