CODEWITHSUNDEEP
vbaexcel
phani
phani

Reputation: 613

Excel VBA- remove part of the string

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!

Upvotes: 19

Views: 159331

Answers (4)

user8608712
user8608712

Reputation:

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

Upvotes: 3

R3uK
R3uK

Reputation: 14537

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)

Upvotes: 5

Anarach
Anarach

Reputation: 458

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.

Upvotes: 3

Adrian
Adrian

Reputation: 373

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)

Upvotes: 20

Related Questions