18

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!

R3uK
  • 14,417
  • 7
  • 43
  • 77
phani
  • 507
  • 2
  • 6
  • 18

4 Answers4

20
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)

Adrian
  • 373
  • 2
  • 10
  • For the record, follows the parameters of replace: `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 String` – Adriano P Jul 03 '17 at 15:44
5

There 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)
R3uK
  • 14,417
  • 7
  • 43
  • 77
3

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.

Anarach
  • 440
  • 2
  • 16
  • 35
3

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
  • Good but better to use InStrRev instead of InStr and Check "]" is the last character or not. – fmnijk Nov 18 '20 at 04:19