VBA: keep only one searched string.

VBA: keep only one searched string.
The list of search stings is given in sheet 2.
In sheet1, with VBA check if a given string is found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with multiple column data in multiple rows as well as in a CSV or text file.

@ susan santa 12345 et al

You continue to post short badly explained questions, and either ignore or don’t understand the various things I have said to you.

My best guess is that you are either
_ a Bot,
_ a total idiot,
_ just trying your luck at getting Homework questions answered quickly
_ just trying your luck at answering someone else’s questions and have no idea or interest in them yourself
_ deliberately trying to be a pain in the arse.

I will probably delete, close , ban you, or some combination in a few days to tidy the place up a bit, unless you improve somehow

Alan

Attached in Post #1 a reduced size sample with a Before ( Input) and an After (Output).

Hello again.

This will be a very similar idea to the last solution I just did for you:
https://excelfox.com/forum/showthrea...6728#post16728

The difference is that we check for duplicate strings and remove those.

As an example I will show you how to remove duplicates of " searched string" from cell A2, ( and put result in cell A3 )

Before (cell A2)

Code:

---

#VBA: keep only one searched string searched string searched string searched string.
#VBA: keep only searched string one searched string.
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file.

---

After running macro below (cell A3)

Code:

---

"#VBA: keep only one searched string.
#VBA: keep only one searched string.
The list of search stings searchedCSV string searched string is given in sheet 2.
In sheet1, with #VBA check if a given string is CSV searched string found more than once in a paragraph/line. If found more than once then keep only one.
I want to do this both in an excel sheet with CSV searched string multiple column data in multiple rows as well as in a CSV or text file."

---

Code:

---

Sub CleanUpCellA2() '    https://excelfox.com/forum/showthread.php/2819-VBA-keep-only-one-searched-string?p=16719&viewfull=1#post16719      https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears?p=16718&viewfull=1#post16718
Rem 0 worksheet data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Rem 1 We Split that text in a cell by the line separator, to give us an array where each element contains the text of a paragraph/ line.
Dim Celtxt As String: Let Celtxt = Ws1.Range("A2").Value2
Dim SptTxt() As String: Let SptTxt() = Split(Celtxt, vbLf, -1, vbBinaryCompare)
Rem 2  Check each element content for duplicated strings, and remove them
Dim Cnt As Long, NewStr As String
    For Cnt = 0 To UBound(SptTxt())
    Dim Pos1 As String: Let Pos1 = InStr(1, SptTxt(Cnt), " searched string", vbBinaryCompare)
        If Pos1 > 0 Then
        'If InStr(1, SptTxt(Cnt), "paragraph/line", vbBinaryCompare) = 0 And InStr(1, SptTxt(Cnt), "searched*string", vbBinaryCompare) = 0 And InStr(1, SptTxt(Cnt), "#VBA", vbBinaryCompare) = 0 Then
            Do While InStr(Pos1 + 1, SptTxt(Cnt), " searched string", vbBinaryCompare) > 0 ' I will keep doing this  Do While Loop  whilst I find another  " serched string"
            Let SptTxt(Cnt) = Replace(SptTxt(Cnt), " searched string", "", 1, 1, vbBinaryCompare)
            Let Pos1 = InStr(Pos1 + 1, SptTxt(Cnt), " searched string", vbBinaryCompare)
            Loop ' While InStr(Pos1 + 1, SptTxt(Cnt), "searched string", vbBinaryCompare) > 0
           
        Let NewStr = NewStr & SptTxt(Cnt) & vbLf
        Else
        End If
    Next Cnt
 Let NewStr = Left(NewStr, Len(NewStr) - 1)
Rem 3 Output
 Let Ws1.Range("A3").Value2 = NewStr
End Sub

---

---

Quote:

---

Originally Posted by santa1234

I want to do this both in an excel sheet with multiple column data

---

You will need to do the basic coding For each cell with data in it, using something like a For Each cell _ Next loop

---

Quote:

---

Originally Posted by santa1234

I want to do this ..... as well as in a CSV or text file.

---

One way to do this would be to import the text file into
an Excel worksheet
, or
a VBA array of data
, do the necessary on that excel worksheet
, then export the modified data to a text file.

Something similar to what I showed you here
https://excelfox.com/forum/showthrea...-a-space-found
https://excelfox.com/forum/showthrea...ll=1#post16696



Alan

---

VBA keep only one searched string.xls https://app.box.com/s/hjytoyz22uv0v1072cv855bgnem9n6fx