# How do I find the max value in a column only if it contains certain texts in google sheets?

The data has numbers in text format and texts with numbers.

How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, if it contains only `NB`)? • As a result, do you expect just the digits value `667` or the whole cell `NB667`? Also. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Nov 9 at 23:55
• As a result, I expect to be the whole cell "NB667". – PurplePapaya Nov 10 at 2:00

You can use the following formula

``````=CONCATENATE(SORTN(ArrayFormula(IFERROR({REGEXEXTRACT(\$A\$2:A,".*NB"),
REGEXEXTRACT(\$A\$2:A,"NB(.*)")*1})),
1,2,2,0))
`````` Functions used:

## Edit.

You can use `Index` & `Match` to get the reference to the max item in the array and return the matching item from C.

I don't like this approach as your declaring an array twice so the computation is more expensive, I would suggest the answer by @marikamitsos

``````=INDEX(C2:C9,MATCH(MAX(
ARRAYFORMULA(INT(IFERROR(REGEXEXTRACT(C2:C9,"NB(\d+)"),0)))),
ARRAYFORMULA(INT(IFERROR(REGEXEXTRACT(C2:C9,"NB(\d+)"),0))),0))

NB667
`````` Regex Explanation.

## `+` Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

• is there a way to show the result as "NB667" with the text ? – PurplePapaya Nov 9 at 23:10