Duplicate text

[Sefty]

Hello, i need help formula to check the same character, for example
1. Jumlah desa terinfkesi Malaria
2. Desa terinfeksi malaria

3. Tingkat Partisipasi Angkatan Kerja
4. Tingkat Partisipasi Angkatan Kerja Perempuan
just need to check part of the sentence

Thanks

 

[SunnyAlv]

Like this ?
I don't know exactly what result you want

reference Cell B1 & C1

=LET(dup,B1:C1,data,PROPER(A2:A6),IFERROR(MID(data,FIND(dup,PROPER(data)),LEN(dup)),""))

 

[Sefty]

Like this ?
I don't know exactly what result you want

reference Cell B1 & C1

=LET(dup,B1:C1,data,PROPER(A2:A6),IFERROR(MID(data,FIND(dup,PROPER(data)),LEN(dup)),""))

View attachment 107459

I want to mark if it is the same in the highlights, for example red, as if using conditional formatting.
If the conditional formatting must be exactly the same.
But if it's just a fragment of a sentence, check the 3 same sentences to see if there's a formula?

 

[SunnyAlv]

Try with VBA

coba ini

Sub Checkformula()
On Error Resume Next

    Dim text As Variant
    Dim rng As Range
    
    Title = "Check Kalimat"
    text = InputBox("Ketikan Teks nya", Title)
    
        If StrPtr(text) = 0 Then
        Exit Sub
    ElseIf text = NullString Then
        Exit Sub
    Else
    
    Set Data = Application.Selection
    Set Data = Application.InputBox("Select Range to Check then click OK:", Title, Data.Address, Type:=8)
    
    For Each rng In Data
        If rng.Value Like "*" & text & "*" Then
            rng.Interior.Color = vbRed
        End If
    Next
End If
End Sub

and

But if it's just a fragment of a sentence, check the 3 same sentences to see if there's a formula?

D1 fill with 3 character according to the picture above

=LET(dup,D1,data,PROPER(A2:A6),IFERROR(MID(data,FIND(dup,PROPER(data)),LEN(dup)),""))

 

[Sefty]

Try with VBA

coba ini

Sub Checkformula()
On Error Resume Next

    Dim text As Variant
    Dim rng As Range
   
    Title = "Check Kalimat"
    text = InputBox("Ketikan Teks nya", Title)
   
        If StrPtr(text) = 0 Then
        Exit Sub
    ElseIf text = NullString Then
        Exit Sub
    Else
   
    Set Data = Application.Selection
    Set Data = Application.InputBox("Select Range to Check then click OK:", Title, Data.Address, Type:=8)
   
    For Each rng In Data
        If rng.Value Like "*" & text & "*" Then
            rng.Interior.Color = vbRed
        End If
    Next
End If
End Sub

and

D1 fill with 3 character according to the picture above

=LET(dup,D1,data,PROPER(A2:A6),IFERROR(MID(data,FIND(dup,PROPER(data)),LEN(dup)),""))

Wow keren, syntax macro excelnya itu bisa buat find jga yaa
tapi maaf jadinya gw kerjain dgn cara pakai Substitute, karna kalau pake macro pun harus tau kata kunci apa aja biar tau hal apa aja yang mau dicari kesamaannya, walaupun pakai substitute jga ini masih kurang efektif karna nantinya masih perlu manual.

Makasih banyak atas responnya dan bantuannya

 

[Fluff]

@Sefty Please post to this forum in English only.

 

[Sefty]

I’m terribly sorry, what I did was wrong

 

[Fluff]

Can you re-post your reply in English.

 

[Sefty]

Try with VBA

coba ini

Sub Checkformula()
On Error Resume Next

    Dim text As Variant
    Dim rng As Range
   
    Title = "Check Kalimat"
    text = InputBox("Ketikan Teks nya", Title)
   
        If StrPtr(text) = 0 Then
        Exit Sub
    ElseIf text = NullString Then
        Exit Sub
    Else
   
    Set Data = Application.Selection
    Set Data = Application.InputBox("Select Range to Check then click OK:", Title, Data.Address, Type:=8)
   
    For Each rng In Data
        If rng.Value Like "*" & text & "*" Then
            rng.Interior.Color = vbRed
        End If
    Next
End If
End Sub

and

D1 fill with 3 character according to the picture above

=LET(dup,D1,data,PROPER(A2:A6),IFERROR(MID(data,FIND(dup,PROPER(data)),LEN(dup)),""))

Wow, that's impressive the Excel macro syntax can do finds too
but sorry, so I did it using Substitute, because even if I use macros, I have to know what keywords to find out what things I want to look for similarities with, even though my data is more than 1000, even if I use Substitute, it's still less effective because later I still need to do it manually.
Thank you very much for your response and help