How to Find the Longest or Shortest Text String in a Column in Excel?
Last Updated : 06 Oct, 2022
In this article, we will see how to find the longest or shortest text string in a column in Excel? Usually, for finding the longest or shortest string we can visit the all string in columns one by one and compare them to get results. This seems to work when you have less amount of data in an excel sheet. This is not a particle with a large dataset in Excel. Let’s see how you can find the longest or shortest string in the column by using just one single line of formula in Excel.
Longest Text String in a Column:
We will use the below formula to get the longest text string in a column in an excel file.
Syntax:
=INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0)
Parameters:
- INDEX: Returns the value of the given position in the column
- MATCH: Locate the position of a lookup value in a column
- MAX: Returns the largest value
- LEN: Returns the length of the given string
- range: Input range of column
Explanation of Formula:
Let’s start with the inner parenthesis value. LEN(range) will return the array of the length of the cell that comes under the input range. Now MAX will return the maximum numeric value it that is the maximum length from the given array. Now this two are passed to MATCH. It will return the position of a lookup value in a column. MATCH is used to perform an exact match. Now INDEX will return the value of the input position of columns.
Example:
Let’s suppose we have the below excel file with 5 columns and we want to find the longest text string in a column ‘country’ that is column D. We will use the above formula to find it.
Now, let’s see how to define the formula for this and what will be the output of it.
Here, we first select the blank cell in column D. Then we enter a formula in that cell and you can see the output of it in the above image. In the above image, you can see we select range from D2 to D11. This is the range of the column in which we want to find the longest text string.
Note: After entering the formula pressing enter will not give the result, you must press Shift + Ctrl + Enter keys together. This will return the longest text string that has been extracted in the column.
Shortest Text String in a Column:
We will use the below formula to get the Shortest text string in a column in an excel file.
Syntax:
=INDEX(range,MATCH(MIN(LEN(range)),LEN(range),0)
Parameters:
- INDEX: Returns the value of the given position in the column
- MATCH: Locate the position of a lookup value in a column
- MIN: Returns the shortest value
- LEN: Returns the length of the given string
- range: Input range of column
Explanation of Formula:
Let’s start with the inner parenthesis value. LEN(range) will return the array of the length of the cell that comes under the input range. Now MIN will return the minimum numeric value of it that is the minimum length from the given array. Now this two are passed to MATCH. It will return the position of a lookup value in a column. MATCH is used to perform an exact match. Now INDEX will return the value of the input position of columns.
Example:
Let’s suppose we have the below excel file with 5 columns and we want to find the shortest text string in a column ‘country’ that is column D. We will use the above formula to find it.
Now, let’s see how to define the formula for this and what will be the output of it.
Here, we first select the blank cell in column D. Then we enter a formula in that cell and you can see the output of it in the above image. In the above image, you can see we select range from D2 to D11. This is the range of the column in which we want to find the shortest text string. Here also remember that after entering the formula pressing enter will not give the result, you must press Shift + Ctrl + Enter keys together.
Similar Reads
SQL Query to Find Shortest & Longest String From a Column of a Table
Here, we are going to see how to find the shortest and longest string from a column of a table in a database with the help of SQL queries. We will first create a database "geeks", then we create a table "friends" with "firstName", "lastName", "age" columns. Then will perform our SQL query on this ta
3 min read
How to Find the Longest Line from a Text File in Python
Finding the longest line from a text file consists of comparing the lengths of each line to determine which one is the longest. This can be done efficiently using various methods in Python. In this article, we will explore three different approaches to Finding the Longest Line from a Text File in Py
3 min read
How to Find the Last Used Row and Column in Excel VBA?
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTyp
2 min read
How to Fit Long Text in Excel Spreadsheets?
By default, when you create a new workbook in Excel, the row height and column width for all cells are set to the same value. If the text you enter in a spreadsheet cell is too long and the next column has data, it either spills over into the next blank cell or is truncated by the data in the adjace
4 min read
How to find maximum string length by column in R DataFrame ?
In this article, we are going to see how to find maximum string length by column in R Programming Language. To find the maximum string length by column in the given dataframe, first, nchar() function is called to get the length of all the string present in the particular column of the dataframe, an
2 min read
How to Extract the last N Words from Text String in Excel
Excel is a tool for storing and managing a large amount of data. Data is entered in a rectangular block, which we called a cell. This tool can be used to perform different tasks like creating graphs and analyzing trends, to get insights from the data. It is primarily used for financial data analysis
3 min read
Find the Longest Non-Prefix-Suffix Substring in the Given String
Given a string s of length n. The task is to determine the longest substring t such that t is neither the prefix nor the suffix of string s, and that substring must appear as both prefix and suffix of the string s. If no such string exists, print -1. Example: Input: s = "fixprefixsuffix"Output: fix
7 min read
How to Sort a Pivot Table in Excel : A Complete Guide
Sorting a Pivot Table in Excel is a powerful way to organize and analyze data effectively. Whether you want to sort alphabetically, numerically, or apply a custom sort in Excel, mastering this feature allows you to extract meaningful insights quickly. This guide walks you through various Pivot Table
7 min read
How to Add a Column in Excel: Step-by-Step Guide
Need to organize your data better in Excel but unsure how to add a column without disrupting your existing setup? Whether you’re working with a simple list or a complex table, inserting columns is a fundamental skill that increases productivity and keeps your data structured. This guide covers 4 eas
6 min read
How to Swap Columns in Excel: 3 Methods Explained
To Swap Columns in Excel - Quick StepsDrag and Drop: Select a column, drag it to a new position, and release.Cut and Paste: Cut a column (Ctrl + X) for Windows and (Cmd + X) for Mac, then paste it in the new location using Insert Cut Cells.Copy and Paste: Copy a column, then insert the copied cells
8 min read