How to Refresh Cell Data After Applying Number Formatting in Excel
There’s a quirk with Microsoft Excel 2010 (and possibly other versions) where custom number formats don’t get applied to existing data. This quick fix can save you from the tedium of re-entering thousands of rows of data.
When entering numerical data in Excel, it helps to format it according to the type of data. Formatting a cell according to its data type—e.g., percentage, currency, date, or text—helps drive consistency and accuracy in displaying and working with your data. If you know from the get-go what format your data should be in, it’s relatively simple to choose a number format for the entire column before entering your data. Just select a column and choose a number format from the Number pane in the Home ribbon:
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.
145 Comments
Leave a Reply
Leave a Reply
andy
October 30, 2013 at 11:31 am
Thanks!! This was a huge help.
Stve
February 26, 2019 at 12:25 pm
Fabulous! Works like a champ on my 15K lines of data! ThaNKS!
Jolene
August 30, 2021 at 5:17 am
Thank you so much, you saved me hours!
tsepo
July 25, 2019 at 6:46 am
Thanks !
Lucie
September 17, 2019 at 11:39 am
OMG!!!! Thank you soooooo much!!!!
Mera P.
February 20, 2020 at 8:46 am
THIS.IS.AMAZING. You are a saint.
Raghavendra Gopalakrishnan
May 16, 2021 at 9:31 pm
Thanks a lot .. Awesome
Dan
February 20, 2014 at 7:14 am
Whoa! Where have you been all these years? :)
Payam
April 9, 2014 at 1:53 am
This is the best excel trick ever for us working with cvs and barcodes that get the +xx at the end.
Perfect. Hope you live a good life with many childrens and a goat for the milk. make cheese of the milk and sell it and live an even happier life.
thanks again. Im very happy, even though i don’t own a goat.
Alane
August 24, 2017 at 6:29 am
LOL best comment ever
TanMiLujan
August 29, 2014 at 11:21 am
This trick rocks!! Saved me hours of updating!
Patrick
September 10, 2014 at 3:35 pm
This problem has plagued me many times. Easy when there are only a couple cells, but a pain in the butt when there are many…until NOW! Thanks for the hack!!
Noortje
October 6, 2014 at 2:40 am
THANK you! You just saved me a few hours of tedious work!
zyndarius
October 16, 2014 at 5:52 am
This was indeed something very helpful.
Thank you.
TONY
October 16, 2014 at 4:21 pm
HUGE HUGE HELP…..
MANY MANY THANKS……
CHEERS.
TONY
Sharon Jose
November 13, 2014 at 10:16 pm
Thanks a lot for this wonderful trick.
Philip Egan
December 4, 2014 at 3:09 am
Excellent, thanks so much for sharing! :o)
Gary
December 17, 2014 at 9:02 am
I’ve been wondering how to fix this for years now, this works perfectly, thanks
Regine
March 4, 2015 at 8:29 am
WOW!!! Totally amazing…thank you so much for this step by step tutorial. You just saved me hours of mind numbing work. Have a blessed day…
matt
March 11, 2015 at 5:34 pm
This keeps making my excel crash. Any advice?
Amani
March 16, 2015 at 3:06 am
thanks from my heart , you really help me
Nicki
March 17, 2015 at 9:32 am
This has saved me hours – thank you so much! You have succeeded where my IT department failed. Over 118000 lines of data for 12 months, and this problem has been recurring. You are literally a life saver!
John
March 20, 2015 at 8:37 am
THIS IS AMAZING! Genius hack that is a huge time saver.
Peter
March 23, 2015 at 4:30 pm
thanks. What a weird thing – same problem with a formula in an adjacent column using isblank – would not execute until double clicked in each cell in column it was reading from. This was doing my head in. thanks
Rick
March 29, 2015 at 2:56 pm
Thank you for the solution!
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.
Frank
October 25, 2019 at 6:10 am
Thank you, Rick!
This helped me a lot :)
William
April 9, 2015 at 5:45 am
I went through all you suggested but the numbers entered as text will not convert to numbers, and of course autosum does not function. I only have this problem on one sheet other work sheets in the book work fine. It’s annoying cant find the solution to a known problem
Thank you in advance
Suzanne
September 2, 2015 at 8:45 am
Another hack for that problem is to use the copy values and multiply using 1. To do this enter 1 in any blank cell. Copy the cell using control c and then highlight the area with the numbers reading as text, use paste special and in the options choose value and multiply. This keeps the same number since multiplying by 1, and allows excel to see that it is a number.
Hayden
June 15, 2016 at 9:46 pm
Thank you Suzanne!
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!
Eric
May 30, 2018 at 11:53 am
Suzanne you are a life saver!
Raz
April 22, 2015 at 12:13 am
Wow.. one of the most useful hacks ever.. saved me tons of time and as someone else mentioned you have a good life there with tons of happiness and money and children and goat milk! :)
Susie
May 8, 2015 at 8:39 pm
THANK YOU! :) I knew there MUST be an easier way but never bothered to search for one before now. Thank you SO much!!! :D
wan
June 1, 2015 at 8:20 pm
U r my savior… this trick work like a charm… thanks… :)
Vali
June 8, 2015 at 11:47 pm
Nice, thank you!
Naresh Khurana
June 18, 2015 at 3:48 am
You rock buddy, Many Thanks!!
Dennis
June 20, 2015 at 6:54 am
Great tip, thank you – harbored over this issue countless times, but never again!
Piseth
July 10, 2015 at 12:11 am
You’re legend. It’s such a great help. I had tried many solutions; none worked, but yours.
Matt
July 21, 2015 at 2:12 am
Great hack. One addition that makes it do more… If you have text masquerading as a formula because the text happens to have “=” in as the first character then this hack can be adapted to make the “forumula” switch to text – tick the Text checkbox in the Text to Colums wizard. Cheers.
Chris
August 3, 2015 at 7:54 pm
Doesn’t seem to work in Excel 2013 when trying to get it to update and recognize the literal value ‘ that had been added to a column of values using formula.
Suzanne
September 2, 2015 at 8:46 am
Thank you!!! This saved me a huge amount of time as it was a date that was reading as text and nothing I was doing worked.
Sampson
September 18, 2015 at 10:50 am
THANK YOU SO MUCH!!!
Ken
October 2, 2015 at 7:33 am
Thank you! This is a great tip. For years I have always copied the range of cells that I want to convert, and pasted them in a text editor. Then I selected all in the text editor, copied, and pasted back to Excel. It works, but with your approach I can do it all within Excel.
Jeffrey Dixon
October 19, 2015 at 1:03 pm
Not to be a downer, and this is indeed helpful, but I find it weird that everybody is raving about a solution that takes opening a menu tab and fiddling with three dialog boxes to implement what Excel should just do automatically – why does Microsoft think you are changing the formatting if not to show the cell content in the new formatting?! If there is some reason why you might want it to apply only to newly entered cell content, I think that must be the exception and not the rule, and the default behavior should be to update. Just saying.
Rick
October 21, 2015 at 3:09 pm
Hi Jeffrey,
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.
Joe
October 19, 2015 at 4:55 pm
I had same issue except I was using numbers. the solutions I found had worked for me. I ended up finding the solution. Which was to select a range of cells and format the cells to text. Then paste the numbers in. I then could select the range and change the format.
Ken
October 22, 2015 at 9:58 am
It would be great if Excel automatically converted the numbers and dates for you in all instances, but it doesn’t. I find myself doing this conversion so often, and this solution works so well that I decided to turn it into a macro. I added to my Excel Addin, and exposed it as a button in the Quick Access Toolbar. I’m going to share it just in case anyone else finds it useful. I use it a lot.
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
Jeffrey Dixon
October 22, 2015 at 10:48 am
As for processing ranges / columns one at a time, how about caching them as a collection/array and then running a loop to process them serially?
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?
Ken
October 22, 2015 at 11:54 am
@Jeff: That’s a good idea: to break up the selected range(s) into chunks that Text-To-Columns can process, store them in an array, and process them in a loop. I’ll give that a try.
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…
Ken
October 22, 2015 at 1:05 pm
@Jeffrey: Thanks for the suggestion. I added the ability for the user to select any number of ranges in any shape or size. At first this seemed like it would be a problem if the user selected an entire ROW, but then I realized that the selection should be limited to the used range in the sheet anyway. Now it’s even simpler for me to convert those unwanted data types. I simply run the macro, press the Select All button in the corner, click OK, and I’m done.
‘ 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
Jeffrey Dixon
October 22, 2015 at 2:25 pm
Awesome, thanks for sharing!
SNO
January 29, 2020 at 2:16 pm
When I copy and paste this into a new macro I get an error of “Compile Error: Expected: End of statement” Any ideas on what I need to change to get this to work?
Krista
October 28, 2015 at 6:58 am
Thank you! This was very helpful!!
Jacques Joubert
November 12, 2015 at 1:59 am
My gratitude.
TA
November 16, 2015 at 10:21 am
this hint rocks. why cant MS figure this out automatically.
Jeffrey Dixon
November 18, 2015 at 7:54 am
Here is a simpler macro I just wrote and started using as a kind of “refresh” of selected cells, simulating what would happen if you clicked in each individual cell, moved the focus to the formula bar, and hit enter. I believe this also works for updating the formatting of the displayed cell value.
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
Er
November 19, 2015 at 8:10 am
Thank you thanks a million man God bless you!
Jeanette
December 2, 2015 at 4:48 pm
THANK YOU! I have struggled with this before. Your fix is brilliant!
Tom
December 11, 2015 at 12:34 pm
Thank you for that! Saved me lots of work
Ethan
December 11, 2015 at 1:07 pm
Thank you so much for this trick. This makes my life 1000X easier!
keriba
January 13, 2016 at 11:47 pm
Thank you
It really helps me
Lorraine
February 1, 2016 at 3:48 pm
Thank you so much, though I am also angry about this strange problem… I’ve been analyzing 80,000 rows of data for 2 years on a monthly basis using Excel 2010 with NO problems… Now I have experienced this problem for the first time in FORMULAS… the value coming up is the same in all rows (same as whatever initial FILL DOWN cell I choose, or whichever COPY/PASTE cell is at the origin)… It’s incredibly frustrating as it has NEVER happened before, and I can’t spend hours pressing ENTER into every single formula… Worst thing is, my cells are already formatted as General and the calculations are already automatic, so there is NO NO NO possible reason to have to validate the data in each cell one at a time… But thanks to your convert to columns, I can temporarily fix this problem… This would have affected my sales report numbers STRONGLY… so, thanks… but Microsoft, please, WTH…!!!
Justin
February 11, 2016 at 11:51 am
Thank you so much. This just helped me fix a spreadsheet with 3500 records. Saved me so much time!
Odice
February 23, 2016 at 5:57 am
I’ve been fighting against this for years. Thanks for the tip, it saved us a lot of time
Roz
March 8, 2016 at 6:53 am
Thanks. I’d fiddled around for ages trying to find a quick fix
nhe0na
March 15, 2016 at 12:35 am
Thank for your sharing. You’re awesome.
Misko
March 24, 2016 at 8:06 am
Extremely lame. There is no REFRESH button in Excel? Like the one in Windows Explorer when you delete a file from folder and then click ‘refresh’…
Atanas
March 25, 2016 at 11:00 pm
God bless you good man.
I have been using a macro which takes forever.
Thank you for the tip, best of luck in your endeavors.
Atanas
Scott Breerton
April 4, 2016 at 5:42 am
You’ve just saved me about 4 hrs! Many Thanks!
luigii1
April 21, 2016 at 6:24 am
This worked on my excel. I changed FormulaR1C1 to FormulaR1C1Local
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
Bob richardson
May 26, 2016 at 7:40 pm
Thank you!
Evaldas
June 3, 2016 at 1:10 am
Five stars discovery!!!!! Thanks!
Amit
June 8, 2016 at 5:47 am
Nice trick….very helpful. Thanks !!!
Sathish K
July 13, 2016 at 1:37 am
Excellent Trick.
Have been doing this manually for ages.. and this saved a lot of time
Thanks for your tip and keep rocking
Dipen
August 29, 2016 at 2:36 am
It was frustrating to see the formatting not getting reflected on the selected columns. This post helped me convert my data in the correct format and generate the desired graphs. Thank you for the explanation with image snapshots.
Nawaz
September 6, 2016 at 6:16 am
HI,
Thanks alot this solution works for me :)
Regards
Nawaz
Minh An
September 29, 2016 at 12:14 am
Thanks. This is best solution I was looking for
Eric
October 14, 2016 at 2:52 pm
Hell yeah! I wish I knew this 10 years ago! Thanks!!
Fulvio Civitareale
November 8, 2016 at 4:48 am
Thanks!!! Yours post helped me a lot!
Mia Lor
November 8, 2016 at 12:00 pm
Amazing!!! I have been looking for this solution for a long time.
RAM
November 10, 2016 at 12:12 pm
Works Great!! Saved me from the painful job of pressing “enter” in each cell to fix the format in a large data. Thanks.
Excelerated
November 23, 2016 at 12:29 pm
Thank you thank you thank you for the Text to Col trick… can’t tell you how many times I’ve manually clicked on a hundred cells.
DAVID RAJA
December 16, 2016 at 1:57 am
It works even for formulas also. Thank you very much
Laura
December 17, 2016 at 11:39 am
You just saved me SO much time. Wish I could go back and save all the time I spent tediously clicking on individual cells before I thought to Google a solution!
Rix59
January 3, 2017 at 2:56 pm
Neat trick, that’s why I LOVE internet!!
Old guy that was born with the RTFM mantra…..
Diggler
January 12, 2017 at 11:51 am
You just saved me so much time. Thanks!!!!
Boater
February 22, 2017 at 4:41 pm
Thanks,
This applies in a bunch of situations. A recordset was ignoring some cells that clearly had data in them. Applying this fix/hack before creating the recordset did the trick. Apparently ADO gets confused when data formats are inconsistent.
Kay
March 7, 2017 at 4:19 pm
Thank you!!!!
Kathy Osborn
March 22, 2017 at 1:59 pm
WOW, you saved me SO much time, thank you, thank you, thank you, thank you!
Mrs. ETT
May 15, 2017 at 11:18 pm
Just had 56,000 rows I needed updated, THANK YOU!
Vivek Wadhwani
June 1, 2017 at 2:31 pm
Thank you much for the detail instruction. I was able to save my 2 hrs.
Ramya Christy
June 28, 2017 at 12:12 am
Excellent fix! Thanks a ton!
chandra
July 8, 2017 at 9:20 pm
This trick is good for me.
But i need to do this for multiple columns.
Do i need to select one column at a time or is there different trick for that?
Ken
July 10, 2017 at 6:16 am
@chandra: You can try the macro I posted in the comments section on October 22, 2015. Check my third post on that day. The way it works is that you will be prompted by the macro to select any range where you want to apply text-to-columns, which can include multiple columns, and even discontinuous ranges. The macro will then loop through the selected columns and areas, and run text-to-columns in the way the original post describes.
I keep this code in an Addin that I created, and I put button in the Quick Access Toolbar that will run this macro as needed. I use it very often, and it makes it even more convenient to run rather than running text-to-columns manually every time the data conversion is needed.
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.
Darmella
July 24, 2017 at 9:47 am
Lifesaver!
Chandra
July 24, 2017 at 11:16 pm
Thanks. This helpsa lot
ChSeEm
August 21, 2017 at 10:43 am
Thanks for this is a great tip.. Saved me a huge time.
Surender Jain
August 28, 2017 at 12:56 am
This is a HUGE help! Have been struggling with date values and this solutions works quite well with it as well.
Daniel Luevano
September 27, 2017 at 10:07 am
+1 nice solution.
Matt
October 19, 2017 at 9:52 am
Thanks, but this really takes the fun out of pressing F2 enter F2 enter F2 enter a million times until your coworkers start wandering around searching for the source of the noise
Charlie
January 31, 2018 at 5:48 am
Jack, NOW it all makes sense… that is… when you look up “THE MAN!” in Websters Dictionary it simply says “Jack Busch” ;-)
Theerawat S
February 22, 2018 at 9:48 pm
You made my day!!! Thank you very much!
Ruby
February 26, 2018 at 8:56 am
Worked. Thanks for the detailed step by step.
T
April 15, 2018 at 5:54 am
Thank You, Great help!
Larry Dillard
April 30, 2018 at 4:27 pm
This post should be number #1 on Google results, it rocks. I’ve been searching for this solution for years.
CW
July 4, 2018 at 1:57 am
Whoever you are, I love you! I had 4700 rows of data to format.
Cara
July 18, 2018 at 11:46 am
I cannot tell you what a life saver this is. I have been beating my head against the wall trying to figure this out. Thank you!
Nathan
October 30, 2018 at 7:07 am
Thanks. You saved me 2,886 mouse clicks.
K.Martin
January 30, 2019 at 12:01 pm
THANK YOU!!!! HUGE HELP!
mark
March 4, 2019 at 12:35 am
I LOVE YOU!! you saved meh xD thank you so much!
Steve Krause
March 5, 2019 at 12:56 pm
Fantastic! Glad the gP crew were able to help you!
Bobby Woodrell
March 29, 2019 at 4:30 pm
Absolutely wonderful solution… Thank you so very much.
Grace
April 17, 2019 at 11:57 pm
This saved me so much time and energy. Thank you so much!
Alane
April 25, 2019 at 10:07 am
OH MY GOSH I LOVE YOU
George Overby
May 15, 2019 at 10:31 am
This hack as you call it, is something I’ve been searching for for years! and it was so simple(once you told us how).
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.
gabriele
June 17, 2019 at 7:25 am
…I got almost crazy about this issue and with this trick, I short it out !!! …Much apreciated !!!
Gabriele.
Allen
June 20, 2019 at 11:12 pm
This worked perfectly! Thank you very much for this trick.
Louis
July 30, 2019 at 1:03 am
THANK YOU for this info to solve this frustrating challenge.
Dil
July 30, 2019 at 2:46 pm
Definitely a life saver! Thanks so much!
Gibby
September 30, 2019 at 4:52 am
God, thank you. This was killing me
Francesco
October 10, 2019 at 9:37 am
Brilliant, thank you!
Angela Hueckel
November 12, 2019 at 6:17 pm
Agree with other comments and at the risk of being redundant, well-done. Just FYI, it works on Google Sheets as well, except you have to choose “Detect Automatically” and it’s magic. Heck, you win Excel. Thanks so much.
Christina
November 21, 2019 at 2:04 pm
You are my new best friend! I knew there had to be a simple way, but didn’t have time to look because I was too busy hitting the F2 and enter key over and over. Wow, so simple. Thank you!!
Joseph
December 5, 2019 at 12:44 am
This was great Thank you… just saved me about 5000 F2 entries!!!! :)
prtyu
January 28, 2020 at 6:28 am
this helped a lot. thanks so much. was stuck for a while
Vivek
January 29, 2020 at 2:15 am
Thank you!! Lots of love from UAE!
Brian
February 11, 2020 at 3:29 am
Thanks from Ghana!
As for Microsoft. Come on, if you give the user an option to apply a new format, then actually apply the format. We should not be expected to search the web for a hidden trick for such a basic function. Surely this issue can be fixed?
Dave
March 13, 2020 at 8:00 am
Great tip, but I’ve run into a weird scenario where it doesn’t work for me.
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?
Fasih
April 8, 2020 at 12:33 pm
you’re a life saver!
Thanks a lot buddy!
Pavan
April 9, 2020 at 2:31 am
Thank u so much buddy, it was really helpful. In fact this turned helpful in large to me as I deal with bundles of downloaded data.
Dean
April 19, 2020 at 10:18 pm
Fantastic! Tx
Crystalmoon
May 7, 2020 at 10:22 pm
OMG it’s works!!
Thank you so much for your sharing
RH
May 11, 2020 at 4:18 am
Massive Help. Thank you.
Kevin
May 18, 2020 at 7:52 pm
Thank you very much. I couldn’t get the dates to change format–without clicking in each one–as you mentioned. Now I can do it! Thank you so much!
Alice
May 29, 2020 at 12:22 pm
Just saved (several annoyed 10s of minutes) of my life!! Thank you :) :)
Amy M
January 5, 2021 at 5:21 pm
Good Lord, THANK YOU! This has been a thorn in my side for years! The Text to Columns hack is pure GENIUS. YES!
inigo
February 5, 2021 at 7:45 am
You are a saint. i wish you forever the best.
KevFrey
April 29, 2021 at 2:02 pm
Thanks so much! Much appreciated for publishing!
donna
October 29, 2021 at 5:41 am
THANK YOU!!!!! it works! So amazing!
EB
January 24, 2022 at 11:39 pm
You need a nobel peace prize. I HAVE BEEN LOOKING FOR THIS FOR OVER 20 YEARS.
Steve Krause
January 27, 2022 at 12:41 pm
We accept!
Really happy we helped you solve the problem EB! Welcome to the site.
groovyPost.com
Felipe
March 15, 2022 at 10:57 pm
Excellent solution. Thanks for sharing!
Brice
April 5, 2022 at 2:16 pm
This just saved me so much time and frustration – you are a lifesaver!
Ric
April 13, 2022 at 7:30 am
Great solution saved me a lot of time. Thanks a lot
HLM
August 31, 2022 at 9:48 pm
Oh!! It still working, thanks you very much and appreciate your help.
Heather
October 27, 2022 at 5:30 am
Thank you SO much! You’ve saved me hours hitting f2!
Thank you thank you thank you!