Chris ha un foglio di lavoro che ha i nomi dei clienti nelle colonne dalla A alla F.Nella colonna G vuole includere una formula che prenderà tutti i nomi dalle sei colonne dei nomi e li concatenerà in una lunga stringa, con i caratteri // tra ogni nome . È possibile che non ci siano nomi in tutte le sei colonne e non dovrebbero esserci delimitatori // iniziali o finali estranei.

Concatenare il testo in Excel è facile. Ad esempio, se hai qualcosa nella cella A2 e vuoi concatenarlo con ciò che è nella cella B2, puoi farlo con questa formula:

=A2 & B2

Potresti includere i delimitatori // tra i due valori semplicemente aggiungendoli al posto giusto:

=A2 & "//" & B2

Questo è abbastanza facile. Usando questo approccio, potresti concatenare tutti e sei i nomi usando la seguente formula:

=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2

Dove le cose si complicano è quando riconosci che alcune di quelle cellule potrebbero non contenere nulla. Pertanto, la formula risulterebbe in delimitatori // finali o finali o in delimitatori doppi (////)

da qualche parte nel mezzo del risultato.

La soluzione più ovvia è utilizzare le istruzioni IF per controllare il contenuto delle celle del nome prima di concatenarle. Questo, tuttavia, può portare a formule incredibilmente lunghe. Ad esempio, la seguente formula eseguirà correttamente il controllo e la concatenazione:

=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))

Sì, questa è una formula a riga singola. (Wow!) Questa formula utilizza l’approccio della nidificazione di istruzioni IF per ottenere il risultato desiderato. Ciò potrebbe funzionare in questo caso particolare, ma la formula è molto vicina al limite di Excel di consentire solo l’annidamento delle istruzioni IF a sette livelli di profondità.

La soluzione al potenziale problema dei livelli nidificati è semplicemente non annidare le istruzioni IF. Invece, puoi valutare ogni cella individualmente e concatenare ciò che viene restituito.

=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"")

& IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Notare che questa formula è molto più breve. Puoi vedere meglio cosa sta facendo se guardi la formula “scomposta” su più righe:

=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Ogni singola istruzione IF nella formula valuta una cella del nome e non restituisce nulla (“”) se la cella non contiene testo, oppure restituisce il delimitatore (//) seguito dal nome. L’intera formula viene quindi racchiusa all’interno dell’istruzione MID che elimina efficacemente il primo // delimitatore nella stringa.

Questa formula può essere ulteriormente abbreviata se, invece di usare la funzione ISTEXT per valutare le celle, fai semplicemente un confronto booleano per scoprire se nella cella c’è del testo, come segue:

=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)

Questa è la stessa identica tecnica, solo un po ‘più breve. (E un po ‘più corto rispetto alla formula originale.)

Questa formula funzionerà benissimo, a condizione che i valori nelle celle del nome siano testo. Se le colonne del tuo nome hanno valori numerici per qualche motivo, puoi facilmente modificare la formula per utilizzare ISBLANK invece di ISTEXT, come mostrato qui:

=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3)

& IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3)

& IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (2173) si applica a Microsoft Excel 97, 2000, 2002 e 2003.