Como encontrar o formato de sintaxe de texto específico em uma célula com uma função do Excel?

Question

Estou procurando uma fórmula de célula para encontrar / identificar / validar se uma célula adjacente contiver caracteres no formato correto.

Se houver um caractere "|", ele deverá ser precedido ou seguido por qualquer quantidade de texto e, em seguida, por outro "|" antes que o texto termine ou haja espaço, caso contrário a célula está errada.

Exemplos:

|Name| |Surname| |City| = ok
|Name| |Surname| = ok
|Name| |Surname| New York = ok
New York |Name| |Surname| = ok
|Name| |City |Surname| = wrong
N|ame |City| |Surname| = wrong
|Surname| |ZipCode| = ok

Alguma idéia?

    

Answer 1

Outra maneira de afirmar o problema é:

1. || ( | consecutivos) não são permitidos, nem aqueles que incluem apenas dois ou mais espaços ^* .
2. Se houver um | | (um espaço no meio) no texto a ser validado, ele deve ser imediatamente precedido por qualquer quantidade de texto que não seja | , com | ou outro | | imediatamente antes de , e ele deve ser imediatamente seguido por qualquer quantidade de texto que não seja | seguido por | ou outro | | .
3. Se não houver | | , então não deve haver | ou exatamente dois | .

Condição 1. é, tecnicamente, explicitamente descartada na questão, ( "qualquer quantidade de texto" pode significar nenhum ou somente espaço é permitido) mas pode ser inferido a partir dos exemplos que esta é a intenção do OP.

Com as condições reformuladas como acima, uma solução somente de fórmula torna-se prontamente aparente conforme visto na seguinte planilha:

EstaéafórmulainseridaemB2:B11:

=IF(CHOOSE(MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),TRUE,FALSE,AND(LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0)),"ok","wrong")

Explicação:

A versão prettificada da fórmula é a seguinte:

=
IF(
  CHOOSE(
    MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))),
    TRUE,
    FALSE,
    AND(
      LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2,
      LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0
    )
  ),
  "ok",
  "wrong"
)

As três condições acima podem ser refatoradas para o seguinte:

[a] Deve haver precisamente mais 2 | do que aqueles representados pelos | | s (o primeiro e o último).

e

[b] Se houver qualquer | , deve haver pelo menos dois deles, e os dois primeiros devem ser separados por pelo menos um caractere não-espacial.

A fórmula para [a] é:

LEN(A1)-LEN(SUBSTITUTE(A1,"|",""))-(LEN(A1)-LEN(SUBSTITUTE(A1,"| |","")))/3*2=2

A fórmula para a parte de validação do texto intra- | de [b] é:

LEN(TRIM(MID(A1,FIND("|",A1)+1,FIND("|",A1,FIND("|",A1)+1)-FIND("|",A1)-1)))>0

A outra parte de [b] (ou seja, que não pode haver apenas um | ) é cuidada pela função CHOOSE() , que também cuida do caso quando não há | (necessário, já que este caso de borda causa erros na fórmula [b] e um resultado incorreto para a fórmula [a]).

O primeiro argumento da função CHOOSE() ,

MIN(3,1+LEN(A1)-LEN(SUBSTITUTE(A1,"|","")))

mapeia as possíveis contagens de | s para os índices 1 , 2 e 3 , assim: [0,1,2,3,4,…] → [1,2,3,3,3,…] e, portanto, a função retorna TRUE para uma contagem de 0 , FALSE para uma contagem de 1 e o resultado da função AND() para todas as outras contagens.

_{^* A condição que não permite dois ou mais espaços entre | pode ser relaxada pelo uso da função TRIM() .}

    

Answer 2

Supondo que cada |xyz| esteja em uma célula individual, essa fórmula fará o trabalho para o |xyz| em A1 :

=IFERROR(IF(LEFT(A1,1)="|",IF(FIND("|",A1,2)=LEN(A1),"ok","not ok"),"not ok"),"not ok")

    

Answer 3

Nossa, essa foi uma pergunta difícil!
Mas depois de 4 horas tropeçando no escuro, acho que tenho o padrão correto de RegEx

Eu criei uma pequena função VBA para fornecer um novo tipo de fórmula =RegExTest() .
A fórmula verifica se um determinado padrão corresponde à célula a ser verificada e retorna true ou false.

Primeiro, tentei pesquisar todas as células válidas. Eu falhei, o padrão era muito longo. Então eu tive a idéia: Por que não procurar as combinações de caracteres inválidos?

(\w+\|\w+|(^|\s)\w+\||\|\w+($|\s)|\|\|)

Aideiaportrás

Opadrãotem4condições.Sealgumdelescorresponderaumastringparcialinválida,afórmularetornaráverdadeiro.Casocontrário,retornafalso,oquesignificaquenãoháerrosnacélulamarcada

CONDITION#1orCONDITION#2orCONDITION#3orCONDITION#4\w+\|\w+(^|\s)\w+\|\|\w+($|\s)\|\|"abc|abc"         "^abc|" or " abc|"   "|abc " or "|abc$"       "||"

RegEx em detalhes

1. Um padrão como (xxx | yyy | zzz) é um grupo com 3 condições e uma condição deve ser verdadeira
2. A combinação \| representa o caractere | que deve ter escape porque | sozinho é um caractere especial
3. \w+ significa todas as letras a-z ou números 0-9 ou sublinhados. Como a seguinte função definida pelo usuário usa a configuração ignorecase = true , não é necessário especificar letras maiúsculas A-Z
4. ^ significa o valor inicial da célula e $ para o valor da célula que termina
5. \s significa um único espaço. Eu ignoro o fato de que também significa abas e quebras de linha

Fonte: Linguagem de expressão regular - Referência rápida em MSDN

Como usar o RegEx no Excel

Isso é o que a maioria das pessoas não conhece. Você realmente pode usar o RegEx em qualquer programa do Office. Abra o editor VBA com ALT + F11 , adicione um novo módulo e cole o código

Function RegExTest(rngCell As Range, strPattern As String) As Boolean        
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBScript.RegExp")        
    objRegEx.Global = True
    objRegEx.IgnoreCase = True
    objRegEx.Pattern = strPattern        
    RegExTest = objRegEx.Test(rngCell.Value)        
End Function

Agora, um novo tipo de fórmula está disponível: =RegExTest( <cell_to_check> , <RegEx_pattern> ) que retornará verdadeiro se o padrão corresponder em qualquer lugar ao valor da célula marcada

Caso alguém precise, aqui está um exemplo de pasta de trabalho