View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default Need to align text around a symbol???

Based on your formula, you already have the components separated. I
was suggesting that you report the three items (B53, ± and L53 in
three separate columns so you can have maximum flexibility on the
formating. However, to accomplish what you are looking for with a
formula, try this...

=IF(B530,CONCATENATE(REPT(" ",5-LEN(TEXT(B53,"#.0"))),FIXED(B53,1),"
± ",FIXED(L53,1),"%"),FIXED(0,0))

You must format the cell with a fixed font such as Courier New. With
this type of font, a space takes up as much screen space as a letter or
number. This formula calculates the number of digits in the B53 and
then adds spaces before it, if necessary, so that the final length is
five characters including the decimal point. This will force the ± to
be in the 7th position. You'll need to determine the largest possible
number in column B and adjust the "5" to match it.

Hope that helps.
- John