separating numbers from text in excel

[lsmyth1717]

Hi

I have a spreadsheet containing addresses. Some of the addresses are in the format "123 Main Street", while others are "123, Main Street" and some may be "Apt 1, 123 Main Street".

I am trying to separate the number part of the address from the rest of the street name so that I am able to identify unique street names.

Does anyone know if I am able to do this using a formula? If the addresses all contained commas I would be able to do it, but because some of them contain only spaces I'm not sure how to go about it.

I don't want to have to go through each one individually as there are around 50,000 and I can't separate it using Text to Column as I want to keep the rest of the streetname together.

Thanks,

LSmyth

 

[SkipVought]

LSmyth,

First, you need to thoroughly analyze the data that you are working with in order to create case logic for each unique format.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}