Extracting 2nd Word from cell

  • If I have a list of addresses how do I extract the street name.


    23 john street
    1 Happy street
    567 Jacobson street
    34 Sunshine street
    22 Alfredington street


    I've tried some =MID(A1, FIND(" ", A1, 1), FIND(" ",A1, FIND(" ",A1)+1)-FIND(" ", A1, 1))
    but for some reason it keeps stuffing up after a few cells and can't figure out why.


    Thank-you :)

  • Re: Extracting 2nd Word from cell


    Try


    =TRIM(MID(A1,LEN(LOOKUP(9^9,LEFT(A1,COLUMN(1:1))*1))+1,2^15))

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!