I am trying to delete part of the string. For example
mystring="site, site text, sales "
I want to remove 'site' from mystring. My required output is "site text, sales"
I use this line of code :
s1 = Replace(mystring, "site", "")
but i am getting "text, sales"
I am not sure how to do this and I'd really appreciate your help!
replace("site, site text, sales ","site, ","",1,1)
You can also send as a parameter the start position and then the number of times you want to replace... (the default is -1)
Public Function Replace( ByVal Expression As String, ByVal Find As String, ByVal Replacement As String, Optional ByVal Start As Integer = 1, Optional ByVal Count As Integer = -1, Optional ByVal Compare As CompareMethod = CompareMethod.Binary ) As StringThere are a lot of different options here :
Just by adding the coma in the search string to be replaced and use Trim to get rid of spaces :
s1 = Trim(Replace(mystring, "site,", ""))
Specify the number of time you want the string to be replaced (first "1" is the start, the second for the number of replacements)
s1 = Trim(Replace(mystring, "site,", "",1,1))
Or the hard/bad way, to decompose your string in two pieces after the first occurence and then recombine to get result...
TempStart = Left(mystring, InStr(1, mystring, "site") + Len(mystring) + 1)
TempEnd = Replace(mystring, TempStart, "")
TempStart = Replace(TempStart, "site", "")
mystring = CStr(TempStart & TempEnd)
You can also user VB's MID function like this:
Mystring=Mid(myString, 6)
output will be "site text, sales"
Just specify the number of characters you want to be removed in the number part.
In my case I wanted to remove the part of the strings that was between "[" and "]". And the following code worked great.
So With original string in column A (and solution in column B):
Sub remove_in_string()
Dim i, lrowA, remChar As Long
Dim mString As String
lrowA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrowA
mString = Cells(i, 1).Value
If InStr(mString, "[") > 0 Then
remChar = InStr(mString, "]") - InStr(mString, "[") + 1
Cells(i, 2).Value = Left(mString, Len(mString) - remChar)
ElseIf InStr(mString, "[") = 0 Then
Cells(i, 2).Value = Cells(i, 1).Value
End If
Next
End Sub
