[VBA] How to Delete Only Specific Characters in a Cell (Using Characters().Delete)

2025年12月26日

Introduction

In this article, I will share how to delete only a specific part of a text string within a cell using VBA.

Background

I encountered a situation where I needed to extract or delete a specific range of characters from a string entered in a cell. At first, I tried using the Replace function, but it didn’t work as expected because it targets text content rather than position. Eventually, I found that the Characters().Delete method was the correct solution.

Actual Code

The following code deletes the specified part of the text.

Range("A1").Characters(1, 6).Delete

Code Explanation

`Range("A1")`

Specifies the target cell. In this case, it is cell A1.

`.Characters(Start, Length)`

This allows you to manipulate a specific part of the text within the cell.

Start: The position to start the operation.
Length: The number of characters to affect.

`.Delete`

This method deletes the specified range of characters.

Important Notes

1-Based Indexing: The Start parameter in the Characters object is 1-based. This means the first character is at position 1.
Example: In the code Characters(1, 6), the 1 indicates the start of the string, and 6 indicates the number of characters to delete.
Result: If cell A1 contains 12 characters, deleting the first 6 will leave the remaining characters starting from the 7th.

Why Replace didn’t work

Initially, I tried to remove the unnecessary string using the Replace function. However, Replace operates based on text matching, not position. Therefore, it was not suitable for this case where I needed to delete characters based on their specific location (e.g., “the first 6 characters”). By using the Characters().Delete method, I was able to accurately delete characters at the targeted position.

Summary

Use Characters().Delete if you want to delete only a part of the text in a cell based on position.
You can precisely specify the target range using Characters(Start, Length).
The Replace function is for text substitution/deletion based on matching content and is not suitable for position-based operations.

Through this process, I learned the technique of partially manipulating cell contents. I will continue to improve my VBA skills by choosing the right functions and methods for each purpose.