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/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears?p=16728#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 dataYou 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/showthread.php/2817-Make-all-text-file-content-in-one-line-if-a-space-found
https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16696&viewfull=1#post16696



Alan






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