# 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])

• Re: Extracting 2nd Word from cell

Try
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50*1,50))
Instead of *1, can use *2 for second word, *3 for third word, etc.

• Re: Extracting 2nd Word from cell

23 East South street

=MID(A1,FIND(" ",A1)+1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND(" ",A1)-1)

• Re: Extracting 2nd Word from cell

Thank-you so much, you have all been so helpfu

## Participate now!

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