Como posso fazer o Excel ignorar caracteres especiais e espaços ao capturar dados de outra célula?

Question

Eu tenho uma pasta de trabalho do Excel que é usada para produzir cotações para clientes. Ele gera automaticamente um número de cotação exclusivo com base no nome do vendedor, no nome da empresa que recebe a cotação, na data e no número da emissão (se duas cotações diferentes forem feitas pelo mesmo vendedor, para a mesma empresa, no mesmo dia, o primeiro terá o número de emissão 01, o segundo terá o número de emissão 02, etc.)

Preenchido com informações fictícias, um número típico de cotação será assim:

JS-ABC-05052016-01

O 'JS' é a sigla do vendedor (John Smith.) O 'ABC' são os três primeiros caracteres do nome da empresa (ABCompany.) O '05052016' é a data de hoje e '01' é o problema número. Esta é toda a informação inserida pelo vendedor.

A fórmula usada na célula que gera e exibe o número da cotação é:

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(D11,3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

A fórmula acima está pegando informações que são inseridas nas células e compilando-as para gerar o número da cotação.

Há um problema, no entanto, quando os três primeiros caracteres do nome da empresa contêm um espaço em branco ou um caractere especial. Por exemplo, um nome de empresa 'A. B. Company 'geraria o seguinte número de cotação:

JS-A. -05052016-01

Outro exemplo é que o nome da empresa "Empresa A & B" geraria o seguinte número de cotação:

JS-A&B-05052016-01

Mais adiante, no processo de cotação, a pasta de trabalho será renomeada para conter o número da cotação. Isso pode causar problemas em que o número da cotação contém um caractere especial, como um período (por exemplo, pode atrapalhar o tipo de arquivo).

Existe uma maneira de fazer o Excel ignorar quaisquer caracteres (incluindo espaços) no nome da empresa que não sejam letras ou números? Por exemplo, fazer uma empresa chamada 'A. & B. Company 'gerar o número da cotação:

JS-ABC-05052016-01

    

Answer 1

Acho que encontrei a resposta para você

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|$&"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Alt + F11 - Inserir - Módulo - Cole-o.

Você pode adicionar mais caracteres especiais à matriz, se desejar. aqui: sSpecialChars = "\/:*?""<>|$&"

Em seguida, no seu celular, digite: =SUBSTITUTE(removeSpecial(A1)," ","")

o resultado de a &b.company é ab.company

Créditos ao autor, acabei de encontrar.

então sua fórmula deve ser:

=UPPER(LEFT(SUBSTITUTE(removeSpecial(P4)," ",""),1)&LEFT(SUBSTITUTE(removeSpecial(P5)," ",""),1)&"-"&LEFT(SUBSTITUTE(removeSpecial(D11)," ",""),3)&"-"&LEFT(SUBSTITUTE(removeSpecial(K6)," ",""),2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),4,2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),7,4)&"-"&SUBSTITUTE(removeSpecial(D5)," ",""))

    

Answer 2

Mais ou menos, substitua o =LEFT(D11,3) na sua função por

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)

Então é

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D11,".","")," ",""),".",""),"&",""),3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

Que pesquisa . , em branco e & - adicione outros se eles existirem.