+ 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
    MS 365
    Posts
    446

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,756

    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
    MS 365
    Posts
    446

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,756

    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
    MS 365
    Posts
    446

    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)

Similar Threads

  1. Large data base with Addresses that need correction
    By pick44 in forum Excel General
    Replies: 1
    Last Post: 02-15-2019, 06:09 PM
  2. [SOLVED] Large data base that needs some cells filled in...see attachment
    By pick44 in forum Excel General
    Replies: 3
    Last Post: 07-21-2018, 05:18 PM
  3. Replies: 6
    Last Post: 09-24-2017, 10:07 AM
  4. [SOLVED] Very large data base and need to separate address from street name
    By pick44 in forum Excel General
    Replies: 2
    Last Post: 08-22-2017, 12:23 PM
  5. Replies: 6
    Last Post: 03-28-2017, 04:40 PM
  6. [SOLVED] Find or Match and Replace-large data base
    By kunjanee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-28-2017, 02:42 AM
  7. Need to pull address ranges with accompanying data for large data base
    By pick44 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-09-2015, 11:12 AM

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