Excel Apagar caracteres que ultrapassem limite

[henry796]

Boa tarde!

 

Tenho uma planilha com várias colunas e preciso limitar em algumas o número de carácteres. Já encontrei aqui no fórum como fazer isso o problema é que minha planilha tem mais de 3.000 linhas e fazendo da forma que foi mostrada aqui (Validação de dados) eu teria que apagar linha por linha os caracteres que ultrapassam o limite. 

 

Existe alguma forma de fazer o Excel "cortar" os caracteres que ultrapassam o limite automaticamente? Por VBA, por exemplo. 

 

Agradeço desde já. 

[Edson Luiz Branco]

@henry796, boa tarde.

 

Monte um arquivo com o layout de como estão seus dados com pelo menos algumas linhas de exemplo, facilita bastante quem vai criar o código.

[henry796]

34 minutos atrás, Edson Luiz Branco disse:

@henry796, boa tarde.

 

Monte um arquivo com o layout de como estão seus dados com pelo menos algumas linhas de exemplo, facilita bastante quem vai criar o código.

 

@Edson Luiz Branco peguei algumas linhas e estou anexando ao tópico um exemplo do arquivo.

 

Na primeira linha, ao final do texto de cada coluna tem o número de caracteres permitidos para a célula. 

ex_chd.xlsx

[Edson Luiz Branco]

@henry796, bom dia.

 

Dá uma olhada se é isso.

 

Observações:

• o código considera que na primeira linha terá a especificação das larguras de cada coluna, conforme o modelo que você anexou
• o código considera ainda que a segunda linha é cabeçalho de colunas, portanto não será truncada. Os dados começam mesmo à partir da terceira linha.
• deixei o atalho CTRL+H para executar a macro.

 

O código ficou assim:

Sub TruncarValores()
  Dim largMax() As Variant, i As Long, nCols As Long
  Dim rgDados As Range, rgLargMax As Range, cel As Range
    Set rgDados = Worksheets("Plan1").Range("A1").CurrentRegion
    Set rgLargMax = rgDados.Rows(1)
    Set rgDados = rgDados.Offset(2, 0).Resize(rgDados.Rows.Count - 2, rgDados.Columns.Count)
  nCols = rgDados.Columns.Count
  ReDim largMax(1 To nCols)
  For i = 1 To nCols
    largMax(i) = Split(rgLargMax.Columns(i).Value, ",")
    largMax(i) = largMax(i)(UBound(largMax(i)))
    If IsNumeric(largMax(i)) Then
      largMax(i) = CLng(largMax(i))
      For Each cel In rgDados.Columns(i).Rows
        If Len(cel.Value) > largMax(i) Then cel.Value = Left(cel.Value, largMax(i))
      Next cel
    End If
  Next i
End Sub

 

ex_chd.zip

[henry796]

@Edson Luiz Branco perfeito!!! Funcionou certinho, exatamente como eu precisava. 

 

Só me tira uma dúvida, em qual parte do código ele entende que deve limitar ao número que tem na célula da linha A?

 

E se eu colocar algum outro número no campo, ex.: 2email,20 - tem algum problema?

 

Novamente agradeço, atendeu perfeitamente. 

[Edson Luiz Branco]

@henry796, que bom que deu certo.

 

20 minutos atrás, henry796 disse:

...em qual parte do código ele entende que deve limitar ao número que tem na célula da linha A?

 

Acho que por linha A você quis dizer linha 1, não é mesmo? É nessas duas linhas aqui:

    largMax(i) = Split(rgLargMax.Columns(i).Value, ",")
    largMax(i) = largMax(i)(UBound(largMax(i)))

como nas células da linha 1 o valor após a última vírgula define o tamanho do campo, a primeira das "instruções" acima explode o texto de cada célula e coloca em uma sub-matriz. Então largMax(1) correspondente à primeira célula ("A2_FILIAL,C,6") conterá uma sub-matriz:

largMax(1)(1) = "A2_FILIAL"

largMax(1)(2) = "C"

largMax(1)(3) = "6"

 

e assim por diante

 

Como estou interessado somente no último valor ("6"), a segunda instrução captura o valor do último elemento da sub-matriz (o que tem o maior subscritor) através da função UBound.

 

