I have two strings like
str1= "[abc 1],[def 2],[ghi 3],[jkl 4],[mno 5]"
str2="[def 2],[mno 5]"
The strings in str2 [def 2],[mno 5] should be deleted or replaced with "" in str1. result will be
str1="[abc 1],[ghi 3],[jkl 4]"
I tried replace function but not working giving full string str1
strorg1 = Replace(str1, str2,"")
Try this (sorry for not being able to format as code by now)
Option Explicit
Sub Main()
Dim str1 As String, str2 As String
Dim str As Variant
str1 = "[abc 1],[def 2],[ghi 3],[jkl 4],[mno 5]"
str2 = "[def 2],[mno 5]"
str1 = "|" & str1 & "|"
For Each str In Split(str2, ",")
str1 = Replace(str1, str, "")
Next str
str1 = Replace(Replace(Replace(str1, ",,", ","), "|,", ""), ",|", "")
MsgBox str1
End Sub
This works i think:
Option Explicit
Sub gen()
Dim ReplaceList(1 To 5) As String
Dim str1 As String, strToReplace As Variant
Dim a() As String
Dim element As Long
str1 = "[abc 1],[def 2],[ghi 3],[jkl 4],[mno 5]"
ReplaceList(1) = "[def 2]"
ReplaceList(2) = "[mno 5]"
a = Split(str1, ",")
For element = UBound(a) To 0 Step -1
For Each strToReplace In ReplaceList
If a(element) = strToReplace Then
a(element) = ""
End If
Next
Next
str1 = Join(a)
Debug.Print str1
End Sub
edit, i don't have access to Access, I hope this works, if not it should set you on the right track.
Try this one :
Sub Macro1()
Dim str1 As String
Dim str2 As String
Dim strTemp As String
Dim strTemp2 As String
Dim strOut As String
str1 = "[abc 1],[def 2],[ghi 3],[jkl 4],[mno 5]"
str2 = "[def 2],[mno 5]"
strOut = str1
Do
strTemp = Application.WorksheetFunction.Search("]", str2)
strTemp2 = Mid(str2, 1, strTemp + 1)
strOut = Replace(strOut, strTemp2, "")
str2 = Replace(str2, strTemp2, "")
Loop Until str2 = ""
End Sub
It will parse your str2, cut pieces inside [] and remove it from str1 one by one.
The output is what you expected, using excel 2010.
