Move only part of a text to another column

Question

I have a spreadsheet as an attachment that in the street column is the address and the number, however, I want to separate, ie remove the number of the addresses that are in the street column and move to column number at once. Below 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    

Answer 1

You can do a VBA function 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 of registering VBA functions in Excel

Or you could use that "gambiarra":

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

Answer 2

It all depends on how standardized your data is, 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 that:

=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 got to this formula.

It is a little long the set of formulas... but it is simple, I will try to separate and explain to better understand:

1. Check the first space of right to the left, because by the data that provided the last characters are their numbers, and some have "S/N" others are without any number, so 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 most difficult formula to actually understand.

2. Search data from right to left after last blank:

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

Replace what is between [ ] (brackets) by the formula of item 1.

3. Verirfica if this result (item 2) is a number and returns that number or returns a text ("S/N" in the 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 of item 2 and item 1 to have the complete formula.

Do a test with the complete formula on a flatten having the address in cell "A1" and you will see that it works.

I hope I’ve helped!