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

Asked

Viewed 84 times

0

I want to display only the numbers in red of the sequence, but this formula only displayed the first specified number and the other after it or before it, I wanted to display jumps in any specific number whatever its position, to be able to use auto-fill smoothly (click and drag the cursor down), as I do to display only the numbers in red ??inserir a descrição da imagem aqui

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

excel

  • Please insert the formula currently used as code and not as image.

  • =TIDY(LEFT(RIGHT(REPLACE(""&TIDY(A99);" ";REPT(" ";20));41);40))

1 answer

1


Solution

With the formula used, it is possible to find only continuous values in the string.

Then it is necessary to find the antepenultimate value and the last and then concatenate, but error may occur when the string has no spaces " "

Last

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

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

Antepenultimate

To find the antepenultimate 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)))

Checking

Checks if there are 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 Excel versions use the function CONCAT() and the old CONCATENAR()

  • I put in the last formula and said, "#name?" with the first two worked, I think Excel should already come with a function to select separate characters and set the position to our choice

  • I checked now, the "Concat" was abbreviated, then I entered right, it worked the formula! great, more functional

Browser other questions tagged excel

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