Função de divisão do VBA - Dividir string de texto em matriz

Usando a função de divisão do VBA

A função VBA Split permite separar as partes do componente de dentro de uma string de texto padrão, onde cada componente usa um caractere delimitador específico, por exemplo, uma vírgula ou dois pontos. É mais fácil usar do que escrever código para pesquisar os delimitadores na string e, em seguida, extrair os valores.

Ele pode ser usado se você estiver lendo em uma linha de um valor separado por vírgulas (arquivo CSV) ou se tiver um endereço de correspondência que está em uma linha, mas deseja vê-lo como várias linhas.

A sintaxe é:

1	Expressão de divisão, delimitador [opcional], limite [opcional], comparação [opcional]

A função VBA Split tem quatro parâmetros:

• Expressão - A sequência de texto que você deseja dividir em diferentes partes.
• Delimitador (opcional)- string ou caractere não imprimível - define o caractere delimitador que será usado para a divisão. Se nenhum caractere delimitador for fornecido, o padrão de um espaço será usado.
• Limite (opcional) - número - Define quantas divisões serão feitas. Se estiver em branco, todas as divisões disponíveis serão feitas dentro da string. Se for definido como 1, nenhuma divisão será feita. Basicamente, ele permite que você separe um número específico de valores começando no início da string, por exemplo, onde a corda é muito longa e você só precisa das três primeiras divisões.
• Comparar (opcional) - Se o seu delimitador for um caractere de texto, ele será usado para alternar se o delimitador diferencia maiúsculas de minúsculas ou não. Os valores são vbBinaryCompare (sensível a maiúsculas e minúsculas) e vbTextCompare (não diferencia maiúsculas de minúsculas).

A função de divisão sempre retorna uma matriz.

Exemplo simples da função de divisão

123456789101112

Sub SplitExample ()'Definir variáveisDim MyArray () As String, MyString As String, I As Variant'String de amostra com delimitadores de espaçoMyString = "Um Dois Três Quatro"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString)'itera através da matriz criada para mostrar cada valorPara Cada I Em MyArrayMsgBox IProximo euEnd Sub

Neste exemplo, nenhum delimitador é especificado porque todas as palavras têm um espaço entre elas, portanto, o delimitador padrão (espaço) pode ser usado.

A matriz não tem dimensões e é definida como uma string. A variável I, que é usada no loop For… Next deve ser dimensionada como uma variante.

Quando esse código for executado, ele mostrará quatro caixas de mensagem, uma para cada uma das divisões, por exemplo, Um dois três. Quatro.

Observe que se houver um espaço duplo entre as palavras na string, isso será avaliado como uma divisão, embora sem nada nela. Este pode não ser o resultado que você deseja ver.

Você pode corrigir esse problema usando a função Substituir para substituir quaisquer espaços duplos por um único espaço:

1	MyString = Substituir (MyString, "", "")

Um espaço à direita ou à direita também pode causar problemas ao produzir uma divisão vazia. Muitas vezes são muito difíceis de ver. Você pode remover esses espaços estranhos usando a função Trim:

1	MyString = Trim (MyString)

Usando a função de divisão com um caractere delimitador

Podemos usar um delimitador de ponto e vírgula (;). Isso é freqüentemente encontrado em strings de endereços de e-mail para separar os endereços. Você pode receber um e-mail que é compartilhado com vários colegas e você deseja ver uma lista em sua planilha de quem ele foi enviado. Você pode copiar facilmente os endereços de e-mail das caixas 'Para' ou 'Copiar' do e-mail e para o seu código.

123456789101112131415

Sub SplitBySemicolonExample ()'Definir variáveisDim MyArray () As String, MyString As String, I As Variant, N As Integer'String de amostra com delimitadores de ponto e vírgulaMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, ";")'Limpar a planilhaActiveSheet.UsedRange.Clear'iterar através da matrizPara N = 0 para UBound (MyArray)'Coloque cada endereço de e-mail na primeira coluna da planilhaIntervalo ("A" & N + 1) .Value = MyArray (N)Próximo NEnd Sub

Observe que um loop For… Next é usado para iterar pelo array. O primeiro elemento na matriz sempre começa em zero e a função Upper Bound é usada para obter o número máximo de elementos.

Depois de executar este código, sua planilha ficará assim:

Usando um parâmetro de limite em uma função de divisão

