Separate address numbers from street name

[Walt Moeller]

I would like to separate the street number from the street name.

Currently I have a large database with one column with an address displayed
as: 108 Delmar

I would like to keep this column but would like to have the number and the
street name set up in two new columns

 

[PCLIVE]

As long as the format of all the address are:

Number, space, street name

Then you could use the following two formulas. These formulas assume that
your data begins at A1. Adjust if needed.

For the number:
=LEFT(A1,FIND(" ",A1)-1)

For the streen name:
=LEFT(A1,FIND(" ",A1)-1)


HTH,
Paul

 

[Per Jessen]

Hi Walt

Use Text to columns on the Data menu.

Select the cells you want to split, now goto Data > Text to column >
Delimited > Next > Check Space > Next > In Destination enter the cell where
the first number should appear > Finish

This should do it.

Regards,
Per

 

[Teethless mama]

For number:
=LEFT(A1,FIND(" ",A1)-1)

For Street name:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

 

[PCLIVE]

Oops! Just realized my formulas were the same.

Street name:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

--

As long as the format of all the address are:

Number, space, street name

Then you could use the following two formulas. These formulas assume that
your data begins at A1. Adjust if needed.

For the number:
=LEFT(A1,FIND(" ",A1)-1)

For the streen name:
=LEFT(A1,FIND(" ",A1)-1)


HTH,
Paul

--