hello

i have a cell with addresses and i want to split the address into hous
number and street name

for example cell C1: 123 Springfield Lane

split to
C1: 123
D1: Springfield Lane


thank

--
narutar
-----------------------------------------------------------------------
narutard's Profile: http://www.excelforum.com/member.php...fo&userid=2511
View this thread: http://www.excelforum.com/showthread.php?threadid=46825

There are a number of ways to achieve this. The simplist way would be to
place your cursor in the cell which contains the full address and run the
following macro:

Public Sub parseaddress()
Dim thevalue$
Dim thehouseno$
x = ActiveCell.Row
y = ActiveCell.Column
thevalue = ActiveCell.Value
thehouseno = Left(ActiveCell.Value, InStr(1, ActiveCell.Value, " "))
Range(Cells(x, y + 1), Cells(x, y + 1)).Value = thehouseno
Range(Cells(x, y + 2), Cells(x, y + 2)).Value = Right(ActiveCell.Value,
Len(ActiveCell.Value) - Len(thehouseno))

End Sub

This will parse the value in the active cell and split the first word into
the next column and the rest of the entry into the 2nd column. There are
obvious limitiations to this and you may want to adjust it to specifically
look for digits instead.

The second problem with this macro is that it only works on a single cell at
a time. If you have a large no. of cells that you wish to change you may
wish to make them into a named range and then enclose the above within a
loop:-

Public Sub parseaddress()
Dim thevalue$
Dim thehouseno$

For Each c In Range("myRangeName")
c.Select
x = ActiveCell.Row
y = ActiveCell.Column
thevalue = ActiveCell.Value
thehouseno = Left(ActiveCell.Value, InStr(1, ActiveCell.Value, " "))
Range(Cells(x, y + 1), Cells(x, y + 1)).Value = thehouseno
Range(Cells(x, y + 2), Cells(x, y + 2)).Value = Right(ActiveCell.Value,
Len(ActiveCell.Value) - Len(thehouseno))
Next c
End Sub

Hope this gives you some pointers...


June


"narutard" wrote in
message ...

hello

i have a cell with addresses and i want to split the address into house
number and street name

for example cell C1: 123 Springfield Lane

split to
C1: 123
D1: Springfield Lane


thanks


--
narutard
------------------------------------------------------------------------
narutard's Profile:
http://www.excelforum.com/member.php...o&userid=25111
View this thread: http://www.excelforum.com/showthread...hreadid=468255

If you don't have exceptions

J2: =LEFT(C2,FIND(" ",C2)-1)
K2: =TRIM(MID(C2,FIND(" ",C2),400))

The VBA is along similar lines you don't need the third operand for MID
and you can use InStr instead of Find.

For a permanent separation and not just a helper column
you could use the SepTerm macro at
http://www.mvps.org/dmcritchie/excel/join.htm#septerm
after creating a new column to the right.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"narutard" wrote in message
...

hello

i have a cell with addresses and i want to split the address into house
number and street name

for example cell C1: 123 Springfield Lane

split to
C1: 123
D1: Springfield Lane


thanks


--
narutard
------------------------------------------------------------------------
narutard's Profile: http://www.excelforum.com/member.php...o&userid=25111
View this thread: http://www.excelforum.com/showthread...hreadid=468255