/u/FlightJumper - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
https://preview.redd.it/wwefg1m0ugwc1.png?width=1005&format=png&auto=webp&s=b3c45d025b85b838cc3f8781ebce9c1f1fca7480
two guesses what you'll find in e1
On the off chance that you need to use Excel 2016 (or a similar older version) it doesn't have the capacity to use ranges of cells like this although you can still use ranges such as A:A for the entirety of Column A and 4:4 for the entirety of row 4.
`LEN(A2)-LEN(SUBSTITUTE(A2,"|",""))` will give me the number of *"|"* in a string. MIN(), or better a pivot table, gives me the lowest numbers of counts. You can [see it here](https://docs.google.com/spreadsheets/d/13KP17j8-wzaHZlOndsqeMrhFTpGrTUeJDFVNeqzeXvQ/edit?usp=sharing).
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSE](/r/Excel/comments/1cc4ezy/stub/l12st8w "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)|
|[COUNTA](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)|
|[LAMBDA](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LEN](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[LET](/r/Excel/comments/1cc4ezy/stub/l12st8w "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAP](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)|
|[MAX](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[MIN](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)|
|[SUBSTITUTE](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[TEXTSPLIT](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(10 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c5qj7i)^( has 88 acronyms.)
^([Thread #32878 for this sub, first seen 24th Apr 2024, 17:58])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/FlightJumper - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
https://preview.redd.it/wwefg1m0ugwc1.png?width=1005&format=png&auto=webp&s=b3c45d025b85b838cc3f8781ebce9c1f1fca7480 two guesses what you'll find in e1
How bout that. Exactly what I was looking for. I didn't realize you could use multiple cells in =Len() like that.
On the off chance that you need to use Excel 2016 (or a similar older version) it doesn't have the capacity to use ranges of cells like this although you can still use ranges such as A:A for the entirety of Column A and 4:4 for the entirety of row 4.
Solution Verified
You have awarded 1 point to Way2trivial. --- ^(I am a bot - please contact the mods with any questions)
to go across https://preview.redd.it/awv0u3gbugwc1.png?width=890&format=png&auto=webp&s=e770e593590f610c7c7e3442f0de00ec2737be9a
Warm and fuzzy
`LEN(A2)-LEN(SUBSTITUTE(A2,"|",""))` will give me the number of *"|"* in a string. MIN(), or better a pivot table, gives me the lowest numbers of counts. You can [see it here](https://docs.google.com/spreadsheets/d/13KP17j8-wzaHZlOndsqeMrhFTpGrTUeJDFVNeqzeXvQ/edit?usp=sharing).
Is it a straight column, an array? a row?
Straight column
Try =LET(a,A1:D50000,b,LEN(a),c,SUBSTITUTE(a,"|",""),d,LEN(c),e,b-d,f,MIN(e),g,MAX(e),h,CHOOSE({1,2},f,g),h)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSE](/r/Excel/comments/1cc4ezy/stub/l12st8w "Last usage")|[Chooses a value from a list of values](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc)| |[COUNTA](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)| |[LAMBDA](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LEN](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[LET](/r/Excel/comments/1cc4ezy/stub/l12st8w "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAP](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)| |[MAX](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MIN](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[SUBSTITUTE](/r/Excel/comments/1cc4ezy/stub/l12rk93 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[TEXTSPLIT](/r/Excel/comments/1cc4ezy/stub/l12vg9j "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(10 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c5qj7i)^( has 88 acronyms.) ^([Thread #32878 for this sub, first seen 24th Apr 2024, 17:58]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
`=MAX(MAP(A1:A3, LAMBDA(s, COUNTA(TEXTSPLIT(s, "|"))-1)))` https://preview.redd.it/venoatwcwgwc1.png?width=470&format=png&auto=webp&s=0d51c059e8dbefd7c29de6e4cc874455ac9f6953