Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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 wrote in message ups.com... 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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 " 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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 wrote in message ups.com... 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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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 " 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) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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" wrote... I stand corrected... Cool... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding leading spaces
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Trim Leading Spaces | Excel Worksheet Functions | |||
trim leading spaces | Excel Discussion (Misc queries) | |||
How do I add leading spaces to a value? | Excel Worksheet Functions | |||
Remove Leading Spaces | Excel Discussion (Misc queries) |