Extracting unique 7-digit string in a cell...Excel

Question

In excel, I want to extract unique 7 digits in a cell with a string of digits. After the first 7 unique digits are extracted (placed in an adjacent column), the next 7 unique digits begin after the last digit of the previous unique 7-digit string.

e.g.(I have inserted separations to see where the following string ought to begin)

120381634|782540251|996314825168993247556112006387453961284587863952145786329520048752123666520473015444

1st  (unique 7-digits) 1203864
2nd                    7825401

Welcome so StackOverflow, sweetypie. Please update your question to include a snippet of your code in your attempt to extract these digits in this manner. — Brett Caswell, Aug 25 '18 at 18:35
Unique digits mean no repeats e.g. ...123251705930 has 5 unique digits 1235709 — sweetypie, Aug 25 '18 at 20:31

score 3 · Accepted Answer · answered Aug 25 '18 at 18:46

This udf will allow you to set the length of unique characters per string and which value to return:

Function UniqueSeven(str As String, lngth As Long, k As Long) As String

Dim dict As Object
Set dict = CreateObject("scripting.Dictionary")

Dim test() As Variant
ReDim test(1 To Len(str) / lngth)

Dim j As Long
j = 1
Dim i As Long
For i = 1 To Len(str)
        If dict.Count < lngth And i < Len(str) Then
            On Error Resume Next
                dict.Add Mid(str, i, 1), Mid(str, i, 1)
            On Error GoTo 0
        Else
            Dim key As Variant
            For Each key In dict.keys
                test(j) = test(j) & dict(key)
            Next key
            j = j + 1
            dict.RemoveAll
            dict.Add Mid(str, i, 1), Mid(str, i, 1)
        End If
Next i

UniqueSeven = test(k)

End Function

You would use it thus:

With your string in A1, put this in a cell:

=IFERROR(UniqueSeven($A$1,7,ROW(A1)),"")

And copy down till you get blanks.

The second argument is the length of the string output. I used ROW(A1) as a counter so it will return 1,2,3,4,... as it is dragged down the column. It can be hard coded number also.

Extracting unique 7-digit string in a cell...Excel

1 Answers1