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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td=bgcolor:#FFFF00]

data​

[/td][td=bgcolor:#FFFF00]

expected result​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td] Street Sudirman No. 233 Sumahilang Pekanbaru [/td][td]Street Sudirman [/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td] Street Kandis Gg Antara No. 10 Pekanbaru [/td][td]Street Kandis Gg Antara[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td] Street KH. Ahmad Dahlan No. 2C Sukajadi Pekanbaru [/td][td]Street KH. Ahmad Dahlan[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td] Street No. 11 Tampan Pekanbaru [/td][td]etc..[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td] Street Warsito No. 11 Pekanbaru [/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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...