# Splitting Address in to 2 column.

#### donlincolnmre

##### New Member
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
ABCD
115326 Binney St
213823 Vista Grande Dr
32341 Silk Tree Dr
48238 Noren St
55035 Vista Montana
619762 Hi Top Ln
7525 S Westgate Dr
84932 Park Pl
97364 Cerritos Ave
105730 Southview Dr
115510 Westfield St
1229 Alegria
1312 Heritage
14121 S Grand Ave
154539 N COLDBROOK
163764 Bella Isola Ln
175007 N peck Rd B
194492 CHEVIOT
201600 Skyline Dr
211320 Sheller Dr
223248 Ashgate Ct
2343138 Corte Argento
24480 S Lyon Ave
2524 Charlotte
2610908 Newville Ave
27506 Terra Bella
286586 Valley Dr
295427 Ashworth St
3014646 Willow Leaf Rd
Sheet1

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
in the previous message, the ADDRESS should be split in column B and C

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

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

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,""))

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
ABCD
112442 Softwind Dr12442Softwind Dr
24973 Hilltop Rd
327854 Fair Acres Ln
413963 Kicking Horse Cir
517591 El Cajon Dr
616427 La Paz Dr
Sheet1

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

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

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.

1,196,237
Messages
6,014,163
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back