VBA: keep only one searched string.

[santa1234]

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.

[DocAElstein]

@ 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

[santa1234]

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

[DocAElstein]

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)

 #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)

 "#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."

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

---

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

---

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