'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find Duplicate Excel Cells Longer Than 255 Characters
Question details
The user needs to find duplicate text strings that exceed 255 characters in Excel because standard duplicate-checking formulas are failing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing data within a large dataset to identify duplicate text entries where cell contents are extremely long.
- Observed behavior
- Using the COUNTIF function to compare duplicate text strings longer than 255 characters returns a #VALUE! error.
Identify the specific target cell you want to check for duplicates and determine the exact data range you need to compare it against.
Use an EXACT Array Formula to Count Duplicates
Bypass the 255-character limit of COUNTIF by combining the SUM and EXACT functions inside an array formula.
The COUNTIF function has a known limitation in Excel where it cannot process text strings longer than 255 characters, resulting in a #VALUE! error. The EXACT function, however, can compare text strings of any length. By wrapping EXACT inside a SUM function as an array formula, you can check an entire range of data at once for exact matches.
Click on a blank cell where you want the total count of duplicates to appear.
Type the formula =SUM(--(EXACT(T1232, T2:T5000))) into the formula bar. Replace 'T1232' with the cell containing your long text, and 'T2:T5000' with your actual data range.
Do not just press Enter. Instead, press Ctrl+Shift+Enter simultaneously on your keyboard. Curly braces {} will automatically appear around the formula, indicating it is calculating as an array.
Use WPS Spreadsheet to Handle Complex Formulas and Long Text
WPS Spreadsheet fully supports advanced array formulas like EXACT and SUM, allowing you to seamlessly process complex data, find long text duplicates, and manage large datasets efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your long text data.
- 2. Input the exact formula: Select a blank cell and enter =SUM(--(EXACT(target_cell, data_range))).
- 3. Calculate the array: Press Ctrl+Shift+Enter to execute the array formula and instantly find your duplicates.
Frequently Asked Questions
Why does COUNTIF return #VALUE! for my text?
Functions like COUNTIF and COUNTIFS are inherently limited to evaluating criteria up to 255 characters long. If any cell referenced in the criteria exceeds this limit, the function will immediately output a #VALUE! error.
What does the double dash (--) do in the array formula?
The double unary operator (--) converts the TRUE and FALSE boolean values returned by the EXACT function into 1s and 0s. This allows the SUM function to mathematically add them together to give you a total count.
Can I use the MATCH function instead of COUNTIF for long strings?
Yes, you can use a formula combination like =MATCH(TRUE, EXACT(target_cell, range), 0) entered as an array formula. This will locate the row number of the first occurrence of a duplicate string longer than 255 characters.
Why does my formula show curly braces {} around it?
The curly braces indicate that the formula has been successfully entered as an array formula using the Ctrl+Shift+Enter shortcut. You cannot type these braces manually; the spreadsheet software adds them automatically to process multiple values at once.
