Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to align text around a symbol???
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 -- guillemot ------------------------------------------------------------------------ guillemot's Profile: http://www.excelforum.com/member.php...o&userid=29833 View this thread: http://www.excelforum.com/showthread...hreadid=501818 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to align text around a symbol???
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to align text around a symbol???
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(B530,CONCATENATE(FIXED(B53,1)," ± ",FIXED(L53,1),"%"),FIXED(0,0)) is there an easy way to line them up from this starting point? -- guillemot ------------------------------------------------------------------------ guillemot's Profile: http://www.excelforum.com/member.php...o&userid=29833 View this thread: http://www.excelforum.com/showthread...hreadid=501818 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to align text around a symbol???
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." -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=501818 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
Text Wrapping | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |