A form using only native Excel functions (the original source is this answer in SOen ) looks like this:
=DIREITA(A2;NÚM.CARACT(A2)-PROCURAR("|";SUBSTITUIR(A2;" ";"|";NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;" ";"")))))
Detailing how it works, from left to right:
-
NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;" ";""))
- counts the total number of spaces in the original string
-
SUBSTITUIR(A2;" ";"|"; ... )
- Changes only the last space for the character |
, to differentiate it from the others (note that your original string can not contain a character of this, otherwise the formula does not work correctly).
-
PROCURAR("|"; ... )
- Finds the absolute position of that character |
added (and that was the last space in the original string)
-
DIREITA(A2;NÚM.CARACT(A2) - ... ))
- Returns all characters after that character |
(that is, the last integer in the original string)
In addition, because you are using Excel in Portuguese (where the comma is used as the decimal separator), you need to use the semicolon as the parameter separator in the formula. p>
The original answer in SOen also has suggestions on how to circumvent the case where there is no white space in the original string (other than the use of commas, this may be your "new" error mentioned in comment).
P.S .: Incidentally, this response is basically the same solution suggested by
@Earendul in a comment, just translated into Excel in Portuguese.
Except for a big coincidence, the original credit is still the author of the reply there in SOen.