+ Reply to Thread
Results 1 to 5 of 5

Large Data Base With Apt # in Street Name

  1. #1
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    windows 10 professional
    Posts
    237

    Large Data Base With Apt # in Street Name

    Using Windows 10 and Excel 2016.

    Large data base that I have to clean up and rid the street name of the Aplt # after the street name, and in some cases, there is an Apt# right after the Street Number. See attached.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,211

    Re: Large Data Base With Apt # in Street Name

    Not elegant, but this works (at least for your small sample)

    =TRIM(LEFT(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#"))-1)&MID(MID(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#")),255),SEARCH(" ",MID(SUBSTITUTE(A2,"# ","#"),SEARCH("#",SUBSTITUTE(A2,"# ","#")),255)),255))
    Glenn



  3. #3
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    windows 10 professional
    Posts
    237

    Re: Large Data Base With Apt # in Street Name

    Thank you.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    23,211

    Re: Large Data Base With Apt # in Street Name

    This is a bit neater:

    =TRIM(LEFT(A2,SEARCH("#",A2)-1)&" "&MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,"# ","#"),LEFT(A2,SEARCH("#",A2)-1),""))&" "," ",REPT(" ",100)),100,100))

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  5. #5
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    windows 10 professional
    Posts
    237

    Re: Large Data Base With Apt # in Street Name

    thank you. I will use it also

+ 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