Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Extract multiple uppercase text from within a string

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract multiple uppercase text from within a string

    Hi,

    I have list of addresses that have address and suburb combined into one cell. The suburbs are all uppercase and I'm trying to find a formula to extract these to another cell.

    Examples (located in column B of my spreadsheet)
    Cnr Hollywood Drive and Wharf Road LANSVALE
    Cnr Hamilton Drive & Tongarra Road ALBION PARK
    2A Hillcrest Avenue BARDWELL VALLEY

    I can use the following array formula found in another forum to extract suburbs with a single name but can't get the suburbs that consist of two names

    =RIGHT(B2,MATCH(" ",MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1),0))

    any assistance appreciated

  2. #2
    Board Regular
    Join Date
    Jan 2011
    Posts
    179
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple uppercase text from within a string

    =RIGHT(B2,MATCH(0,--EXACT(UPPER(MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),0)-1)
    Ctrl+Shift+Enter

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple uppercase text from within a string

    Quote Originally Posted by Ganjin View Post
    =RIGHT(B2,MATCH(0,--EXACT(UPPER(MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1)),0)-1)
    Ctrl+Shift+Enter
    Thanks Ganjin,

    That works perfectly!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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