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

Question

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

O que eu recebo:

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.

    

Answer 1

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 - link
Visite a página link
Definir sabor para "pcre (php)" - é a opção padrão
Expressão regular: (?<=;)(#\d+;#)|(;#\d+$) Sinalizador: 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)]
Calc 'Find & Substituir 'janela permite usar expressões regulares.

Ctrl + H
Localizar: (?<=;)(#\d+;#)|(;#\d+$)
Substituir por: um espaço ou espaço em branco Na secção "Outras opções", assinale "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
Embrulhe ao redor: verificado
Modo de pesquisa: expressão regular
Encontre o que: (?'name_surname'[^;#]+\s[^;#]+;)(#\d+(;#|$))
Substituir por: $+{name_surname}
Clique no botão "Substituir tudo"

Significado:
"Encontrar 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 me focar em soluções bastante rápidas, mas totalmente funcionais, então acho que o sth pode ser tirado disso.

    

Answer 2

Eu tentei a fórmula. Não foi bonito. No entanto, tenho certeza de que há outras maneiras de fazer isso.

Esta fórmula levará sua sequência na célula A1 e removerá as seqüências #123 e #2345 . Você sempre pode expandir a técnica ainda mais, se desejar. Ele encontra as ocorrências de "#" e depois divide a string 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.