Apache OpenOffice Community Forum

Posted: **Fri Feb 14, 2014 11:24 am**

Hey there! Please help me out, I have one single cell containing comma separated text , e.g. "test1, test2, test3, test1, test4, test2, test5".
And I need macros that will return the same string without duplicates, i.e. "test1, test2, test3, test4, test5".

I've found VBA for Excel but I can't translate it to Open Office working script.
http://www.ozgrid.com/forum/showthread.php?t=69682

This one works in Excel but not in Calc:

Code: Select all

Function stringOfUniques(inputString As String) As String 
    Dim inArray() As String 
    Dim xVal As Variant 
    inArray = Split(inputString, ",") 
    For Each xVal In inArray 
        If InStr(stringOfUniques, Trim(xVal)) = 0 Then _ 
        stringOfUniques = stringOfUniques & Trim(xVal) & "," 
    Next xVal 
End Function

Any help would be must appeciated!

Posted: **Fri Feb 14, 2014 1:31 pm**

Works for me.

Posted: **Fri Feb 14, 2014 3:49 pm**

How do you use it?

If A1 contains "test1, test2, test3, test1, test4, test2, test5"
Putting "=STRINGOFUNIQUES(A1)" into any other string returns comma "," in my case.
Concerning the script itself, I opened My macros and dialogs.Standard and pasted it after the end of Main (empty sub).

Posted: **Fri Feb 14, 2014 4:04 pm**

Try this version:

Code: Select all

Function stringOfUniques(inputString As String) As String
    Dim inArray() As String
    Dim xVal As Variant
    Dim s As String
    inArray = Split(inputString, ",")
    For Each xVal In inArray
        If InStr(s, Trim(xVal)) = 0 Then _
        s = s & Trim(xVal) & ","
    Next xVal
    stringOfUniques = s
End Function

Posted: **Fri Feb 14, 2014 6:08 pm**

Thanks Villeroy, it worked. Even the first code works, it was the matter of saving all files plus cell refreshing. Experience gained, topic closed. ; )

Posted: **Fri Feb 14, 2014 6:12 pm**

eugenechabanov wrote:Thanks Villeroy, it worked. Even the first code works, it was the matter of saving all files plus cell refreshing. Experience gained, topic closed. ; )

The original version behaves very strange. When you change the referenced string, the old return value is concatenated with the new value. The function variable seems to be static. My fixed version works normally (and it should work with Excel too).

Posted: **Fri May 05, 2017 11:03 am**

This function was exactly what I was searching for today.

However I found a problem with the function, if you have a word that is already contained in a previous word.

For example

Code: Select all

Los Angeles, California, CA

will only return

Code: Select all

Los Angeles,California

because CA is already contained in California

I created a a quick and dirty solution for this problem by adding a comma to the InStr search:

If InStr(s, Trim(xVal) & ",") = 0 Then _

Code: Select all

Function stringOfUniques(inputString As String) As String
    Dim inArray() As String
    Dim xVal As Variant
    Dim s As String
    inArray = Split(inputString, ",")
    For Each xVal In inArray
        If InStr(s, Trim(xVal) & ",") = 0 Then _
        s = s & Trim(xVal) & ","
    Next xVal
    stringOfUniques = s
End Function

just in case anyone else needs this.

Apache OpenOffice Community Forum

[Solved] Remove duplicates in one text cell

[Solved] Remove duplicates in one text cell

Re: Remove duplicates in one text cell

Re: Remove duplicates in one text cell

Re: Remove duplicates in one text cell

Re: Remove duplicates in one text cell

Re: Remove duplicates in one text cell

Re: [Solved] Remove duplicates in one text cell