Using MS Excel 2002, how would I separate a street # from a street name into separate columns?

Question

The street #s and names are different lengths. I want to have the entire street name in the 2nd column. For example column 2 would include "Park Place Ave.," not just "Park."

Does anyone else have any clearer instructions? The 1st two answers are too vague. I do appreciate the effort though.

Answer 1

Here's how it's done using Text to Columns:

Step 1: Put all of your addreses into one column
Step 2: Copy column and paste it in next column to right
Step 3: Select the first address column
Step 4: From menu bar, select Data, Text to Columns
Step 5: Select Delimited then click Next
Step 6: In Delimiters area have only Space checked, click Next
Step 7: In Data Preview window, click on second column then select "Do not import column (skip)"
Step 8: Repeat for rest of columns to right of 1st column in Data Preview window and click Finish
Step 9: Select the 2nd address column
Step 10: Repeat steps 4 - 6.
Step 11: In Data Preview window, click first column then select "Do not import column (skip)"
Step 12: Click Finish

And there you have it. Done in 12 simple steps. You should now have two columns. The first column with just the numbers, and the second column with the rest of the address. Sorry I didnt' go into further detail. Lost what I wrote the first time, so kept it simple and concise as I could for the 2nd time.

Feel free to ask me if you have any questions.

Answer 2

If all the street numbers are the same length, you can use the Text to Columns function to separate them by fixed length. If the street numbers are of variable length and there is a space between the number and the street name, then you can use the Text to Columns function to separate them using "space" as the separation value. The only problem with the second method is you will end up with some street names being separated into more than one column it the street name has more than one word in it. If that happens, you can use the Cocantenate function to basically "add" the two text strings together again for the correct street name.

Answer 3

Use the Text to Columns feature found in the Data menu. Just follow the wizard.

Answer 4

Well, we will do it in function
If you are expecting the number then the name, or the name and then the number
Send me more details and I can do it with you, I like these stuff

Read my profile, I am XLMan