Move only part of a text to another column

Question

Move only part of a text to another column

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have a spreadsheet according to the attachment that in the backyard column is the address and the number, however, I want to separate, that is, remove the number of the addresses that are in the backyard column and move to column number at one time only. The following is an example.

Logradouro                                  Numéro (Essa coluna nao tem nada)

AV NOSSA SENHORA DE COPACABANA 664  
AV DOM SEVERINO     
AV GETULIO VARGAS   
AV TANCREDO NEVES 148   
AV DOM HELDER CAMARA, 5332  
AV TANCREDO NEVES 148   
R PROJETADA 05 S/N  
ROD BA-526 305  
AV MAGALHAES DE CASTRO 12000

excel microsoft

asked by anonymous 07.07.2017 / 15:42

2 answers

0

You can do a function in VBA for Excel:

Function StripNumber(stdText As String) 
    Dim str As String, i As Integer 
     'strips the number from a longer text string
    stdText = Trim(stdText) 

    For i = 1 To Len(stdText) 
        If Not IsNumeric(Mid(stdText, i, 1)) Then 
            str = str & Mid(stdText, i, 1) 
        End If 
    Next i 

    StripNumber = str ' * 1

End Function

Here has a tutorial to register VBA functions in Excel

Or you can use this "gambiarra":

SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (1, 2, "", 1, ""), 2, ""), 3, 4, "", 5, ""), 6, ""), 7, "", 8, ""), 9,

07.07.2017 / 16:21

Incorrect mime for XLS files Kendo ui NumericTextBoxFor - Set startup values

score 0 · Accepted Answer

It all depends on how your data is standardized, but if the numbers are always in the last positions as in your example:

AV NOSSA SENHORA DE COPACABANA 664  
AV DOM SEVERINO     
AV DOM HELDER CAMARA, 5332  
R PROJETADA 05 S/N  
ROD BA-526 305

You could use a set of functions to achieve what you want, something like this:

=SE(ÉERRO(VALOR(DIREITA(A1;CORRESP(" "; ÍNDICE(EXT.TEXTO(A1;NÚM.CARACT(A1)-LIN(INDIRETO("1:"&NÚM.CARACT(A1)))+1;1);0);0)-1)));"S/N";VALOR(DIREITA(A1;CORRESP(" "; ÍNDICE(EXT.TEXTO(A1;NÚM.CARACT(A1)-LIN(INDIRETO("1:"&NÚM.CARACT(A1)))+1;1);0);0)-1)))

This is the complete formula, see below how I came up with this formula.

It's a bit long the set of formulas ... but it's simple, I'll try to separate and explain to understand better:

It checks the first space of the right for the left , because the data that gave the last characters are their numbers, and some have "S / N" others are no number, then it would be:

=CORRESP(" "; ÍNDICE(EXT.TEXTO(A1;NÚM.CARACT(A1)-LIN(INDIRETO("1:"&NÚM.CARACT(A1)))+1;1);0);0)

This is the hardest formula to really understand.

Search the data from right to left after the last whitespace:

=DIREITA(A1;[FÓRMULA DO ITEM 1 AQUI]-1)

Replace what is between [] (brackets) with the formula in item 1.

Verify that this result (item 2) is a number and returns that number or returns a text ("S / N" in case):

=SE(ÉERRO(VALOR([FÓRMULA DO ITEM 2 E 1]));"S/N";VALOR([FÓRMULA DO ITEM 2 E 1]))

Replace what is between [] (brackets) by the formula from item 2 and item 1 to have the complete formula.

I hope I have helped!