Kim ha una colonna di dati utilizzata per contenere un codice di posizione. Questo codice è composto da una singola lettera seguita da due cifre, come A03 o B12.

Kim vorrebbe formattare in modo condizionale la colonna in modo che qualsiasi cosa inserita nella colonna che non utilizza questo modello venga evidenziata in qualche modo.

Ci sono molti modi in cui questo problema può essere affrontato. Ogni approccio dipende dallo sviluppo di una formula che può essere utilizzata all’interno di una regola di formattazione condizionale per restituire True o False e attivare il formato condizionale. (Questo suggerimento non spiegherà come creare una regola di formattazione condizionale, ma si concentrerà invece sulle varie formule che possono essere utilizzate nella regola. Il modo in cui crei regole di formattazione condizionale è trattato in altri ExcelTips.)

Qualunque sia la formula messa insieme deve testare tre cose:

  • Ci sono esattamente tre caratteri nella stringa.

  • Il primo carattere è una lettera.

  • Il secondo e il terzo carattere sono cifre.

Scoprire se il testo in una cella ha solo tre caratteri è piuttosto facile; puoi usare la funzione LEN per farlo:

=LEN(A1)=3

Anche scoprire se il primo carattere è una lettera è piuttosto facile. In effetti ci sono un paio di modi in cui può essere fatto. Una delle seguenti condizioni restituirà True se il primo carattere è una lettera:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Questi controllano per assicurarsi che solo una lettera maiuscola sia nella prima posizione. Se vuoi accettare anche lettere minuscole, puoi usare una variazione del secondo test:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Se entrambe le lettere maiuscole e minuscole sono accettabili (insieme a praticamente qualsiasi altro simbolo), potresti considerare di utilizzare il seguente test:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Ecco un paio di modi in cui puoi applicare il terzo test: se il secondo e il terzo carattere sono cifre:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Nota che questi approcci trattano gli ultimi due personaggi insieme. Ciò significa che “1”, “11” e “111” supererebbero tutti il ​​test: vengono verificati correttamente come numeri. Se la tua formula controllasse solo le ultime due cifre, questo potrebbe essere un problema, ma il fatto che includerai anche il primo controllo (per la lunghezza totale della stringa nella cella e che deve essere 3), allora non non presenta affatto un problema.

Il trucco, ora, è combinare il tuo approccio di scelta per ciascuno dei tre test in un’unica formula. Questo può essere fatto usando la funzione AND. Scelgo solo il più breve da ciascuno dei test e li combino in questo modo:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Come scritto, questa formula restituisce True se tutti i test vengono superati, il che significa che la cella contiene un codice di posizione con un modello valido. Questo funzionerebbe benissimo come formato condizionale se Kim dovesse formattare la colonna come un colore (ad esempio, verde) e quindi utilizzare il formato condizionale per rimuovere il colore verde. Questo può sembrare al contrario e potresti effettivamente voler applicare un formato solo se il modello non è soddisfatto. In questo caso, racchiudi semplicemente la formula in una funzione NOT per invertire il vero / falso restituito:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Come puoi vedere, usare una formula come questa può essere un po ‘complicato. Potresti, se preferisci, creare una UDF (funzione definita dall’utente) che renderebbe la regola di formattazione condizionale un po ‘più breve. La seguente macro è un buon modo per andare:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Per usare l’UDF nella tua regola di formattazione condizionale, tutto ciò che devi fare è usare la seguente formula:

=IsBadPattern(A1)

Il risultato dell’UDF sarà TRUE se la stringa nella cella di riferimento non corrisponde al modello desiderato. Come scritto, non consentirà l’uso di lettere minuscole nella prima posizione del carattere. Se è necessario consentire le lettere minuscole, non è necessario modificare l’UDF. Modificare invece la formula come segue:

=IsBadPattern(UPPER(A1))

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

Questo suggerimento (9976) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.