+ Reply to Thread
Results 1 to 10 of 10

Formula to Seperate streetname from street+Postcode string

  1. 09-19-2016, 12:27 AM #1
    FredFitzgerald
    FredFitzgerald is offline
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Formula to Seperate streetname from street+Postcode string

    Hi Guys

    I've been struggling with this trying t clean some data. Basically I have a very large data where street names have postcode attached to them such as Harrington Street KW1. I tried using TRIM(LEFT(SUBSTITUTE(A1,"",255)),255)), however this just returns Harrington and leaves Street. In instances where I have street with more one or two strings such Norbury East Avenue KW2, the formula just returns Norbury. Grateful if anyone could assist with formula to extract the street name from the post code.

    Many thanks
    Reply With QuoteRegister To Reply
  2. 09-19-2016, 12:59 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula to Seperate streetname from street+Postcode string

    A
    B
    C
    1
    Street Name & Postal Code
    Street Name
    Postal Code
    2
    Harrington Street KW1
    =TRIM(SUBSTITUTE(A2,C2,""))
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",50)),50))
    3
    Norbury East Avenue KW2
    =TRIM(SUBSTITUTE(A3,C3,""))
    =TRIM(RIGHT(SUBSTITUTE(TRIM(A3)," ",REPT(" ",50)),50))


    A
    B
    C
    1
    Street Name & Postal Code
    Street Name
    Postal Code
    2
    Harrington Street KW1
    Harrington Street
    KW1
    3
    Norbury East Avenue KW2
    Norbury East Avenue
    KW2


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 09-19-2016, 01:15 AM #3
    mangesh.mehendale
    mangesh.mehendale is offline
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Formula to Seperate streetname from street+Postcode string

    HTML Code: 
    Last edited by mangesh.mehendale; 09-19-2016 at 04:42 AM.
    Don`t care, take care...

    Regards,
    Mangesh
    Reply With QuoteRegister To Reply
  4. 09-19-2016, 01:15 AM #4
    FredFitzgerald
    FredFitzgerald is offline
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Formula to Seperate streetname from street+Postcode string

    Grand!!Many thanks Sixthsense. Really appreciate this. Just one last tweak just so I don't come back to you again.., if there are some street names without postcodes(ie already in the format I want) in
    the array, how do I tweak the formula not to separate some bits or portions of the street name?
    Reply With QuoteRegister To Reply
  5. 09-19-2016, 01:36 AM #5
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Formula to Seperate streetname from street+Postcode string

    We need atleast 10 sample names to understand your data structure
    Reply With QuoteRegister To Reply
  6. 09-19-2016, 06:48 AM #6
    FredFitzgerald
    FredFitzgerald is offline
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Formula to Seperate streetname from street+Postcode string

    Please see below

    Street names+Postcode
    Harrington Road KW1
    Kettering Road KW1
    Queen's Park West NK2
    Albert Road
    Oxford Avenue NK1
    Mallet Street NC1W
    Kirkby Road
    Livermore street LW3
    Killford Avenu KW1C
    Wilmot street KW2
    Reply With QuoteRegister To Reply
  7. 09-19-2016, 07:16 AM #7
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Formula to Seperate streetname from street+Postcode string

    For Street :
    Please Login or Register  to view this content.
    For Postcode :
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-19-2016, 07:59 AM #8
    FredFitzgerald
    FredFitzgerald is offline
    Forum Contributor
    Join Date
    03-09-2014
    Location
    Telford
    MS-Off Ver
    Microsoft 365
    Posts
    101

    Re: Formula to Seperate streetname from street+Postcode string

    Sanram, you just made my day big time!! Munchas Gracias. Each day I'm learning something new from you guys, much appreciated.
    Reply With QuoteRegister To Reply
  9. 09-19-2016, 08:05 AM #9
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Formula to Seperate streetname from street+Postcode string

    You are welcome and thanks for the rep.
    Reply With QuoteRegister To Reply
  10. 09-19-2016, 08:21 AM #10
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to Seperate streetname from street+Postcode string

    Here is another way
    1. Enter this formula in C1 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    2.Enter this one in B1 and copy down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    v A B C
    1 Harrington Road KW1 Harrington Road KW1
    2 Kettering Road KW1 Kettering Road KW1
    3 Queen's Park West NK2 Queen's Park West NK2
    4 Albert Road Albert Road
    5 Oxford Avenue NK1 Oxford Avenue NK1
    6 Mallet Street NC1W Mallet Street NC1W
    7 Kirkby Road Kirkby Road
    8 Livermore street LW3 Livermore street LW3
    9 Killford Avenu KW1C Killford Avenu KW1C
    10 Wilmot street KW2 Wilmot street KW2
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split address from one cell to have street number/name and suburb/state/postcode separated
    By WeedMan in forum Excel General
    Replies: 4
    Last Post: 05-29-2014, 12:05 AM
  2. Extract postcode after last comma in text string
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2013, 09:35 AM
  3. [SOLVED] Trying to separate street number and street name into columns
    By greenmat in forum Excel General
    Replies: 7
    Last Post: 07-27-2012, 04:24 PM
  4. [SOLVED] Trying to seperate messy raw data, street, city, state, zip
    By KraXed112 in forum Excel General
    Replies: 2
    Last Post: 07-21-2012, 03:15 PM
  5. separate street address and street number
    By iwanttoplaywii in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-24-2011, 09:45 PM
  6. how do I convert cell 'Street,#' to '# Street' these are addresse
    By Closing hyperlinked wookbooks upon exit in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2006, 06:23 AM
  7. [SOLVED] How do I sort a column of street number/street name by the stree.
    By JHoleman1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2005, 09:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1