Need to align text around a symbol???

[guillemot]

Hi all,

I am trying to align a table of data that looks like this:
15.8 ± 3.3%
13.5 ± 1.4%
2.9 ± 3.3%
11.1 ± 0.0
6.5 ± 4.7
50.0 ± 29.6
146.8 ± 0.0
0.4 ± 0.0%

They are formatted as text. Not all get the percent symbol. I'd like to
align the ± symbols to tidy the appearance of the data. Suggestions?

Actually, the whole thing looks more like this (here's a sample with
three data columns that spans two sampling dates Note the non-data
rows)
Spring 2004
Data Type Box Woodlot Fernwood
Number of Quadrats Invaded 18.0 20.0
Total A. petolata Cover (%) 19.7 ± 2.1% 50.0 ± 5.5%
Adult Cover (%) 3.4 ± 3.7% 16.0 ± 7.5%
Seedling Cover (%) 16.3 ± 4.6% 34.8 ± 4.3%
Seedings/0.5m2 88.0 ± 0.0 74.4 ± 0.0
Adults/0.5m2 2.8 ± 4.6 12.7 ± 3.1
Mean A. petiolata Height 16.5 ± 30.2 55.8 ± 52.8
Per Capita Fecundity 56.8 ± 0.0 228.6 ± 0.0
Leaf Damage (% removed) 0.9 ± 0.0% 0.6 ± 0.0%
Damage Types (% of Quadrats)
Windowpane Damage 0 0
Edge Feeding Damage 0.9 ± 0.1% 0.4 ± 0.1%
Holes Damage 0.9 ± 0.1% 0.9 ± 0.1%
Disease Damage 0.1 ± 0.0% 0.2 ± 0.0%
Other Damage 0 0
Soil Cover (%) 35.0 ± 5.1 11.5 ± 1.4
Wood Cover (%) 29.8 ± 4.8 7.4 ± 3.9
Leaf Cover (%) 35.3 ± 0.0 81.1 ± 0.1
Rock Cover (%) 0 0.1 ± 0.2
Spring 2005
Data Type Box Woodlot Fernwood
Number of Quadrats Invaded 19 20
Total A. petolata Cover (%) 12.3 ± 2.9% 37.7 ± 1.6%
Adult Cover (%) 10.0 ± 1.0% 5.6 ± 6.2%

Cheers,
Jeff

 

[John Michl]

Not a whole lot you can do if it is formatted as text since you'd have
to control the line up by adding and subtracting spaces. I'd suggest
splitting each cell into three cells: amount, +/- sign, variance or
something like that. You could use a string manipulation function to
parse up the current data.

A1: 3.4 ± 3.7%
B1: =LEFT(A1,FIND("±",A1)-2) or 3.4
C1: ±
D1: =MID(A7,FIND("±",A7)+2,100) or 3.7%

Technically, the result is text but Excel is smart enough to let you
use the result in math operations.

- John

 

[guillemot]

Thanks for your reply, John.

I'm afraid that I'm not fluent enough in excel to make sense of what
you've posted. Perhaps if I back up a step to the point where my data
were in numeric form it would be easier (they weren't entered as
text).

I have two tables: one that calculates the the mean percent, and one
that calucaltes the standard error. I then made a table that
concatenates the two and inserts the +/- symbol between them. I copy
and paste this into a clean space and that's my final product. It looks
like this where B53 is the % and L53 is the std error:

=IF(B53>0,CONCATENATE(FIXED(B53,1)," ± ",FIXED(L53,1),"%"),FIXED(0,0))

is there an easy way to line them up from this starting point?

 

[John Michl]

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(B53>0,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

 

[wjohnson]

If the text is "split" into 3 columns: i.e 15.8 ± 3.3% - Leave it
in 3 columns and then just "right justify" on the 15.8 column, "center"
the ± column and then "left justify" the 3.3% column and then narrow the
columns a necessary. The left, right and center are visible on the MENU
BAR. You might have to select TOOLS, TOOL BARS and then FORMATTING to
see the icons - there will be 3 of them together. Then you can use any
font you want and the numbers will look "Lined-Up."