extract street addresses

[claudia80]

Good morning.
Could you help me find the right formula to extract the street addresses as in the orange column?
I tried with python but it cuts the first part.
Thank you

 

[vletm]

claudia80​

one sample
cell C2 =MID(A2,FIND(")",A2,1)+2,FIND("-",SUBSTITUTE(A2," ","-",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(")",A2)-2)
and copy down

 

[pecoflyer]

Withe recent versions try

=TEXTBEFORE((TEXTAFTER(A2;")"));" ";-1)

Replace the ; with , depending on your locale

 

[claudia80]

I translated the formulas into Italian but they don't work

=ESTRAI(A2;TROVA(")";A2;1)+2;TROVA("-";SOSTITUISCI(A2;" ";"-";LUNGHEZZA(A2)-LUNGHEZZA(SOSTITUISCI(A2;" ";""))))-TROVA(")";A2)-2)

=TESTOPRIMA((TESTODOPO(A2;")"));" ";-1)

 

[vletm]

claudia80​

... test this

 

[claudia80]

Now works.
Thank

 

[vletm]

claudia80​

Which one works?
How different from Your translation?

 

[claudia80]

claudia80​

Which one works?
How different from Your translation?

I've tried yours, the other one seems to be can't be translated or doesn't exist in Italian

 

[pecoflyer]

I've tried yours, the other one seems to be can't be translated or doesn't exist in Italian

It certainly exists but your Office version probably does not support it ? Which one are you using ?
You can also install the function translator from MS

 

[claudia80]

It certainly exists but your Office version probably does not support it ? Which one are you using ?
You can also install the function translator from MS

Excel 2010 at work, Office 2021 Professional at home