How to extract only the penultimate character of a string and then use auto-fill in Excel

Solution

To get the penultimate cell item, use the following function in the first cell and then auto-fill.

Formula

=ARRUMAR(ESQUERDA(DIREITA(SUBSTITUIR(""&ARRUMAR(A1);" ";REPT(" ";40));80);40))

Where " " is the separator, which in this case is a space. This formula works only with space as a separator.

Separate space separator

If the separator is different from space, for example a hyphen -

As in the following table:

+---+----------------------------+---------+
|   |             A              |    B    |
+---+----------------------------+---------+
| 1 | 1-22-333-4444-55555-666666 | 55555   |
| 2 | 1-22-333-4444-55555        | 4444    |
| 3 | 1-22-333-4444              | 333     |
| 4 | 1-22-333                   | 22      |
| 5 | 1-22                       | 1       |
| 6 | 1                          | 1       |
+---+----------------------------+---------+

A custom function (UDF) can be created in VBA:

Function EXTRAIRPENULTIMO(Txt As String, Separador As String) As String
    On Error GoTo ErrHandler:
    contador = Len(Txt) - Len(Replace(Txt, Separador, ""))
    If contador = 0 Then
        EXTRAIRPENULTIMO = Txt
    Else
        EXTRAIRPENULTIMO = Split(Application.Trim(Mid(Txt, 1)), Separador)(contador - 1)
    End If
    Exit Function
ErrHandler:
    ' error handling code
    EXTRAIRPENULTIMO = CVErr(xlErrNA)
    On Error GoTo 0
End Function

And in the B column the following formula is used:

=EXTRAIRPENULTIMO(B1;"-")

The formula has gotten a bit big

Inthespecificcaseofthepenultimatevaluebetweenspaces,

=SE(NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";""))<1;
  "";
  SE(
    NÚM.CARACT(A1) - NÚM.CARACT(SUBSTITUIR(A1; " ";""))=1;
    ESQUERDA(A1;LOCALIZAR(" ";A1));
    EXT.TEXTO(
      A1;
      LOCALIZAR("|";SUBSTITUIR(A1;" ";"|";NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";""))-1))+1;
      LOCALIZAR("|";SUBSTITUIR(A1;" ";"|";NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";"")))) -
      LOCALIZAR("|";SUBSTITUIR(A1;" ";"|";NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";""))-1))-1
    )
  )
)

And a generalization of this would be

=SE(
  NÚM.CARACT($A1) - NÚM.CARACT(SUBSTITUIR($A1; " ";""))<C$7;
  "";
  SE(
    NÚM.CARACT($A1) - NÚM.CARACT(SUBSTITUIR($A1; " ";""))=C$7;
    ESQUERDA($A1;LOCALIZAR(" ";$A1));
    EXT.TEXTO(
      $A1;
      LOCALIZAR("|";SUBSTITUIR($A1;" ";"|";NÚM.CARACT($A1)-NÚM.CARACT(SUBSTITUIR($A1;" ";""))-C$7))+1;
      LOCALIZAR("|";SUBSTITUIR($A1;" ";"|";NÚM.CARACT($A1)-NÚM.CARACT(SUBSTITUIR($A1;" ";""))-C$7+1)) -
      LOCALIZAR("|";SUBSTITUIR($A1;" ";"|";NÚM.CARACT($A1)-NÚM.CARACT(SUBSTITUIR($A1;" ";""))-C$7))-1
    )
  )
)