Separate address numbers from street name

W

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
 
P

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
 
P

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
 
T

Teethless mama

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

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

PCLIVE

Oops! Just realized my formulas were the same.

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

--

PCLIVE said:
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

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top