UNIQUE function

banana74 

While the UNIQUE function is designed to extract unique values, it's not directly applicable to extracting specific characters from strings. However, here are a few ways to achieve your desired result in Excel:

Method 1: Using MID and UNIQUE:

1. Extract characters 7-10: In a new column (e.g., B2), use the MID function to extract characters 7-10 from each cell in column A:

   Excel

   =MID(A2, 7, 4)

    

2. Apply UNIQUE to the extracted characters: Use the UNIQUE function on the new column with extracted characters to get unique values:

   Excel

   =UNIQUE(B2:B5001)

    

3. Paste the results: Paste the unique values to your desired sheet.

Method 2: Using FILTERXML and UNIQUE:

1. Create an XML string: Use the TEXTJOIN function to create an XML string with each character separated by a tag:

   Excel

   ="<root>" & TEXTJOIN("</char><char>", TRUE, MID(A2, {1;2;3;4;5;6;7;8;9;10})) & "</root>"
   

    

2. Extract characters with FILTERXML: Use the FILTERXML function to extract the desired characters based on their position:

   Excel

   =FILTERXML("<t>" & A2 & "</t>", "//t/char[position() >= 7 and position() <= 10]")

    

3. Apply UNIQUE to the extracted characters: Use the UNIQUE function on the column with extracted characters to get unique values.

4. Remove XML tags: Use the SUBSTITUTE function to remove the XML tags:

   Excel

   =SUBSTITUTE(C2:C5001, "<char>", "")

    

5. Paste the results: Paste the unique values without XML tags to your desired sheet.

Method 3: Using VBA:

1. Create a macro: Write a macro that iterates through each cell in column A, extracts characters 7-10 using Mid or Left, and stores the unique values in a separate array.

2. Paste the results: Use the macro to paste the unique values to your desired sheet.

Choose the method that best suits your comfort level and preferences. Remember to adjust the cell references and formulas to match your actual data and desired output.

banana74 

Let's say the texts are in A1:A5000 on Sheet 1.

In a cell on another sheet

=UNIQUE(MID('Sheet 1'!A1:A5000, 7, 4))