Adding leading spaces

[gcotterl]

Some cells in Col A contain these amounts:

$.58
$1,267,98
$1.20
$10,098.23
$.01
$258,957,98

The horizontal alignment of these cells is left-justified.

How do I add blanks (spaces) in front of the dollar-sign so the last
character of the amount aligns with the right margin? (Note, I can't
just right-justify the cells)

 

[David McRitchie]

If those are numbers then you can use cell formatting
Format, Cells, custom

Here are three you can check out: (from my formula.htm page)
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

with asterisk protection:
_($**#,##0.00_);[Red]_($**(#,##0.00);_($* "-"??_);_(@_)
_(**$#,##0.00_);[Red]_(**$(#,##0.00);_(**$0.00_);_(@_)

Additional examples at
http://www.mvps.org/dmcritchie/excel/formula.htm#numberformat

 

[Guest]

In short you can't, unless you use Courier New font (or some other
proportional font). In Arial, Times New Roman, etc, each digit takes up a
different amount of space. This means that there is no way to line things up
by just padding spaces. The other issue is that the only way to pad the
spaces (to the best of my knowledge) is going to require you to change your
numbers to text which means you could not sum them or perform any calculation
on them. Out of curiosity why will right justifying not work? Perhaps there
is a different way around this, other than padding spaces...

 

[gcotterl]

Col A of my spreadsheet contains:

0ROBERT E WARREN JR INC

329 S D ST

PERRIS CA 92570 02/22/2006

$171.22
0 200500064 2005-2006 008-029
000000870-9
0SAUL, JERRY J & SHIRLEY M

23670 SUNNYMEAD BLV

MORENO VALLEY CA 92553 02/22/2006

$.01
0 200500066 2005-2006 021-329
000000899-6
0ELMER J WOOD INC

2570 3RD ST

RIVERSIDE CA 92507 02/22/2006

$1,322.59
0 200500076 2005-2006 009-005
000000997-1

How do I add leading-spaces ONLY to the cells whose first character is
a dollar-sign (for example, "$171.22", "$.01" and "$1,322.59") so the
right-most character ("2", "1" and "9") of the amount aligns with the
right-margin?

 

[Guest]

I stand corrected... Cool...
--
HTH...

Jim Thomlinson

If those are numbers then you can use cell formatting
Format, Cells, custom

Here are three you can check out: (from my formula.htm page)
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

with asterisk protection:
_($**#,##0.00_);[Red]_($**(#,##0.00);_($* "-"??_);_(@_)
_(**$#,##0.00_);[Red]_(**$(#,##0.00);_(**$0.00_);_(@_)

Additional examples at
http://www.mvps.org/dmcritchie/excel/formula.htm#numberformat

Some cells in Col A contain these amounts:

$.58
$1,267,98
$1.20
$10,098.23
$.01
$258,957,98

The horizontal alignment of these cells is left-justified.

How do I add blanks (spaces) in front of the dollar-sign so the last
character of the amount aligns with the right margin? (Note, I can't
just right-justify the cells)

 

[Guest]

Thomlinson's suggestion of using Courier New is excellent!
We can pad in VBA:

Function justy(s As String) As String
Dim i As Integer
i = Len(s)
justy = s
nodol = Right(s, Len(s) - 1)
If i = 10 Then
Exit Function
End If
For j = 10 To i Step -1
nodol = " " & nodol
Next
justy = "$" & nodol
End Function

I copied you numbers out of your posting to column A in in column B put
=justy(A1) and then copied down. Set the column width correctly and the
results look pretty good.

 

[David McRitchie]

Hi Jim,
Good, that takes off the pressure of guessing whether you had text or numbers for data.

Had they been text cells you would have to do something to remove
the $ sign, and the comma such as using Ctrl+H

Then you would want to include your desired number formatting
but no change in formatting would not occur until text data is converted to numbers.

To TRIM the cells and reenter the values you could use the TrimALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[gcotterl]

I figured out another way of doing it:

Put this formula in Col B:

=IF(LEFT(A1,1)="$",(REPT(" ",80-LEN(A1))&A1),A1)

If the leftmost character in the cell in Col A is $, subtract the
length of that cell from 80. (The result is the number of spaces that
need to be added in front of the $). Append the amount after the last
space.

Otherwise, the cell in Col B will equal the contents of the cell in Col
A.