How do you seperate a street number from street name in Excel?

Question

If I have a column of data in an excel spreadsheet that includes a street number and street name how can I create a column of just street number and a column of just street name? For example 1 cell contains the words "10 Main Street" how can I execute a function that will give me a column with the "10" and just leave or create a column for the "Main Street". Keep in mind there may be longer numbers like 2100 Main Street or 7A Main Street.

Answer 1

I'm sure there must be another way, but I think I'd end up inserting a column to the right and manually 'cut and paste' the street names into the inserted column. Sorry I can't be of more help
Wishes n Dreams XxX

PS - Just got this from the help function....

Divide text across cells

Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one column wide.
Note There must be one or more blank columns to the right of the selected column or the data to the right of the selected column will be overwritten.

On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.

PPS -
It works - I've just tried it
Wishes n Dreams XxX

Answer 2

I keep data files and cannot fathom why you would want to separate the street number from the street name, however, if that's your wish, because there are so many combinations, you may have to do it manually. You would insert a column after the number/street, then select the street, cut, then paste in the now blank column.

Someone much more knowledgeable than I would have to help you with a macro.

I am always up to learning new procedures. Would you mind sending a message and telling me for what reason you want to do this? How will it help you with whatever it is you are trying to do?

Answer 3

Do it manually