Reformatting text to number formatt

susom

New Member
Joined
Aug 3, 2011
Messages
47
I have used text based functions to extract numeric text values from a text string. I.e. the text string in a cell contained =<4%, and I pulled out the 4%. The 4% is left aligned in the cell because it is still being recognized as text by excel, I believe. However, when I go to reformat the cell using the format option to make it a 2 decimal number (e.g. .04), it doesn't work. However, if I go I hit f2 to edit the contents and escape out the format will change. Is there an easier way than hitting f2 / enter to reformat the cells?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Try surrounding you formula with Value()

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">4%</td><td style="text-align: right;;">4.00%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K6</th><td style="text-align:left">=VALUE(<font color="Blue">J6</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top