0
Solution
To obtain the penultimate cell item, use the following function in the first cell and then the auto-refilling.
Formula
=ARRUMAR(ESQUERDA(DIREITA(SUBSTITUIR(""&ARRUMAR(A1);" ";REPT(" ";40));80);40))
In which " "
is the separator, which in this case is a space. This formula works only with space as separator.
Different separator of space
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 column B
the following formula is used:
=EXTRAIRPENULTIMO(B1;"-")
Very good. But would it be possible to insert another numbering sequence in front of the penultimate, for example a numbering that this before "55555" or after? in case it would be: B1: 5555555 4444, or if it had the sequence "7" in front of the sequence "666666", B1: 5555555 7777777, ??
– Elienay Junior
would be the value before and after the penultimate? Therefore, a concatenation of the last and antepenultimate? I suggest asking another question
– danieltakeshi
Yes. I’ll ask another question
– Elienay Junior