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