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.

Solution 1:

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.

Solution 2:

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.

Solution 3:

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