How to display separate characters from different sequences by skipping spaces in Excel

Navigation
0

I want to display only the red numbers of the sequence, but this formula only displayed the first number specified and the other after it or before it, I wanted to display jumping in any number whatever its position, in order to use the auto-fill without problems (click and drag the cursor down), how do I display only the red numbers ??

=ARRUMAR(ESQUERDA(DIREITA(SUBSTITUIR(""&ARRUMAR(A99);" ";REPT(" ";20));41);40))

    
asked by anonymous 23.10.2018 / 00:10

1 answer

1

Solution

With the formula used, you can find only continuous values in the string.

Then it is necessary to find the last and last value and then to concatenate, but an error can occur when the string does not have spaces " "

Last

To find the last value, the following formula is used:

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

Last penultimate

To find the penultimate value, the following formula is used:

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

Concatenation

Then they are concatenated with:

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

Verification

Check for more than two spaces in the cell with:

=SE((NÚM.CARACT(A2)-NÚM.CARACT(SUBSTITUIR(A2;" ";"")))>1;"Fórmula caso haja mais que dois espaços";"ERRO")

Formula

=SE((NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";"")))>1;ARRUMAR(CONCAT(ESQUERDA(DIREITA(SUBSTITUIR(""&ARRUMAR(A1);" ";REPT(" ";40));120);40);ESQUERDA(DIREITA(SUBSTITUIR(""&ARRUMAR(A1);" ";REPT(" ";40));40);40)));"ERRO")

  

Newer versions of Excel use the CONCAT() function and the old CONCATENAR()

    
23.10.2018 / 16:13
Complete form without reloading the page Join in three tables does not show all results