20 minutos atrás, henry796 disse:

E se eu colocar algum outro número no campo, ex.: 2email,20 - tem algum problema?

 

Não deverá ter problemas, se você separar com vírgulas e deixar o valor ao final, sem espaços em branco.

[henry796]

1 minuto atrás, Edson Luiz Branco disse:

Acho que por linha A você quis dizer linha 1, não é mesmo? É nessas duas linhas aqui:

 

Foi, escrevi errado. 

 

Perfeito, entendi como funciona. 

 

@Edson Luiz Branco muito obrigado! Me ajudou bastante!

 

Um abraço.

[brendomark]

boa noite eu estou com o mesmo problema tentei fazer a solução proposta por voce e nao consegui se voce puder me ajudar ficarei grato.

 

segue em anexo a planinha que to fazendo, somente o campo nome da conta deve ter essa função de truncar valores que ultrapassa 40 caracteres.

LAYOUT PLANO DE CONTAS.xlsx

[Edson Luiz Branco]

Bem vindo ao Fórum Office do Clube do Hardware, @brendomark

 

Seu caso é um pouco diferente. Clique com o botão direito na "orelha" da Planilha1 e escolha "Exibir Código".

 

Copie e cole o código abaixo e depois faça um teste inserindo algo com mais de 39 caracteres na coluna Nome da Conta da sua tabela:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rg As Range, cél As Range
  Set rg = Intersect(Target, Me.Range("Tabela1[NOME DA CONTA]"))
  If Not rg Is Nothing Then
    With Application
     .ScreenUpdating = False
       .EnableEvents = False
         For Each cél In rg
           If (Not cél.HasFormula) And (Len(cél.Value) > 39) Then
             cél.Value = Left(cél.Value, 39)
           End If
         Next cél
       .EnableEvents = True
     .ScreenUpdating = True
    End With
  End If
  Set rg = Nothing: Set cél = Nothing
End Sub

Após testar, nos informe se era isso ou não...

[Visitante]

16 horas atrás, brendomark disse:

segue em anexo a planinha que to fazendo, somente o campo nome da conta deve ter essa função de truncar valores que ultrapassa 40 caracteres.

 

Se a sua planilha ainda está vazia conforme a que você disponibilizou, refaça a Validação de Dados que está aplicada na coluna C, assim:

1. selecione C2

2. menu Validação de Dados / Limpar tudo / Permitir: Comprimento de Texto / Dados: é menor ou igual a / Máximo: 40

3. marque Aplicar alterações a todas as células ...

4. Mensagem de entrada: Certifique-se de que a célula tenha no máximo 40 caracteres / OK

 

Ou, se a sua planilha já contiver dados e você quiser truncar as células da coluna C que tenham mais de 40 caracteres, então rode uma cópia do código abaixo instalado em um módulo comum, e em seguida refaça a Validação.

Sub Trunca40Caracteres()
 Dim k As Long, nc As Range
 k = [C:C].Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
 For Each nc In Range("C2:C" & k)
  If Len(nc.Value) > 40 Then nc.Value = Left(nc.Value, 40)
 Next nc
End Sub

 

[brendomark]

Muito obrigado esta funcionando corretamente. 

[Patropi]

@brendomark

 

Se a dúvida foi sanada, marque o tópico como Resolvido, conforme instrução na minha assinatura.

 

[]s

[brendomark]

boa  tarde, me deparei com o mesmo problema.

Tenho uma Planilha e preciso limitar a quantidade de caractere de algumas colunas. tentei colocar o mesmo código na minha primeira duvido no fórum, porém, não funcionou.

os dados da coluna C4 para baixo precisa ter 40 caractere e os da coluda D4 para baixo precisa ter 24.

seguem o modelo da minha planilha.

teste.rar

[Visitante]

Considerando que a planilha seja a LAYOUT, pois você não informou, substitua as fórmulas existentes conforme abaixo.

 

em C4

=SE(DADOS!B7="";"";ESQUERDA(DADOS!B7;40))

em D4

=ESQUERDA(C4;24)

 

[brendomark]

Muito obrigado, eu nao tinha pensado nessa solução. super me ajudou. obrigado