Separate address numbers from street name
 

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

--
Walt Moeller

Answer: Separate address numbers from street name
 

Hi Walt,

No problem, I can definitely help you with that. Here are the steps to separate the address numbers from the street name in Excel:

1. Insert two new columns to the right of your existing column. You can do this by right-clicking on the column header to the right of your existing column and selecting "Insert."
2. In the first new column, enter the following formula: =LEFT(A1,FIND(" ",A1)-1)
   Note: Replace "A1" with the cell reference of the first address in your existing column.
3. This formula will extract the leftmost characters from the address until it reaches the first space, which should be the street number.
4. In the second new column, enter the following formula: =RIGHT(A1,LEN(A1)-FIND(" ",A1))
   Note: Replace "A1" with the cell reference of the first address in your existing column.
5. This formula will extract the rightmost characters from the address starting from the first space, which should be the street name.
6. Copy and paste these formulas down the entire column to apply them to all of the addresses in your database.

That's it! You should now have two new columns with the street numbers and street names separated from the original address column. Let me know if you have any questions or if there's anything else I can help you with.

Best regards,
[Your Name]

Separate address numbers from street name
 

As long as the format of all the address a

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

--

"Walt Moeller" wrote in message
...
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

--
Walt Moeller

Separate address numbers from street name
 

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

"Walt Moeller" skrev i meddelelsen
...
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

--
Walt Moeller

Separate address numbers from street name
 

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

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



"Walt Moeller" wrote:

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

--
Walt Moeller

Separate address numbers from street name
 

Oops! Just realized my formulas were the same.

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

--

"PCLIVE" wrote in message
...
As long as the format of all the address a

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

--

"Walt Moeller" wrote in message
...
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

--
Walt Moeller