extract street number from address

**rickshadey** · 10-23-2008, 08:21 PM

I have a list of street addresses. I want to alphabetize them. I just want to remove all the text (usually just numbers but not always) up to and including the first space only.
Thanks in advance

**davesexcel** · 10-23-2008, 08:31 PM

Hello,
Give text to columns a try,
select the column with the data and then goto=>data=>text to columns
select the delimited option then next, then select spaces then next and then finish

**Trebor76** · 10-23-2008, 08:35 PM

Hi there,

Assuming your data starts at cell A2 (change as required) the following formula will do the job - just fill down as required:

=TRIM(MID(A2,SEARCH(" ",A2),255))

If this is to be the end result, you can convert all these formulas to values and then delete Column A (in this example).

HTH

Robert

**rickshadey** · 10-23-2008, 08:37 PM

Tried that. The problem is that it breaks up ALL spaces. I don't see an option to make it do only 1.
45 Main St gives me three columns
23 State Line Rd gives me 4
I only want 2. Everything left of the first space. I would've swore i saw someone do this before...

**davesexcel** · 10-23-2008, 08:41 PM

Originally Posted by rickshadey

Tried that. The problem is that it breaks up ALL spaces. I don't see an option to make it do only 1.
45 Main St gives me three columns
23 State Line Rd gives me 4
I only want 2. Everything left of the first space. I would've swore i saw someone do this before...

If you use fixed width, you can double click the separator lines to delete them leaving just the first space