Be the first user to complete this post
|
Add to List |
VBA-Excel: String Functions – Replace()
Description:
The Replace() function returns a string after replacing a part of string with another substring. The no of replacements depends on the parameters provided.
Format:
Replace(strMainString, find, replace[, start[, count[, compare]]])
Arguments:
- strMainString
- Mandatory
- Type: String
- The string in which replacements to be made.
- find
- Mandatory
- Type: String
- Substring which needs to be found and replaced in the main string.
- Replace
- Mandatory
- Type: String
- Replacement substring
- Start
- Optional
- Type: Numeric
- Starting position in the main string from which the find and replace operation will start, if not provided, 1 is assumed.
- Count
- Optional
- Type: Numeric
- No of replacements to be made, if not provided, -1 is assumed which means all the possible replacements will happen
- Compare
- Optional
- Type: Numeric
- The type of comparison to find the string in the main string, like vbBinaryCompare ( Value =0), vbTextCompare (value=1).
Function FnReplace() Dim strMainString strMainString = "my name is sumit jain, and the name is sumit jain" MsgBox Replace(strMainString, "sumit", "XXXX") End Function

Note: Start, Count, Compare arguments are not provided that means all the possible replacements will happen.
MsgBox Replace(strMainString, "jain", "YYYY", 1, 1)
Note: Start = 1 and Count =1, means starting from position 1, Count =1 means only one replacement will be made.

MsgBox Replace(strMainString, "sumit", "AAAA", 1, 1, vbTextCompare)
Note: Start = 1 and Count =1, means starting from position 1, Count =1 means only one replacement will be made and use vbTextCompare as comparison

MsgBox Replace(strMainString, "sumit", "BBBB", 15, 1)
Note: Start = 15 and Count =1, means starting from position 15, Count =1 means only one replacement will be made and use vbTextCompare as comparison.

Also Read About Other String() Functions
INSTR() | InstrREV() | LCase()
Also Read:
- VBA-Excel: String Functions – Len()
- VBA-Excel : Strings Functions – Lcase
- VBA Excel – Looping Through a Range of Cells
- VBA-Excel: Fill Excel Range Values in a 2D Array
- VBA-Excel: String Functions – Trim()