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
)?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityThe 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
)?
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:
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.
NB
matches the characters NB literally (case sensitive)(\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)
667
or the whole cellNB667
? 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