Solved Excel: remove characters with one hotkey

Whitebox / MINE
August 30, 2015 at 03:33:00
Specs: Windows 7, e6850/4GB
I use a system that spits out a spreadsheet every day, but the data in each cell has brackets around it.


Cell A1: [31434031478]
Cell A2: [31434031479]
Cell A3: [31434031480]

To make them easier on the eyes, I use Excel's inbuilt REPLACE function, and tell it to Replace All left brackets with nothing, then the right brackets with nothing. But that takes multiple keyboard commands/mouseclicks, and is grossly inefficient. The system itself CANNOT BE CHANGED and must include these brackets to ensure the numbers come out properly, so what I would like is to be able to press, say, F2, and have it automatically remove all instances of [ and ].

I've used AutoHotKey for certain hotkeyed shortcuts, but I don't know how to do this one. I asked on their forums, and they said that AutoHotkey is overly complicated for such a simple Excel macro, and "It might be easier to use a RegEx replace within Excel and create an Excel macro to run it. Or even without using the RegEx option of Excel's replace, just make an Excel macro to execute the two separate replaces that you currently do."

I tried googling this, but have found no "How to delete multiple characters in Excel with one key [SOLVED] thread, or similar. Can anyone help?

See More: Excel: remove characters with one hotkey

Report •

August 30, 2015 at 04:50:49
✔ Best Answer
Excel allows you to record a macro and assign the VBA code to a key combination.

Start the macro recorder, follow the manual steps to replace the brackets and then stop the recorder. When you press the key combination, Excel VBA will replay the recorded steps.

There is an often a need to edit the code and clean it up, but if your process is consistent and repeatable, the recorded code might be all that you need.

Let's us know if we can help further.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 30, 2015 at 07:47:44
My knowledge of VBA is not very extensive, but try this:

Sub MySearchReplace()
Cells.Replace What:="[", Replacement:=""
Cells.Replace What:="]", Replacement:=""
End Sub

See how that works for you.


Report •

August 30, 2015 at 12:01:55
This works great! AND opens up new possibilities!

A shame the hotkey has to be CTRL+something, and not just one key like F12, though.

Report •

Related Solutions

August 30, 2015 at 12:31:09
You can use a Command Button on the Quick Access tool bar if you wish.

Don't know what version of Excel your using, this is for 2007:

Open your workbook
Click the Microsoft Office button
Click on the Excel Options button to bring up the Excel Options dialog box (at the bottom of window)
Click on the Customize button in the left hand pane
The Customize the Quick Access Tool bar window
should appear.
In the Choose Command From drop down box
Select Macros
Scroll down the list of Macro till you find your Search & Replace macro
Select the Macro and then click the Add button in the middle
Your Macro should now appear in the Right Side box
At the bottom of the right side box, select Modify
In the pop up icon box, select an appropriate icon,
Click OK
Click OK

You can now just click on the Icon, which should be on the Quick Access tool bar, and your Macro will launch.


Report •

Ask Question