How to find the longest or shortest text string in a column?
In a worksheet, if you want to know which cell contains the longest or shortest characters and then extract it from a column, how could you do? Normally, you may count the number of the text strings in the list one by one and compare them to get the result. But here, I can talk about an easy formula to help you find the longest or shortest text as you need.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To retrieve the longest text string from a list of data, the following Array formula can help you.
1. Next to your list of data, in a blank cell, please enter this formula:
=INDEX(A2:A11,MATCH(MAX(LEN(A2:A11)),LEN(A2:A11),0)), see screenshot:
Tip: In the above formula, A2:A11 indicates the data range that you want to use, you can change it as your need.
2. Then press Shift + Ctrl + Enter keys together, and the longest text string has been extracted. See screenshot:
Note: If you need to get the shortest text string, please apply this Array formula:
=INDEX(A2:A11,MATCH(MIN(LEN(A2:A11)),LEN(A2:A11),0)), and remember to press Shift + Ctrl + Enter keys at the same time.
How to find the last used row or column in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoAbsolute nonsense. Returned a 12-char string when I know for a fact there are 15-char strings in the list at least.
- To post as a guest, your comment is unpublished.· 4 years agoThanks for the tip.
How I can obtain equal result with a range 10x10 not only 1x10 as the example.
- To post as a guest, your comment is unpublished.· 5 years agoVery useful function and guidance
- To post as a guest, your comment is unpublished.· 5 years ago[b]Thank You,You saved my job[/b]