O parâmetro limit permite que um número específico de divisões seja feito desde o início da string. Infelizmente, você não pode fornecer uma posição inicial ou uma gama de divisões a serem feitas, por isso é bastante básico. Você pode construir seu próprio código VBA para criar uma função para fazer isso, e isso será explicado posteriormente neste artigo.

123456789101112131415

Sub SplitWithLimitExample ()'Criar variáveisDim MyArray () As String, MyString As String, I As Variant, N As Integer'String de amostra com delimitadores de vírgulaMyString = "Um, Dois, Três, Quatro, Cinco, Seis"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, ",", 4)'Limpar a planilhaActiveSheet.UsedRange.Clear'Iterar através da matrizPara N = 0 para UBound (MyArray)'Coloque cada divisão na primeira coluna da planilhaIntervalo ("A" & N + 1) .Value = MyArray (N)Próximo NEnd Sub

Depois de executar este código, sua planilha ficará assim:

Apenas os três primeiros valores de divisão são mostrados separadamente. Os três últimos valores são mostrados como uma longa string e não são divididos.

Se você escolher um valor limite maior que o número de delimitadores em uma string, isso não produzirá um erro. A string será dividida em todas as suas partes componentes, como se o valor limite não tivesse sido fornecido.

Usando o parâmetro de comparação em uma função de divisão

O parâmetro Compare determina se o delimitador diferencia maiúsculas de minúsculas ou não. Isso não é aplicável se os delimitadores forem vírgulas, ponto e vírgula ou dois pontos.

Observação: em vez disso, você sempre pode colocar o texto de comparação de opção <> na parte superior do seu módulo para eliminar a diferenciação de maiúsculas e minúsculas para todo o módulo.

123456789101112131415

Sub SplitByCompareExample ()'Criar variáveisDim MyArray () As String, MyString As String, I As Variant, N As Integer'Amostra de string com X delimitadoresMyString = "OneXTwoXThreexFourXFivexSix"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, "X",, vbBinaryCompare)'Limpar a planilhaActiveSheet.UsedRange.Clear'iterar através da matrizPara N = 0 para UBound (MyArray)'Coloque cada divisão na primeira coluna da planilhaIntervalo ("A" & N + 1) .Value = MyArray (N)Próximo NEnd Sub

Neste exemplo, a string a ser dividida usa o caractere 'X' como um delimitador. No entanto, nesta string, há uma mistura de caracteres 'X' maiúsculos e minúsculos. O parâmetro Compare na função Split usa um caractere 'X' maiúsculo.

Se o parâmetro Compare for definido como vbBinaryCompare, os caracteres ‘x’ minúsculos serão ignorados e sua planilha terá a seguinte aparência:

Se o parâmetro Compare for definido como vbTextCompare, os caracteres ‘x’ minúsculos serão usados ​​na divisão e sua planilha terá a seguinte aparência:

Observe que o valor na célula A6 está truncado porque contém um caractere ‘x’ minúsculo. Como a divisão não faz distinção entre maiúsculas e minúsculas, qualquer delimitador que faça parte de uma subcadeia de caracteres fará com que ocorra uma divisão.

Este é um ponto importante a se ter em mente ao usar um delimitador de texto e vbTextCompare. Você pode facilmente acabar com o resultado errado.

Usando caracteres não imprimíveis como caractere delimitador

Você pode usar caracteres não imprimíveis como delimitador, como um retorno de carro (uma quebra de linha).

Aqui usamos o vbCr para especificar um retorno de carro <>

123456789101112131415

Sub SplitByNonPrintableExample ()'Criar variáveisDim MyArray () As String, MyString As String, I As Variant, N As Integer'Amostra de string com delimitadores de retorno de carroMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, vbCr,, vbTextCompare)'Limpar a planilhaActiveSheet.UsedRange.Clear'Iterar através da matrizPara N = 0 para UBound (MyArray)'Coloque cada divisão na primeira coluna da planilhaIntervalo ("A" & N + 1) .Value = MyArray (N)Próximo NEnd Sub

Neste exemplo, uma string é construída usando vbCr (caractere de retorno de carro) como delimitador.

Quando este código for executado, sua planilha ficará assim:

Usando a função Join para reverter uma divisão

A função Join re-unirá todos os elementos de uma matriz, mas usando um delimitador especificado. Se nenhum caractere delimitador for especificado, um espaço será usado.

123456789101112131415

