Format Value in a Textbox

%3CLINGO-SUB%20id%3D%22lingo-sub-2120404%22%20slang%3D%22en-US%22%3EFormat%20Value%20in%20a%20Textbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120404%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20textbox%20in%20a%20chart%20that%20displays%20the%20value%20of%20a%20cell%20(D37)%20in%20my%20spreadsheet.%3C%2FP%3E%3CP%3ECell%20(D37)%20contents%20are%3A%20%3D%22Average%20Total%20Intake%20%3D%20%22%26amp%3BD36%26amp%3B%22%20kcal%22.%3C%2FP%3E%3CP%3ECell%20(D36)%20is%20the%20average%20of%20some%20data%20in%20the%20sheet%3A%20%3DAVERAGE(H2%3AH29).%3C%2FP%3E%3CP%3ECell%20D36%20containing%20the%20average%20is%20formatted%20to%20display%20no%20digits%20after%20the%20decimal%20place.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20D37%20displays%20the%20average%20with%2012%20digits%20past%20the%20decimal%20place%2C%20but%20I%20want%200%20digits%20past%20the%20decimal%20place.%26nbsp%3B%20If%20I%20try%20to%20change%20the%20display%20format%20via%20the%20ribbon%2C%20I%20only%20hear%20the%20'you%20screwed%20up'%20chime%2C%20no%20change.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20format%20the%20value%20in%20cell%20(D37)%20to%20display%20with%20the%20desired%20number%20of%20digits%20after%20the%20decimal%20place%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2120404%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120760%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Value%20in%20a%20Textbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677336%22%20target%3D%22_blank%22%3E%40Ralph_Page%3C%2FA%3E%26nbsp%3BExcel%20has%20many%20not%20so%20obvious%20functions.%20Glad%20I%20could%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120461%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Value%20in%20a%20Textbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BPerfect%2C%20I%20was%20not%20aware%20of%20the%20TEXT%20function.%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2120448%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20Value%20in%20a%20Textbox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2120448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677336%22%20target%3D%22_blank%22%3E%40Ralph_Page%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%22Average%20Total%20Intake%20%3D%20%22%26amp%3BTEXT(D36%2C%220%22)%26amp%3B%22%20kcal%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a textbox in a chart that displays the value of a cell (D37) in my spreadsheet.

Cell (D37) contents are: ="Average Total Intake = "&D36&" kcal".

Cell (D36) is the average of some data in the sheet: =AVERAGE(H2:H29).

Cell D36 containing the average is formatted to display no digits after the decimal place.  

Cell D37 displays the average with 12 digits past the decimal place, but I want 0 digits past the decimal place.  If I try to change the display format via the ribbon, I only hear the 'you screwed up' chime, no change.

Is there a way to format the value in cell (D37) to display with the desired number of digits after the decimal place?

 

Thanks

 

3 Replies

@Ralph_Page Try this:

="Average Total Intake = "&TEXT(D36,"0")&" kcal"

 

@Riny_van_Eekelen Perfect, I was not aware of the TEXT function.  Thanks

@Ralph_Page Excel has many not so obvious functions. Glad I could help!