Splitting Address in to 2 column.

[donlincolnmre]

Hello

I'm trying to split the numeric portion of the address in 1 column and the rest into another colum, the TEXT TO COLUMN fuction will break up in to severl difference weired coulmne, i just need to split this in to 2 colum.

c coulmn should have just the numeric portion of the addresss &
d should have the rest of the address.

Any help would be appreciated.

here is the code.

Book4
	A	B	C	D
1	15326 Binney St
2	13823 Vista Grande Dr
3	2341 Silk Tree Dr
4	8238 Noren St
5	5035 Vista Montana
6	19762 Hi Top Ln
7	525 S Westgate Dr
8	4932 Park Pl
9	7364 Cerritos Ave
10	5730 Southview Dr
11	5510 Westfield St
12	29 Alegria
13	12 Heritage
14	121 S Grand Ave
15	4539 N COLDBROOK
16	3764 Bella Isola Ln
17	5007 N peck Rd B
18	41126 Academy Dr
19	4492 CHEVIOT
20	1600 Skyline Dr
21	1320 Sheller Dr
22	3248 Ashgate Ct
23	43138 Corte Argento
24	480 S Lyon Ave
25	24 Charlotte
26	10908 Newville Ave
27	506 Terra Bella
28	6586 Valley Dr
29	5427 Ashworth St
30	14646 Willow Leaf Rd
Sheet1

 

[donlincolnmre]

in the previous message, the ADDRESS should be split in column B and C

 

[Nimrod]

Formula for column B: =LEFT(A1,FIND(" ",A1,1))

Formula for Column C: =MID(A1,FIND(" ",A1,1),100)

 

[Peter_SSs]

Although you may not see them, I believe that Nimrod's formulas will leave a blank at the end of the number part and another one at the start of the rest of the address. [Check with =LEN(B1) and =LEN(C1)]

Therefore, I would suggest the slight modifications:
=TRIM(LEFT(A1,FIND(" ",A1,1))) or an alternative:
=LEFT(A1,FIND(" ",A1,1)-1)

and
=TRIM(MID(A1,FIND(" ",A1,1),100)) or an alternative:
=TRIM(SUBSTITUTE(A1,B1,""))

 

[donlincolnmre]

well it worked fine, except for one problem, it splitted 1 row in to 2 rows, as you see the output, how can you fix that., now how do i splt the address Hilltop since the next row next to it is taken over.

here is the output.

Book4
	A	B	C	D
1	12442 Softwind Dr	12442	Softwind Dr
2	4973 Hilltop Rd
3	27854 Fair Acres Ln
4	13963 Kicking Horse Cir
5	17591 El Cajon Dr
6	16427 La Paz Dr
Sheet1

 

[andy3816]

R U sure column B & c did not have two rows merged before, the solution worked fine for me

 

[donlincolnmre]

i'm running Excell 2000 and as soon as i pasted the formula in cell b1 it took over the row underneath it, its kind of odd and smae thing with column c1

 

[donlincolnmre]

well it was the bold character that was causing the problem, i just selected the formula and pasted it in excell some how when i did that it split the row in 2 and when i pasted the formula in a note pad then copy and pasted in excell it worked fine.

Thanks a lot.