2

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)?

enter image description here

Improve this question
2
  • 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 '20 at 23:55
  • As a result, I expect to be the whole cell "NB667". – PurplePapaya Nov 10 '20 at 2:00
2

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))

enter image description here

Functions used:

Improve this answer
2

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

enter image description here

Regex Explanation.

NB matches the characters NB literally (case sensitive)

1st Capturing Group (\d+)

\d+ matches a digit (equal to [0-9])

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

Improve this answer
2
  • 1
    is there a way to show the result as "NB667" with the text ? – PurplePapaya Nov 9 '20 at 23:10
  • @PurplePapaya see edit, please add your expected output to your post next time :) – Manakin Nov 10 '20 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.