Geek Answers Handbook

Excel VBA - delete part of a row

I am trying to delete part of a string. For instance, mystring="site, site text, sales "

I want to remove the " site " from mystring. My desired result: "site text, sales

I use this line of code:

s1 = Replace(mystring, "site", "")

but i get "text, sales"

I am not sure how to do this, and I am very grateful for your help!

+4
source share
4 answers
replace("site, site text, sales ","site, ","",1,1)

You can also send the starting position as a parameter, and then the number of times you want to replace ... (default is -1)

+6
source

There are many different options here:

Just adding coma to the search bar for replacement and use Trimto get rid of spaces:

s1 = Trim(Replace(mystring, "site,", ""))

, ( "1" - , - )

s1 = Trim(Replace(mystring, "site,", "",1,1))

/ , , , ...

TempStart = Left(mystring, InStr(1, mystring, "site") + Len(mystring) + 1)
TempEnd = Replace(mystring, TempStart, "")
TempStart = Replace(TempStart, "site", "")
mystring = CStr(TempStart & TempEnd)
+1

You can also use the VB MID function for the user as follows:

Mystring=Mid(myString, 6)

the output will be "text site sales

Just indicate the number of characters you want to delete, in the part number .

+1
source

In my case, I wanted to delete the part of the lines that was between "[" and "]". And the following code worked fine.

So, with the original row in column A (and the 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
+1
source

More articles:


All Articles