+ Reply to Thread
Results 1 to 13 of 13

Address and Street Name in same cell but not aligned

  1. #1
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Address and Street Name in same cell but not aligned

    Large Data Base. The data was given to me in PDF and I converted using Adobe to Excel. The data have the address field as "Wrap Text".

    Example: address above street name.
    "12803
    NORTHBOROUGH DR"

    How do I get it to be ADDRESS IN ONE CELL COLUMN AND STREET NAME IN ANOTHER CELL COLUMN.

    Some addresses have 3 numbers and others have as many as 5 numbers.

    Windows 10, Excel 2016
    Attached Files Attached Files

  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 2403
    Posts
    43,893

    Re: Address and Street Name in same cell but not aligned

    If by this: "Some addresses have 3 numbers and others have as many as 5 numbers", you mean 5 digits...

    =TRIM(MID(SUBSTITUTE(CHAR(10)&$B3,CHAR(10),REPT(" ",125)),125*COLUMNS($B:B),125))

    copied across. If you mean something else, post representative samples.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Address and Street Name in same cell but not aligned

    you only gave one example and if I understand what you want, this used to be called a carriage return, to remove that I use find and replace.
    find what >> ctrl & enter, replace with what >> hit the space bar once. Then hit replace and see if that is what you want, if so hit replace all and that should do it for you.
    if that is what you are looking for.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Re: Address and Street Name in same cell but not aligned

    Here is the original with the Columns to show the address and street separate. Other addresses and streets will be different but the same type of issue.

    Thanks
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Address and Street Name in same cell but not aligned

    Ok, looking at one more time, Glenn's still seems to give you what you want, you just drag it right.
    Last edited by Sam Capricci; 11-24-2020 at 11:48 AM.

  6. #6
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Re: Address and Street Name in same cell but not aligned

    Yes, I see it pulls out the street number. But not all have same street name, also, some street numbers are 3 numbers. But I can probably deal with it, if the street names can also be in separate column.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Address and Street Name in same cell but not aligned

    Please post a REPRSENTATIVE sample of what you have... not jut ONE!!!
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Re: Address and Street Name in same cell but not aligned

    Yes, thank you
    Attached Files Attached Files

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Address and Street Name in same cell but not aligned

    Please try at
    B3
    =-LOOKUP(0,-LEFT(A3,{1,2,3,4,5}))

    C3
    =TRIM(MID(A3,LEN(B3)+1,99))
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Address and Street Name in same cell but not aligned

    OK. Your input data are a bit of a mess. Some have Char(10) between the number and the alphabetic part, others have two spaces and still others have one.

    This will give you the number:
    =-LOOKUP(1,-LEFT(A3,ROW($1:$100)))

    and this the alphabetic portion, nicely cleaned up:
    =TRIM(SUBSTITUTE(SUBSTITUTE(A3,B3,""),CHAR(10),""))
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Re: Address and Street Name in same cell but not aligned

    Thank you Glenn. It works. Now I can begin my work. Blessings,

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Address and Street Name in same cell but not aligned

    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.

  13. #13
    Forum Contributor
    Join Date
    03-06-2015
    Location
    houston, tx
    MS-Off Ver
    MS 365
    Posts
    490

    Re: Address and Street Name in same cell but not aligned

    Yes. I will Thanks for reminder.

+ 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. Match incomplete or mixed street/physical Address to a clean address format
    By vivek.budhram in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2018, 03:31 PM
  2. Look up specific street address within street address range
    By dbd6lsx in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-11-2015, 09:46 PM
  3. Pulling company name, street address, city, state, and zip from address
    By UNREAL2K4 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2015, 12:53 PM
  4. Replies: 4
    Last Post: 05-29-2014, 12:05 AM
  5. Need to pull street address out of full address in a cell
    By Philalawyer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2014, 09:48 AM
  6. Extract House Number from Street Address into new Cell
    By tamorgen in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-20-2011, 07:48 AM
  7. separate street address and street number
    By iwanttoplaywii in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-24-2011, 09:45 PM

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