I've got a list with productnumbers. Like this:
RS0001
RS0002
RS0030
RS0050
All numbers are RS followed by four numbers.
I use the following array formula to find the highest number:
{="RS"&TEXT(MAX(--(RIGHT($B$1:$B$100,LEN($B$1:$B$100)-2))),"0000")}
However the list goes further than B100, but I can't use B:B because there are also empty cells in column B which gives #Value!.
How can I get this formula to ignore blanks?