svillardi

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.

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

membership

This solution is only available to members.

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

Here is one way to do it.

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

Saqib

FCharCol.xls

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

Saqib

FCharCol.xls

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

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

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

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.

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

=A2&REPT(" ",46-LEN(A2))

ASKER

Thanks!

ASKER

If I am filling in this formula, how am I supposed to use the same field for the actual data?