asked on

Custom Formatting Excel with spaces

Hi,

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.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

svillardi

ASKER

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?

Saqib Husain

Here is one way to do it.

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

Saqib
FCharCol.xls

Patrick Matthews

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

Patrick Matthews

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)
        Next
        .Range("a2", "a" & LastR).Value = arr
    End With

    MsgBox "Done"

End Sub

Open in new window

svillardi

ASKER

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

Patrick Matthews

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.

wchh

Another option:
=A2&REPT(" ",46-LEN(A2))

svillardi

ASKER

Thanks!