Extract Text Only Before String "No."

[muhammad susanto]

hi all..

how to extract data/text only before string "No."
this my layout :
Excel 2007 32 bit

	E​	F​
2​	data​	expected result​
3​	Street Sudirman No. 233 Sumahilang Pekanbaru	Street Sudirman
4​	Street Kandis Gg Antara No. 10 Pekanbaru	Street Kandis Gg Antara
5​	Street KH. Ahmad Dahlan No. 2C Sukajadi Pekanbaru	Street KH. Ahmad Dahlan
6​	Street No. 11 Tampan Pekanbaru	etc..
7​	Street Warsito No. 11 Pekanbaru

Sheet: Sheet1

any help much appreciated...

m.susanto

 

[Rick Rothstein]

Give this formula a try...

=LEFT(E3,SEARCH("No.",E3)-2)

 

[muhammad susanto]

Give this formula a try...

=LEFT(E3,SEARCH("No.",E3)-2)

hi Rick, thanks a lot..worked well...