Questão Excel 2010 - Remover caracteres de uma célula, o número de caracteres varia


Eu tenho uma lista exportada de outro aplicativo. Os resultados são retornados com caracteres extras entre eles.

O que eu ganho:

Mary One;#123;#Bob Two;#2345;#Charles Three;#445

O que eu quero:

Mary One; Bob Two; Charles Three

O número de usuários que podem ser listados na célula não é consistente. O complicador adicional é que os números variam entre 3 e 4 caracteres.

Estou tentando evitar que isso seja muito complicado. Idealmente, essa seria uma fórmula que eu comunicaria ao restante do meu departamento para usar conforme necessário.

Eu vou estar usando SUBSTITUTE e LEN, mas eu poderia usar alguma ajuda para manter isso limpo.


0


origem


DEVE ser uma fórmula? Criar uma função definida pelo usuário usando o RegEx seria uma maneira fácil de lidar com o problema. Porém, distribuir o UDF pode representar dificuldades. - B540Glenn
Você quer a resposta em uma célula ou em várias células? (Ou seja, A1 = string longa, B1 = "Mary One;", C1 = "Bob dois", etc.) - bvaughn


Respostas:


Eu vou apresentar aqui algumas opções para escolher, mas todas elas dependem de expressões regulares. É possível não usar o regex, mas acho que pode envolver um pouco mais de tempo.

[Opção 1 - https://regex101.com/]
Visite a página https://regex101.com/
Definir o sabor para "pcre (php)" - é a opção padrão
Expressão regular: (?<=;)(#\d+;#)|(;#\d+$) Bandeira: g
Cadeia de teste: Mary One;#123;#Bob Two;#2345;#Charles Three;#445
Substituição:  // um espaço (ou pode ser deixado em branco)
Saída: Mary One; Bob Two; Charles Three

A saída precisa ser aparada, pois há um espaço adicional no final.

[Opção 2 - LibreOffice Calc (portátil)]
A janela Calc 'Find & Replace' permite usar expressões regulares.

Ctrl + H
Encontrar: (?<=;)(#\d+;#)|(;#\d+$)
Substitua por: um espaço ou em branco
Na seção 'Outras opções', marque 'Expressões regulares'
Clique no botão "Substituir tudo".

[Opção 3 - Notepad ++]
Eu estou mencionando o N ++ aqui, pois é uma ferramenta muito poderosa enquanto se trabalha com muitos tipos de textos. Seu poder fica em grande parte em vários plugins. Mas aqui vou me referir a uma janela padrão Substituir. Regex permite dividir as partes correspondentes do texto. Isso é chamado de agrupamento. Cada grupo pode ter um nome e esse nome pode ser usado na substituição.

Ctrl + H → Substituir guia
Envolver: verificado
Modo de pesquisa: expressão regular
Encontre o que: (?'name_surname'[^;#]+\s[^;#]+;)(#\d+(;#|$))
Substituir com: $+{name_surname}
Clique no botão "Substituir todos"

Significado:
‘Encontre o que’ encontra tudo - o texto desejado e indesejado.
"Substituir por" substitui o texto bruto por apenas partes desejadas.

[Opção 4 - Excel - função definida pelo usuário (VBA)]
Abra o editor do Visual Basic (Alt + F11)
Adicionar referência:
Ferramentas -> Referências -> Microsoft VBScript Regular Expressions 5.5

Option Explicit
Function leaveNames(CellValue As Variant)
    Dim RegEx As RegExp
    Dim Expr As String
    Set RegEx = New RegExp
    Expr = "(#\d+;#)|(;#\d+$)"
    RegEx.Global = True
    RegEx.IgnoreCase = False
    RegEx.MultiLine = False
    RegEx.Pattern = Expr
    leaveNames = RegEx.Replace(CellValue, "")
End Function


[Resumo]
Eu tentei focar em soluções bastante rápidas, mas totalmente funcionais, então eu acho que sth pode ser tirado disso.


1



A opção 4 provou ser exatamente o que meus usuários precisam. Muito obrigado! - DeNaeL
Infelizmente, acabei de executá-lo em um novo relatório e ele quebra se houver mais de dois nomes. Na célula com um terceiro nome eu tenho: Tom Kent, Julie Y Busse; # 562; DeNae Leverentz - DeNaeL
Eu acho que a principal diferença entre a string da questão e o comentário é que no último, nem todos os nomes começam com o hash. Modifiquei a expressão regular adicionando um ponto de interrogação que torna o hash opcional: Expr = "(#\d+;#?)|(;#\d+$)". A nova expressão ainda funciona com as strings anteriores. Se você encontrou mais alguns cenários negativos, então provavelmente a causa raiz ficará na regex mas, felizmente, isso pode ser facilmente alterado. - wlod
Obrigado! Eu não encontrei nenhum outro cenário negativo! - DeNaeL


Eu tentei a fórmula. Não foi bonito. Porém, tenho certeza de que existem outras maneiras de fazer isso.

Esta fórmula levará sua string na célula A1 e removerá a string #123 e #2345 cordas. Você sempre pode expandir a técnica ainda mais, se desejar. Ele encontra as ocorrências de "#" e, em seguida, divide a cadeia em pedaços menores.

=LEFT(A1,SEARCH("#",A1,1)-1) &  MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,SEARCH("#",MID(RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),SEARCH("#",RIGHT(A1,LEN(A1)-SEARCH("#",A1,1)),1)+1,LEN((A1))),1)-1)

Não é nem limpa nem facilmente comunicável.

Eu sugiro uma função definida pelo usuário. Aqui é um artigo explicando como configurar e distribuir uma função do usuário. Dê uma chance ao UDF e, se tiver dúvidas sobre o processo, pergunte.


0