Can you edit the contents of an Excel cell without having to double click it?

I have a list of about 10,000 name/addresses that I have to manually cleanse in Excel (fun!). As you can imagine, having to double click to enter almost every cell to edit it is a drag. And it's frustrating that if you are even slightly near the top/bottom of the cell, you end up at the top/bottom of the worksheet!

Is there a way of telling Excel to stay in 'edit mode', so that when I click on a cell (or better yet, scroll down to it), it automatically allows me to edit the content? I can't just start typing, as that overwrite the whole cell, when often times I just need to edit 1 or 2 characters. Thanks.

David Gard

Posted 2013-05-20T14:55:51.617

Reputation: 1 368

1It's a dirty workaround, but what about converting it to CSV, splitting with commas and editing with Notepad or any other text editor you like? – gronostaj – 2013-05-20T14:58:38.523

9Are you sure there is no script or function that can do this "cleanse" for you? When faced with a similar situation, I took all the data from excel and successfully ran it through a small regex script. – Moses – 2013-05-20T15:00:14.587

19Definitely look in to what Moses says. If you're doing anything 10,000 times manually then you're doing it wrong. – Tanner Faulkner – 2013-05-20T15:03:15.557

@Moses - Unfortunately no. These entries come from a database, and it's mainly the 'Address Name' field that is mucked up, with the data being very inconsistent. Some say things like 'Smith John Mr & Smith Joan Mrs', which should be 'Mr & Mrs J Smith', while others are blank, etc. This, basically, is the start of a very big project. – David Gard – 2013-05-20T16:09:58.747

Can you tell us what the manual cleanse entails? It may be that some of that could be automated instead – datatoo – 2013-05-20T16:21:30.520

1@datatoo - Names need to be but in a readable way, so that they can be used on addresses. Due to multiple databases being merged in to one over the years (and people being lazy), the data is inconsistent within the cell. I have no reference cell to go by, and once this part of the project is complete, I will be splitting the data in to Salutation, Initials, Surname, etc. It's at this point that I can start to use automation. – David Gard – 2013-05-20T16:25:14.173

A python script for the logic + python NLTK should be able to handle all of the human variations of name entry. Use that to get things into a syntax-free canonical form, and then use the python script to generate the name column in whatever format you want. Or if you know another scripting language, use that instead of python. But python's NLTK does a great job at chunking text. – Clayton Stanley – 2013-05-20T19:50:51.080

@David Gard There are things you can do like Make all Names Uppercase, do countifs on those name fields or concatenated fields to find duplicates, then reconvert to Proper names again, then Advanced filter for uniques. You may eliminate a huge portion of the work. Using formulas or vba – datatoo – 2013-05-22T00:19:13.133

Answers

You can do this with the following macro. It will cause your Excel to behave as if you hit F2 every time you change selections on the spreadsheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    SendKeys "{F2}"
End Sub

Hit Alt+F11 to bring up the VBA editor, expand the left tree view until you see the worksheet you want, double click on that worksheet, and paste the above code in there.

You can disable this simply by adding ' in front of the line of code to make it a comment like:

'SendKeys "{F2}"

Or, I guess, deleting it completely.

This also works very well if you are using enter to move cells (which I suspect is your main intent) as it will start editing them immediately and let you move much faster.

enderland

Posted 2013-05-20T14:55:51.617

Reputation: 1 788

Btw, thanks for asking this, I'm going to use this myself a fair bit as this is way better for lots of data manipulation! – enderland – 2013-05-20T18:20:37.820

Perfect! This is exactly what I want for this workbook, thanks so much. The only slight weirdness that I have noticed is that if I use 'Shift+Tab' to go right-left through cells, I have to repress 'Shift' every time, as opposed to just being able to keep it held down. – David Gard – 2013-05-21T09:02:42.607

3Beware that using VBA in this way has its drawbacks. Most notably, it will eliminate any Undo functionality on the sheet. So, if you screw up and delete some data accidentally, you'd better have a backup. Also, arrow keys no longer work for navigating the sheet. – Excellll – 2013-05-21T19:24:02.033

