29

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.

8
  • 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, 2013 at 14:58
  • 9
    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, 2013 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.
    – rtf
    May 20, 2013 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, 2013 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, 2013 at 16:25

10 Answers 10

20

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.

4
  • 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, 2013 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, 2013 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, 2013 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.) May 22, 2013 at 6:13
20

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.

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

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

2

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.

0

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.

0

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

0

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

0

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.

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
    Sep 21, 2015 at 19:22
0

This was the easiest solution that worked for me.

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

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

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .