How to extract only the penultimate character from a sequence and then use auto-refilling in Excel

Asked

Viewed 403 times

0

I have a numerical sequence separated by spaces, I just want to extract the numbers in red, but when using auto-refill (click and drag down with the cursor) it doesn’t work! how do I extract only the numbers in red from all cells? and use auto-refilling smoothly, ?? inserir a descrição da imagem aqui

excel

2 answers

2


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, ??

  • would be the value before and after the penultimate? Therefore, a concatenation of the last and antepenultimate? I suggest asking another question

  • Yes. I’ll ask another question

1

The formula got kind of big

inserir a descrição da imagem aqui

In the specific case of the penultimate value between spaces

=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 that 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
    )
  )
)

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.