# Function for MAX text string in a range



## bbbb1234 (Jan 2, 2023)

I have the following values in cells D5:D9 - note that is is only a partial list of the values
N
BJ
G
O
M

I need an Excel formula that will find the MAX text string in a range no matter how long the string is - in this case above it would be BJ. I have found lots of solutions on the web but none of them have worked. I one case, the formula returns O which is partially correct. In another case, it returned N, the first text string in the list.

Any ideas?


----------



## Fluff (Jan 2, 2023)

How about

```
=TAKE(SORTBY(D5:D9,COUNTIFS(D5:D9,"<="&D5:D9)),1)
```


----------



## bbbb1234 (Jan 2, 2023)

Fluff - that worked - sort of. I expanded the range to d5:d58, it did not work. I then worked backwards from d5:d14 where it did not work. It did not work on d5:d11 but did work on d5:d10. Thoughts?


----------



## bbbb1234 (Jan 2, 2023)

Two more things. The formula returned B for d5:d11 but BJ for d5:d10. The values for the entire range of d5:d58 is below.


NBJGOMRBKGHKHMGLLHIFOHHLOLBIBIJJHHHHHHMMIIGHJDJDDBMEOLHIG


----------



## Fluff (Jan 2, 2023)

What is the max length of the strings?


----------



## bbbb1234 (Jan 2, 2023)

The max string length is 3 characters.


----------



## Fluff (Jan 2, 2023)

Will it ever be greater than XFD?


----------



## bbbb1234 (Jan 2, 2023)

No, it will never be more than XFD.


----------



## Fluff (Jan 2, 2023)

Ok, how about

```
=TAKE(SORTBY(D5:D58,COLUMN(INDIRECT(D5:D58&"1")),-1),1)
```


----------



## bbbb1234 (Jan 2, 2023)

That worked!!! Since the range will get larger over time, say d5:d58 today d5:d59 a week from now, and d5:d1000 a year from now, I assume the formula will continue to work as long as the max string does not exceed XFD. Is that correct?


----------



## bbbb1234 (Jan 2, 2023)

I have the following values in cells D5:D9 - note that is is only a partial list of the values
N
BJ
G
O
M

I need an Excel formula that will find the MAX text string in a range no matter how long the string is - in this case above it would be BJ. I have found lots of solutions on the web but none of them have worked. I one case, the formula returns O which is partially correct. In another case, it returned N, the first text string in the list.

Any ideas?


----------



## Fluff (Jan 3, 2023)

That's right. 

Another option that is non-volatile & will allow strings beyond XFD

```
=TAKE(SORTBY(D5:D100,BYROW(D5:D100,LAMBDA(br,IFERROR(--(CONCAT(CODE(MID(UPPER(br),SEQUENCE(,LEN(br)),1)))),0))),-1),1)
```


----------