3@Excellll has an excellent point; your Undo functionality is lost. For such a simple macro, it doesn't seem worth it. Consider using AutoHotKey instead - you can tell it to press F2 after Enter, Shift-Enter, Tab or Shift-Tab. (And you can make sure it only works in Excel too.) – benshepherd – 2013-05-22T06:13:58.567

You can step into edit mode from the keyboard via the F2 key, or you could create a VB macro that has a popup/prompt. While strictly speaking there is no option that I know of to stay in F2 mode, you could write a small script into Excel which goes down one row and steps into the cell (then have this macro replace your "return/tab" to new col).

Edit: Another option would be to manage these edits through a different program with a more accessible user-interface for this kind of data. For instance, push all of this data into a database program like Access, then manage the data fields through their UI.

However, all of this avoids the main problem, which is why is there 10,000 rows of data that require manual cleaning? If writing a single script to rule them all is too cumbersome, why not break down the table into meaningful groups and tackling them that way? It is hard to imagine that 10,000 data fields must be manually processed and there is no workaround.

Moses

Posted 2013-05-20T14:55:51.617

Reputation: 870

1Thanks for the F2 tip, that does help. I'll look to overwrite the return/tab keys for this spread sheet with that function. Thanks. – David Gard – 2013-05-20T16:21:58.927

@DavidGard no problem. I also updated my answer with a third alternative which may work for you. – Moses – 2013-05-20T17:29:43.220

3+1 ...why is there 10,000 rows of data that require manual cleaning? – Icode4food – 2013-05-20T19:10:49.273

The only way I know is by pressing F2. That works every time, until you press Return / Enter to move off the current cell.

Kevdog777

Posted 2013-05-20T14:55:51.617

Reputation: 437

A possible answer would be a program called "Middle Mouse Button (or Wheel) to Doubleclick" it is available on codeproject I do not know if I should post links here but I'm sure google can be your friend. what it does is while it is active you can single click your middle mouse button and it will send a double left click to your computer.

Dan

Posted 2013-05-20T14:55:51.617

Reputation: 41

If you are editing the cells without formula or something that can only be done in Excel, just copy these cells and paste them as normal columns or table into a Word document. Edit the data in those table freely and then copy back right to the original place in Excel.

Jack

Posted 2013-05-20T14:55:51.617

Reputation: 1

Pressing "F2" is possibly the best choice, though it won't remain in edit mode. If you know the edits you want to perform, the find/replace (Ctrl + F) is also very useful and may save you a lot of time. Additionally, you can find a list of over 200 excel keyboard shortcuts here.

Carlos Manta

Posted 2013-05-20T14:55:51.617

Reputation: 68

select the entire column, and use find and replace. just put an empty space on both find and replace then it will update- like this you can fool excel and it works

excel

Posted 2013-05-20T14:55:51.617

Reputation: 1

Try the =concatenate formula. Join information of two cells together. Then you could copy and paste values.

Luke

Posted 2013-05-20T14:55:51.617

Reputation: 1

Check the parameters of format cell...if the cell has a check mark on hidden on the protection tab, it wont give you the option to edit on the written text of the cell. check box should be unchecked in order to see the content of the box and edit it as you need it.

user500030

Posted 2013-05-20T14:55:51.617

Reputation: 1

This doesn't really answer what was asked in the question. With a little more rep, you will be able to add helpful information in a comment. – fixer1234 – 2015-09-21T19:22:57.287

This was the easiest solution that worked for me.

http://ccm.net/forum/affich-172180-unable-to-change-date-format-in-excel

First change your format to the specific date that you want for your column of data.
With the column selected, select "Copy"
Open notepad (NOT WORD, it has to be notepad)
Paste your data into notepad.
In notepad, press CTRL + A (selects all cells) then press CTRL + C (copies data)
Paste data back into Excel.
It will say something about size not being the same, paste it anyways.

Michael LaPalme

Posted 2013-05-20T14:55:51.617

Reputation: 1