Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
It'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 May 20 '13 at 14:58
8  
Are 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 May 20 '13 at 15:00
19  
Definitely look in to what Moses says. If you're doing anything 10,000 times manually then you're doing it wrong. –  Tanner May 20 '13 at 15:03
    
@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 May 20 '13 at 16:09
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 May 20 '13 at 16:25
show 3 more comments

6 Answers

up vote 12 down vote accepted

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.

share|improve this answer
    
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 May 20 '13 at 18:20
    
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 May 21 '13 at 9:02
3  
Beware 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 May 21 '13 at 19:24
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 May 22 '13 at 6:13
add comment

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.

share|improve this answer
1  
Thanks 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 May 20 '13 at 16:21
    
@DavidGard no problem. I also updated my answer with a third alternative which may work for you. –  Moses May 20 '13 at 17:29
3  
+1 ...why is there 10,000 rows of data that require manual cleaning? –  Icode4food May 20 '13 at 19:10
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.