Removing Specific Characters in Excel: Using SUBSTITUTE and TRIM Functions

スポンサーリンク
スポンサーリンク

How to Remove Specific Characters

This article explains how to remove specific characters from cells in Excel. Whether you need to remove particular strings or extra spaces, the SUBSTITUTE and TRIM functions are effective tools. Additionally, the Find and Replace feature offers a quick solution for direct modifications.

Removing Characters Directly in the Data

Using the Find and Replace dialog (Ctrl+H), you can easily remove specific characters by replacing them with a blank value. This works for selected ranges or the entire sheet.

Using the SUBSTITUTE Function to Remove Specific Characters

A B C
1 Original Text After Removing Characters
2 123-456-789 =SUBSTITUTE(A2, “-“, “”)
3 abc.def.ghi =SUBSTITUTE(A3, “.”, “”)

Steps

  1. Enter strings containing the characters you want to remove in column A.
  2. Use the SUBSTITUTE function in column B to remove the specific characters.
  3. For example, in B2, enter:
    =SUBSTITUTE(A2, “-“, “”) to remove hyphens.

Function Explanation

The SUBSTITUTE function replaces specified characters in a string with another character. By replacing with an empty string (“”), the target characters are effectively removed.

Results

If A2 contains “123-456-789”, B2 will display “123456789”. Similarly, if A3 contains “abc.def.ghi”, B3 will display “abcdefghi”.

Using the TRIM Function to Remove Extra Spaces

A B C
1 Original Text After Removing Extra Spaces
2 Microsoft     Excel =TRIM(A2)
3 Data       Processing =TRIM(A3)

Steps

  1. Enter strings with extra spaces in column A.
  2. Use the TRIM function in column B to remove excess spaces.
  3. For example, in B2, enter:
    =TRIM(A2) to clean up extra spaces in A2.

Function Explanation

The TRIM function removes all leading, trailing, and extra spaces between words, leaving only single spaces.

Results

If A2 contains ” Microsoft     Excel “, B2 will display “Microsoft Excel”. Similarly, if A3 contains ” Data     Processing “, B3 will display “Data Processing”.

Conclusion

The SUBSTITUTE function is ideal for removing specific characters, while the TRIM function effectively eliminates extra spaces. These tools enable efficient string cleanup and formatting.