Excel VBA- remove part of the string

Question

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!

Answer 1

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)

Answer 2

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)