Splitting cells

[chicoboss]

HI everyone,


I have a very large data base, on a column we have a list of address + number. I want to separate the number from the address and put number on another cell.

Exp-
A1

Nachtegaalstraat 76

<tbody>
</tbody>

Split as following
B1
Nachtegaalstraat
C1
76

I hope i have been clear enough.

Many thanks for your help.
Regards,

 

[Jonmo1]

welcome to the board..

Try Highlighting column A
Data - Text To Columns
Deliminated - next
Check Space - next
Destination: = B1
Click Finish.

 

[Rick Rothstein]

I have a very large data base, on a column we have a list of address + number. I want to separate the number from the address and put number on another cell.

Exp-
A1

Nachtegaalstraat 76

<TBODY>
</TBODY>

Split as following
B1
Nachtegaalstraat
C1
76

Will your addresses ever contain more than one word?

 

[chicoboss]

Many thanks for your quick reply.

I tried that,

But it seems not to work because i have some addresses that are longer and with space and it will put every separation on a different cell.
I need to separate them into two cells

 

[chicoboss]

Yes exp :

Van der Takstraat 1C

<tbody>
</tbody>

Will your addresses ever contain more than one word?

 

[chicoboss]

Btw, im working on excel 2010

 

[Rick Rothstein]

Yes exp :

Van der Takstraat 1C

<TBODY>
</TBODY>

Can we assume 1C is the "number" at the end of your text? If so, give this macro a try...

Sub SplitNumberFromAddress()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr).Offset(, 1) = Evaluate("IF(LEN(" & Addr & "),TRIM(RIGHT(SUBSTITUTE(TRIM(" & _
                                     Addr & "),"" "",REPT("" "",99)),99)),"""")")
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),SUBSTITUTE(" & Addr & ","" ""&" & _
                         Range(Addr).Offset(, 1).Address & ",""""),"""")")
End Sub

 

[Jonmo1]

If we can just go by "Split at the last space in the cell"
And it can be assumed that the "number" will not be repeated within the Name..

Try
B1: =TRIM(SUBSTITUTE(A1,C1,""))
C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Note, the formula in B1 depends upon the result of the formula in C1.
So B1 will not be correct if you have not yet put the formula in C1

 

[chicoboss]

I appriciate your help, but please also help in creating this macro

 

[chicoboss]

If we can just go by "Split at the last space in the cell"
And it can be assumed that the "number" will not be repeated within the Name..

Try
B1: =TRIM(SUBSTITUTE(A1,C1,""))
C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

Note, the formula in B1 depends upon the result of the formula in C1.
So B1 will not be correct if you have not yet put the formula in C1

When putting the formula in the bar i get an error : the formula you typed contains an error.