Remove specific characters from string
To remove specific characters from a string you need to apply the Excel SUBSTITUTE function
Example: Remove specific characters from string
|
=SUBSTITUTE(B5,C5,"")
|
The formula uses the Excel SUBSTITUTE function to remove the specified character (4) from the selected string.
|
SubRemove_specific_characters_from_string()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to remove number 4 from the selected string
ws.Range("D5") = Application.WorksheetFunction.Substitute(ws.Range("B5"), ws.Range("C5"), "")
ws.Range("D5") = Application.WorksheetFunction.Substitute(ws.Range("B5"), ws.Range("C5"), "")
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
String: Ensure that the string from which you want to remove characters is captured in cell ("B5").
Characters to Remove: Ensure that the characters that you want to remove from the selected string is captured in cell ("C5").
Worksheet Name: Have a worksheet named Analyst.
String: Ensure that the string from which you want to remove characters is captured in cell ("B5").
Characters to Remove: Ensure that the characters that you want to remove from the selected string is captured in cell ("C5").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
String: Select the string from which you want to remove characters by changing the cell reference ("B5") to any cell in the worksheet that contains the string and doesn't conflict with the formula.
Characters to Remove: Select the characters which you want to remove from the specified string by changing the cell reference ("C5") to any cell in the worksheet that contains the characters that you want to remove and doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
String: Select the string from which you want to remove characters by changing the cell reference ("B5") to any cell in the worksheet that contains the string and doesn't conflict with the formula.
Characters to Remove: Select the characters which you want to remove from the specified string by changing the cell reference ("C5") to any cell in the worksheet that contains the characters that you want to remove and doesn't conflict with the formula.
EXPLANATION
To remove specific characters from a string you need to apply the Excel SUBSTITUTE function.
To remove specific characters from a string you need to apply the Excel SUBSTITUTE function.
FORMULA
=SUBSTITUTE(text, old_text, "")
=SUBSTITUTE(text, old_text, "")
ARGUMENTS
text: The string from which to subtract characters.
old_text: The existing characters to replace.