Chcę zastosować walidację danych do numerów referencyjnych, aby wymusić następujący układ (tylko wielkie litery):

xx_nnx-xx_nnn_nn-xxx

X = numery

N = litery

ex: 12_ab1-23_abc_ab-123

Poniższa niestandardowa formuła umożliwia to wszystkim z wyjątkiem numerów - czy jest dla tego jakiś obejście?

Nie chcę używać * Ponieważ pozwala na więcej znaków niż chcę.

=COUNTIF(A1,"??_???-??_???_??-???")

0
Snottus 22 marzec 2020, 12:33

1 odpowiedź

Najlepsza odpowiedź

Możesz wybrać AND, aby dodać stan funkcji, którą już napisałeś. na przykład Następujące przetestuje pozycje, które będą numeryczne.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0))

Uwagi: Jest to dość podstawowe podejście i może potrzebować pewnych poprawek, jeśli masz przypadki związane z wykorzystaniem dziesiętnych itp.

Edytuj: Możesz spróbować poniżej podejścia do sprawdzania wielkiej litery w określonych pozycjach.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),INDEX(FREQUENCY(-CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),{-91,-65,0}),2)=7)

edytuj2: okazało się to trudniejsze niż wyobrażałem sobie. Po formule działa w DV dla komórki {X0}}.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),MIN(FLOOR(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),65))=65,MAX(CEILING(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),90))=90)

Ale z powodu jakiegoś dziwacznego Excel nie pozwoli mi po prostu wkleić go. Napisałem taki sam kod, który stosuje DV do Cell A1 i zaskakująco akceptuje tę samą długą formułę przez kod. Upewnij się, że usuniesz DV w komórce przed uruchomieniem tego kodu.

With Range("A1")
    .Value = "12_AB1-23_ADC_AZ-123"
    .Validation.Add xlValidateCustom, , , "=AND(COUNTIF(A1,""??_???-??_???_??-???""),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),MIN(FLOOR(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),65))=65,MAX(CEILING(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),90))=90)"
End With

Raz tam możesz dostać go w każdej innej komórce, po prostu robiąc copy >> Paste Special >> Validation.

Dla jasności używam programu Excel 2016.

1
shrivallabha.redij 26 marzec 2020, 12:14