Sub JoinExample ()'Criar variáveisDim MyArray () As String, MyString As String, I As Variant, N As IntegerDim Target As String'String de amostra com delimitadores de vírgulaMyString = "Um, Dois, Três, Quatro, Cinco, Seis"'Coloque MyString na célula A1Intervalo ("A1"). Valor = MyString'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, ",")'Use a função Join para recriar a string original usando um delimitador de ponto e vírgulaTarget = Join (MyArray, ”;”)'Coloque a string de resultado na célula A2Intervalo ("A2"). Valor = AlvoEnd Sub

Este código divide uma string com delimitadores de vírgula em uma matriz e a reúne novamente usando delimitadores de ponto-e-vírgula.

Depois de executar este código, sua planilha ficará assim:

A célula A1 tem a string original com delimitadores de vírgula e a célula A2 tem a nova string unida com delimitadores de ponto e vírgula.

Usando a função Split para fazer uma contagem de palavras

Tendo em mente que uma variável de string no Excel VBA pode ter até 2 Gb de comprimento, você pode usar a função de divisão para fazer a contagem de palavras em um trecho de texto. Obviamente, o Microsoft Word faz isso automaticamente, mas isso pode ser útil para um arquivo de texto simples ou texto copiado de outro aplicativo.

1234567891011121314

Sub NumberOfWordsExample ()'Criar variáveisDim MyArray () As String, MyString As String'String de amostra com delimitadores de espaçoMyString = "Um Dois Três Quatro Cinco Seis"'Remova quaisquer espaços duplosMyString = Substituir (MyString, "", "")'Remova quaisquer espaços à esquerda ou à direitaMyString = Trim (MyString)'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString)'Mostra o número de palavras usando a função UBoundMsgBox "Número de palavras" & UBound (MyArray) + 1End Sub

Um dos perigos desse código de contagem de palavras é que ele será gerado por espaços duplos e espaços à esquerda e à direita. Se estiverem presentes, serão contadas como palavras extras e a contagem de palavras será imprecisa.

O código usa as funções Replace e Trim para remover esses espaços extras.

A linha de código final exibe o número de palavras encontradas usando a função UBound para obter o número máximo de elementos da matriz e, em seguida, incrementando-o em 1. Isso ocorre porque o primeiro elemento da matriz começa em zero.

Dividindo um endereço em células da planilha

Os endereços de correio costumam ser longas sequências de texto com delimitadores de vírgula. Você também pode dividir cada parte do endereço em uma célula separada.

123456789101112131415

Sub AddressExample ()'Criar variáveisDim MyArray () As String, MyString As String, N As Integer'Configurar string com o endereço da Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EUA"'Use a função de divisão para dividir a string usando um delimitador de vírgulaMyArray = Split (MyString, ",")'Limpar a planilhaActiveSheet.UsedRange.Clear'iterar através da matrizPara N = 0 para UBound (MyArray)'Coloque cada divisão na primeira coluna da planilhaIntervalo ("A" & N + 1) .Value = MyArray (N)Próximo NEnd Sub

A execução desse código usará o delimitador de vírgula para colocar cada linha do endereço em uma célula separada:

Se você quiser apenas retornar o código postal (último elemento da matriz), poderá usar o código:

123456789101112

Sub AddressZipCodeExample ()'Criar variáveisDim MyArray () As String, MyString As String, N As Integer, Temp As String'Configurar string com o endereço da Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EUA"'Use a função de divisão para dividir a string usando um delimitador de vírgulaMyArray = Split (MyString, ",")'Limpar a planilhaActiveSheet.UsedRange.Clear'Coloque o código postal na célula A1Intervalo ("A1"). Valor = MyArray (UBound (MyArray))End Sub

Isso usará apenas o último elemento na matriz, que é encontrado usando a função UBound.

Por outro lado, você pode desejar ver todas as linhas em uma célula para que possam ser impressas em uma etiqueta de endereço:

1234567891011121314151617

Sub AddressExample ()'Criar variáveisDim MyArray () As String, MyString As String, N As Integer, Temp As String'Configurar string com o endereço da Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EUA"'Use a função de divisão para dividir a string usando um delimitador de vírgulaMyArray = Split (MyString, ",")'Limpar a planilhaActiveSheet.UsedRange.Clear'iterar através da matrizPara N = 0 para UBound (MyArray)'coloque cada elemento da matriz mais um caractere de alimentação de linha em uma stringTemp = Temp & MyArray (N) & vbLfPróximo N'Coloque a corda na planilhaFaixa ("A1") = TempEnd Sub

Este exemplo funciona da mesma maneira que o anterior, exceto que cria uma string temporária de todos os elementos da matriz, mas inserindo um caractere de alimentação de linha após cada elemento.

