+ Reply to Thread
Results 1 to 8 of 8

extract street number from address

  1. #1
    Registered User
    Join Date
    09-05-2008
    Location
    Chico, ca
    Posts
    7

    extract street number from address

    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
    Last edited by rickshadey; 10-23-2008 at 08:52 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525
    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

  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,565
    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

  4. #4
    Registered User
    Join Date
    09-05-2008
    Location
    Chico, ca
    Posts
    7

    Not quite what I want

    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...

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525
    Quote Originally Posted by rickshadey View Post
    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

  6. #6
    Registered User
    Join Date
    09-05-2008
    Location
    Chico, ca
    Posts
    7
    Am I doing something wrong? I pasted the formula in and i get a warning saying its a circular argument.

  7. #7
    Registered User
    Join Date
    09-05-2008
    Location
    Chico, ca
    Posts
    7
    still not getting it. I see what you mean with fixed width, but there are too many different lengths of street numbers.

  8. #8
    Registered User
    Join Date
    09-05-2008
    Location
    Chico, ca
    Posts
    7
    OK! You were right on! Thanks...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1