Splitting up address

[Guest]

How would I write a macro that would split up an addrss by spaces? Lets say
the address is 1364 W elm street apt #4? I am new to coding so I dont know
where to start. Also I need the macro to run down the list until there is no
address to move.
Any help would be greatly appreciated, thanks Neal.

 

[casey.chambliss]

I suggest you start by recording a macro of you using the text to
columns function and using a space as your delimitor.

 

[Guest]

or try this 1 i got from mr. bak

Sub test()
Dim rCell As Range
For Each rCell In Selection
rCell(1, 2).Resize(, 6) = Split(rCell, " ")
Next
End Sub

 

[Guest]

remember select ur adress cells, - and run kode

"(e-mail address removed)" skrev:

 

[Guest]

Thank you it works great.

remember select ur adress cells, - and run kode

"(e-mail address removed)" skrev:

 

[Guest]

How do I avoid getting a null value in the remainding cells. Not every
address will have 6 cells to populate?Is there a way to write it out to stop
after it hits a blank spot?

 

[Guest]

Sub test()
Dim c, cCount
Dim rCell As Range
For Each rCell In Selection
For c = 1 To Len(rCell)
If Mid(rCell, c, 1) = " " Then cCount = cCount + 1
Next
rCell(1, 2).Resize(, cCount + 1) = Split(rCell, " "): cCount = 0
Next
End Sub

 

[Guest]

This worked great. I had one question about assigning a range. Would i enter
it under the dim rcell as range, and would it be range("A1").select? Thanks
for the help.

 

[Guest]

U have to select all the cells in a column u want to split, then run kode

Im not sure if that is what u mean

else ask again but keep it simple cus im from Denmark

 

[Guest]

Ok, thanks. The code should like this?

Sub test()
range("A1").select
Dim c, cCount
Dim rCell As Range
For Each rCell In Selection
For c = 1 To Len(rCell)
If Mid(rCell, c, 1) = " " Then cCount = cCount + 1
Next
rCell(1, 2).Resize(, cCount + 1) = Split(rCell, " "): cCount = 0
Next
End Sub