A planilha ficará assim depois que o código for executado:

Dividir string em células de planilha

Você pode copiar a matriz Split em células da planilha <> com apenas um comando:

12345678910

Sub CopyToRange ()'Criar variáveisDim MyArray () As String, MyString As String'String de amostra com delimitadores de espaçoMyString = "Um, Dois, Três, Quatro, Cinco, Seis"'Use a função Split para dividir as partes componentes da cordaMyArray = Split (MyString, ",")'Copie a matriz para a planilhaRange ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub

Quando este código for executado, sua planilha ficará assim:

Criação de uma nova função para permitir a divisão de um determinado ponto

O parâmetro Limit na função Split permite apenas que você especifique um limite superior onde você deseja que a divisão pare. Sempre começa no início da string.

Seria muito útil ter uma função semelhante em que você pudesse especificar o ponto inicial da divisão dentro da string e o número de divisões que deseja ver desse ponto em diante. Ele também irá extrair apenas as divisões que você especificou no array, em vez de ter um enorme valor de string como o último elemento do array.

Você pode construir facilmente uma função (chamada SplitSlicer) no VBA para fazer isso:

123456789101112131415161718192021222324

Função SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Criar variável de matrizDim MyArray () As String'Capture a divisão usando a variável inicial usando o caractere delimitadorMyArray = Split (Target, Del, Start)‘Verifique se o parâmetro inicial é maior do que o número de divisões - isso pode causar problemasIf Start> UBound (MyArray) + 1 Then‘Exibir erro e sair da funçãoMsgBox "O parâmetro de início é maior que o número de divisões disponíveis"SplitSlicer = MyArrayFunção de saídaFim se'Coloque o último elemento do array na stringTarget = MyArray (UBound (MyArray))'Divida a string usando N como o limiteMyArray = Split (Target, Del, N)‘Verifique se o limite superior é maior que zero, pois o código remove o último elementoIf UBound (MyArray)> 0 Then'Use ReDim para remover o elemento final da matrizReDim Preserve MyArray (UBound (MyArray) - 1)Fim se'Retorna o novo arraySplitSlicer = MyArrayFunção Final

Esta função é construída com quatro parâmetros:

• Alvo - string - esta é a string de entrada que você deseja dividir
• Del - string ou caractere não imprimível - este é o caractere delimitador que você usa, por exemplo vírgula, dois pontos
• Começar - número - esta é a divisão inicial para sua fatia
• N - número - este é o número de divisões que você deseja fazer em sua fatia

Nenhum desses parâmetros é opcional ou tem valores padrão, mas você pode trabalhar isso no código da função se desejar estendê-la ainda mais.

A função usa a função Split para criar uma matriz usando o parâmetro Start como Limit. Isso significa que os elementos da matriz manterão as divisões até o parâmetro inicial, mas o restante da string será o último elemento e não será dividido.

O último elemento na matriz é transferido de volta para uma string usando a função UBound para determinar qual elemento é esse.

A string é então dividida novamente na matriz, usando N como a variável de limite. Isso significa que as divisões serão feitas para a string até a posição N, após o que o resto da string formará o último elemento do array.

A instrução ReDim é usada para remover o último elemento, pois queremos apenas os elementos específicos deixados na matriz. Observe que o parâmetro Preserve é usado, caso contrário, todos os dados na matriz serão perdidos.

A nova matriz é então retornada ao código de onde foi chamada.

Observe que o código é ‘à prova de erros’. Os usuários costumam fazer coisas estranhas que você não considerou. Por exemplo, se eles tentarem usar a função com o parâmetro Start ou N maior que o número disponível de divisões na string, é provável que a função falhe.

O código é incluído para verificar o valor inicial e também para garantir que haja um elemento que pode ser removido quando a instrução ReDim é usada na matriz.

Aqui está o código para testar a função:

123456789101112

Sub TestSplitSlicer ()'Criar variáveisDim MyArray () As String, MyString As String'Definir string de amostra com delimitadores de vírgulaMyString = "Um, Dois, Três, Quatro, Cinco, Seis, Sete, Oito, Nove, Dez"'Use a função Splitslicer para definir uma nova matrizMyArray = SplitSlicer (MyString, ",", 4, 3)'Limpar a folha ativaActiveSheet.UsedRange.Clear'Copie a matriz para a planilhaRange ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub

Execute este código e sua planilha ficará assim: