However, things can become troublesome when trying to apply number formatting to an existing range of data. You can update the format of a cell, but sometimes it won’t automatically refresh with the new formatting. This stubbornness usually happens when choosing a custom number format. You can update it by double-clicking the cell, making no changes, and then press Enter, but this can be very tedious. This process is particularly troublesome when importing significant amounts of data. If you find yourself in this boat, try this trick: Start with a range of pre-entered data. In this example, we’re using a column that’s entered as text. We want to give it custom formatting so it looks more like a time from a stopwatch. We want to give it the format: [h]:mm:ss To do this, start by selecting the column.
Then, click the drop-down in the Number pane of the Home ribbon. Choose More number formats. Or, if you want, choose one of the presets.
Choose Custom and type in the format you want to use for the numbers. Click OK.
Notice nothing has changed, even though it shows “Custom” in the Number Format drop-down.
If you edit the cell and press enter, the new format takes effect. But with hundreds of rows of data, this will take forever.
To speed things up, select the column, go to the Data ribbon, and click Text to Columns.
Choose Delimited and click Next.
Uncheck all the delimiters and click Next.
The number formatting of all the cells will update.
This trick is a bit of a hack, but it works. It takes all the values from each row and then re-enters them into the cells automatically. For this reason, this trick will not work for cells that are formulas. If you have formulas, pressing F9 should recalculate the sheet and update the number format. But in my experience, I haven’t had this problem with formulas. Thank you. If you want to put this process into VBA code use the following: Range(“Whatever range of cells you need”).Select Selection.NumberFormat = “Whatever Format you Desire” Selection.TextToColumns DataType:=xlDelimited Example: Range(“C2:C8548”).Select Selection.NumberFormat = “[$-409] mmm-yy;@” Selection.TextToColumns DataType:=xlDelimited This example takes the dates in cells C2 through C8548 formats them to Month – Year (Nov-14) and the performs the TextToColumns fuction in the article above to refresh the cells. Thank you in advance For some reason the original hack didn’t work for me suggested by Jack? I followed everything to a tee and excel kept crashing? It frozen and kept saying it’s trying to find a solution to the problem and never recovered. I tried it to a smaller list but will still the same issue. I had numbers stored as text and when changing the category back to General or Number it kept coming up with the error of number stored as text. It didn’t happen to all cells only ones where the text used to be say 0.30, 0.40 etc (with the extra 0 at the end). It would still stay as 0.30 after changing to the General category. I had to then go through all of these numbers and click on them to edit then enter again. It would then register as a number again and display as 0.3. Only your way as suggested fixed my problem! Thank you! You can put this solution into the simple click of a button. If you know how to make a macro you can use the following code that I posted in the comments awhile ago: “If you want to put this process into VBA code use the following: Range(“Whatever range of cells you need”).Select Selection.NumberFormat = “Whatever Format you Desire” Selection.TextToColumns DataType:=xlDelimited Example: Range(“C2:C8548”).Select Selection.NumberFormat = “[$-409] mmm-yy;@” Selection.TextToColumns DataType:=xlDelimited This example takes the dates in cells C2 through C8548 formats them to Month – Year (Nov-14) and the performs the TextToColumns fuction in the article above to refresh the cells.” Also if you have the ability you can program in a dialog box to input a column number and execute off of a hotkey you set up. Before running the macro, each time you must change the format of the cells to the appropriate data type (e.g. Number, Date). Another thing to note is that running the TextToColumns macro changes the settings in the UI dialogue. This means you will probably encounter unexpected behavior the next time do something that uses those settings. I added a section of code at the bottom to reset the delimiter to “tab”, which is what I usually use. This can be modified to use other delimiters like “comma”, or this section of code can be removed completely. ‘ Converts a value to the data type of the cell. Public Sub DataTypeConversion() Dim rngToConvert As Range On Error Resume Next ‘ InputBox will prevent invalid ranges from being submitted when set to Type:=8. Set rngToConvert = Application.InputBox(Prompt:=”Select a range of cells to convert the values to the data type of each cell.”, Title:=”Data Type Conversion”, Default:=Application.Selection.Address, Type:=8) ‘ Check for cancel: “Object required”. If Err.Number = 424 Then ‘ Cancel. Exit Sub End If On Error GoTo 0 ‘ If nothing was selected then exit. If rngToConvert Is Nothing Then Exit Sub End If ‘ If more than one column is selected then exit. If rngToConvert.Columns.Count > 1 Then MsgBox “Only one column can be processed at a time.”, vbExclamation + vbOK, “Data Type Conversion Range” Exit Sub End If ‘ If more than one range is selected then exit. If rngToConvert.Areas.Count > 1 Then MsgBox “You selected multiple ranges. Only a single range can be processed at a time.”, vbExclamation + vbOK, “Data Type Conversion Range” Exit Sub End If ‘ Use TextToColumns to convert the value in each cell to the cell’s data type. rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ‘ Reset delimiter in TextToColumn dialogue to tab. Optional section. Dim ws As Worksheet Set ws = rngToConvert.Parent Set rngToConvert = ws.Range(ws.Cells(ws.Rows.Count, ws.Columns.Count), ws.Cells(ws.Rows.Count, ws.Columns.Count)) rngToConvert.Value = “1” rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True rngToConvert.ClearContents rngToConvert.Parent.UsedRange End Sub Taking it a step further, instead of making it so you have to click a button or enter a hotkey, what about an events macro that detects that you have changed a cell format and automatically runs on the cells that have the format changed? It seems that would functionally emulate the behavior Excel ought to be doing anyway, which would be great as long as it doesn’t cause twitches or glitches or something. Is there a reason why an events macro would be impractical? Also, using an event macro is a creative idea, but then I think each worksheet for which you want this event to be handled would need an event handler, which turns it into a macro workbook. Instead, I like the idea of using normal workbooks most of the time and using an AddIn with macro abilities to do work on the normal workbook. Perhaps there is a way that I’m not thinking of… ‘ Converts a value to the data type of the cell. This approach replaces the need to copy values into a text editor and then paste them back into Excel. Public Sub DataTypeConversion() Dim rngToConvert As Range On Error Resume Next ‘ InputBox will prevent invalid ranges from being submitted when set to Type:=8. Set rngToConvert = Application.InputBox(Prompt:=”Select a range of cells to convert the values to the data type of each cell.”, Title:=”Data Type Conversion”, Default:=Application.Selection.Address, Type:=8) ‘ Check for cancel: “Object required”. If Err.Number = 424 Then ‘ Cancel. Exit Sub End If On Error GoTo 0 ‘ If nothing was selected then exit. If rngToConvert Is Nothing Then Exit Sub End If ‘ Consolidate intersecting ranges, and limit to used range. Set rngToConvert = Intersect(rngToConvert, rngToConvert.Parent.UsedRange) ‘ Break range into segments consisting of a single column and a single area, which is the only shape Text To Columns can process. Dim rngCol As Range, rngSegment As Range Dim colSegments As Collection Set colSegments = New Collection For Each rngCol In rngToConvert.Columns For Each rngSegment In rngCol.Areas colSegments.Add rngSegment Next Next Application.ScreenUpdating = False ‘ Convert data types in each segment. For Each rngSegment In colSegments ‘ Entirely blank ranges cannot be processed by Text To Columns. If (rngSegment.Count Application.WorksheetFunction.CountBlank(rngSegment)) Then ‘ Use TextToColumns to convert the value in each cell to the cell’s data type. rngSegment.TextToColumns Destination:=rngSegment, DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True End If Next ‘ Reset delimiter in TextToColumn dialogue to tab. Dim ws As Worksheet Set ws = rngToConvert.Parent Set rngToConvert = ws.Range(ws.Cells(ws.Rows.Count, ws.Columns.Count), ws.Cells(ws.Rows.Count, ws.Columns.Count)) rngToConvert.Value = “1” rngToConvert.TextToColumns Destination:=rngToConvert, DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True rngToConvert.ClearContents rngToConvert.Parent.UsedRange Application.ScreenUpdating = True End Sub Sub RefreshCells() ‘ ‘ RefreshCells Macro ‘ Refreshes selected cells by reentering formula bar text ‘ ‘ Keyboard Shortcut: Ctrl+r Dim formula As String For Each cell In Selection formula = cell.FormulaR1C1 cell.FormulaR1C1 = formula Next cell End Sub I have been using a macro which takes forever. Thank you for the tip, best of luck in your endeavors. Atanas Sub RefreshCells() ‘ ‘ RefreshCells Macro ‘ Refreshes selected cells by reentering formula bar text ‘ ‘ Keyboard Shortcut: Ctrl+r Dim formula As String For Each cell In Selection formula = cell.FormulaR1C1Local cell.FormulaR1C1Local = formula Next cell End Sub Thanks alot this solution works for me :) Regards Nawaz There are other comments containing macros as well, but those macros loop through each selected cell individually. That would be fine if only a handful of cells were selected, but it could take a long time if an entire column or more were selected. i can’t count how many times I’ve double-clicked through a column of data because it was a lot, but no so much and I needed it done. I have a column I need to refresh with numbers in the format similar to “01-20” and when we run the text to columns tool, it always formats these numbers as dates instead of text, even when text is checked. Any way around this? Thanks a lot buddy! Really happy we helped you solve the problem EB! Welcome to the site. groovyPost.com Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
