• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

arrange the address in a different way and integration with zip code

Good morning.
I have a city file with postal code, city, provinces etc sheet "ITALIAN CITIES").
Starting from the "ADDRESSES" sheet, I would need a formula that lays out the address by first entering the street (the part after ")", then the postal code and then the city and province initials "in round brackets", after the street and before the city I should enter the postal code (first column of the following sheet), comparing column B of the "addresses" sheet with column "C" of the other sheet and the city and abbreviation of the province present in the address column of the first sheet and columns B and D of the second sheet.

Can you help me?

I made an example in the first cell.
 

Attachments

  • foglio.xlsx
    52.5 KB · Views: 4
You can use these formulas for row 2 then pull down as needed:
in C2: =D2&", "&E2&" "&F2&" "&G2
in D2: =DESTRA(A2;LUNGHEZZA(A2)-RICERCA(")";A2)-1)
in E2: =INDICE('CITTA ITALIANE'!A:A;CONFRONTA(F2;'CITTA ITALIANE'!B:B;0))
in F2: =SINISTRA(A2;TROVA("(";A2)-2)
in G2: =STRINGA.ESTRAI(A2;TROVA("(";A2);4)

or a single formula in C2 without the need of help columns (then pull down as needed):
=DESTRA(A2;LUNGHEZZA(A2)-RICERCA(")";A2)-1)&", "&INDICE('CITTA ITALIANE'!A:A;CONFRONTA(F2;'CITTA ITALIANE'!B:B;0))&" "&SINISTRA(A2;TROVA("(";A2)-2)&" "&STRINGA.ESTRAI(A2;TROVA("(";A2);4)
 
Last edited:
You can use these formulas for row 2 then pull down as needed:
in C2: =D2&", "&E2&" "&F2&" "&G2
in D2: =DESTRA(A2;LUNGHEZZA(A2)-RICERCA(")";A2)-1)
in E2: =INDICE('CITTA ITALIANE'!A:A;CONFRONTA(F2;'CITTA ITALIANE'!B:B;0))
in F2: =SINISTRA(A2;TROVA("(";A2)-2)
in G2: =STRINGA.ESTRAI(A2;TROVA("(";A2);4)

or a single formula in C2 without the need of help columns (then pull down as needed):
=DESTRA(A2;LUNGHEZZA(A2)-RICERCA(")";A2)-1)&", "&INDICE('CITTA ITALIANE'!A:A;CONFRONTA(F2;'CITTA ITALIANE'!B:B;0))&" "&SINISTRA(A2;TROVA("(";A2)-2)&" "&STRINGA.ESTRAI(A2;TROVA("(";A2);4)

thanks for the formulas. the formula " E2:" doesn't work.
the zip code is linked to the city, not the province.
so when the city and province match then the postal code should be entered
 
thanks for the formulas. the formula " E2:" doesn't work.
the zip code is linked to the city, not the province.
so when the city and province match then the postal code should be entered
I tried again and it went well.
Thank you for everything (even for using the formulas in Italian)
Have a good time :)
 
Back
Top