Adding leading spaces

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)

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...m#numberformat

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> 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)
>

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

Jim Thomlinson


"[email protected]" wrote:

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

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?

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

Jim Thomlinson


"David McRitchie" wrote:

> 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...m#numberformat
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> <[email protected]> wrote in message news:[email protected]...
> > 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)
> >
>
>
>

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.
--
Gary''s Student


"Jim Thomlinson" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "[email protected]" wrote:
>
> > 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)
> >
> >

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.

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim Thomlinson" <[email protected]> wrote...
> I stand corrected... Cool...