-1

I am trying to create an Excel VBA that would delete only a specific part of the cell in only one column.

In Column A, I have a directory values:

For example:

Directoryof K:\data\Admin\

What I would like to do is remove the "Directoryof" from all the cells in column A and leave only the remaining text that follows it.

Community
  • 1
  • 1
Cory Gegg
  • 1
  • 1
  • 1

3 Answers3

1

You could use something like text to columns, fixed width, and split the columns after Directoryof and then copy/paste the values back into column A.

I'm not sure if there's a method to do this without a helper column without VBA. If you can afford to use a second column, you can also use =LEFT(Cell, # of characters) assuming that the part you want to strip off is always "Directoryof" and then copy/paste values back into column A.

shA.t
  • 16,580
  • 5
  • 54
  • 111
KFichter
  • 773
  • 4
  • 15
1

You could probably use regex to accomplish what you are going for. Regular Expressions are often used for finding patterns. If all of your follows the same format, you could break your strings apart into two capture groups with something like:

(.+)([A-Z]:\\.+)

https://regex101.com/r/uD4uJ0/2 <-- this will show you your capture groups

Edit: I updated this link, sorry, originally had the wrong one.

This here How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops will show you how to split up capture groups if you are interested.

Community
  • 1
  • 1
Scott T
  • 233
  • 2
  • 5
  • 14
1

To create a macro to perform the above follow the below steps:

  1. Click the "Developer" tab on the top menu.
  2. You will find an option "Record Macro".

  3. Click the Record Macro ->
    a. A dialog box appears, give your macro a name b. Shortcut key (if you want) can give by pressing (shift and any key such as
    letters) c. Store macro in : This workbook (this allows your macro to run on this sheet).

  4. Click on "Use Relative References".

  5. Once you are done, just perform the delete operation ( by removing the portion you do not want) on one of the column so that the macro may record the process which you are performing.

  6. Once done, below at the lowest pane you will find Stop Macro option (a small blue square box). Click it to stop the recording of the macro.

  7. Now you are ready with a macro to replicate the same without you performing the operation.

  8. Just goto any other column where you want to perform the operation and click on "Macro" option on the developer tab and then click on your created marco, and you will see the magic happen.

Sudhanshu Agrawal
  • 412
  • 3
  • 9