Uso de la función de división de VBA
La función VBA Split le permite separar las partes del componente dentro de una cadena de texto estándar donde cada componente usa un carácter delimitador específico, p. una coma o dos puntos. Es más fácil de usar que escribir código para buscar los delimitadores en la cadena y luego extraer los valores.
Podría usarse si está leyendo en una línea de un archivo de valores separados por comas (archivo CSV) o si tiene una dirección de correo que está en una sola línea, pero desea verla como varias líneas.
La sintaxis es:
| 1 | Expresión dividida, delimitador [opcional], límite [opcional], comparar [opcional] |
La función VBA Split tiene cuatro parámetros:
- Expresión - La cadena de texto que desea dividir en diferentes partes.
- Delimitador (Opcional)- cadena o carácter no imprimible: define el carácter delimitador que se utilizará para la división. Si no se proporciona un carácter delimitador, se utiliza el espacio predeterminado.
- Límite (Opcional) - número: define cuántas divisiones se realizarán. Si está en blanco, todas las divisiones disponibles se realizarán dentro de la cadena. Si se establece en 1, no se realizarán divisiones. Básicamente, le permite separar un número específico de valores comenzando al principio de la cadena, p. Ej. donde la cadena es muy larga y solo necesitas las tres primeras divisiones.
- Comparar (Opcional) - Si su delimitador es un carácter de texto, esto se usa para alternar si el delimitador distingue entre mayúsculas y minúsculas o no. Los valores son vbBinaryCompare (distingue entre mayúsculas y minúsculas) y vbTextCompare (no distingue entre mayúsculas y minúsculas).
La función de división siempre devuelve una matriz.
Ejemplo simple de la función de división
| 123456789101112 | Sub SplitExample ()'Definir variablesDim MyArray () como cadena, MyString como cadena, I como variante'Cadena de muestra con delimitadores de espacioMyString = "Uno Dos Tres Cuatro"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString)'iterar a través de la matriz creada para mostrar cada valorPara cada yo en MyArrayMsgBox ISiguiente yoEnd Sub |
En este ejemplo, no se especifica ningún delimitador porque todas las palabras tienen un espacio entre ellas, por lo que se puede usar el delimitador predeterminado (espacio).
La matriz no tiene dimensiones y se establece como una cadena. La variable I, que se utiliza en el bucle For… Next, debe dimensionarse como variante.
Cuando se ejecuta este código, mostrará cuatro cuadros de mensaje, uno para cada una de las divisiones, p. Ej. Uno dos tres. Cuatro.
Tenga en cuenta que si hay un espacio doble entre las palabras de la cadena, esto se evaluará como una división, aunque sin nada. Puede que este no sea el resultado que desea ver.
Puede solucionar este problema utilizando la función Reemplazar para reemplazar los espacios dobles con un solo espacio:
| 1 | MyString = Reemplazar (MyString, "", "") |
Un espacio inicial o final también puede causar problemas al producir una división vacía. Suelen ser muy difíciles de ver. Puede eliminar estos espacios superfluos utilizando la función Recortar:
| 1 | MyString = Recortar (MyString) |
Uso de la función de división con un carácter delimitador
Podemos usar un delimitador de punto y coma (;). Esto se encuentra con frecuencia en cadenas de direcciones de correo electrónico para separar las direcciones. Es posible que le envíen un correo electrónico que se comparte con varios colegas y desea ver una lista en su hoja de trabajo de a quién se ha dirigido. Puede copiar fácilmente las direcciones de correo electrónico de los cuadros de correo electrónico "Para" o "Copiar" y en su código.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definir variablesDim MyArray () como cadena, MyString como cadena, I como variante, N como entero'Cadena de muestra con delimitadores de punto y comaMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, ";")'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'iterar a través de la matrizPara N = 0 a UBound (MyArray)'Coloque cada dirección de correo electrónico en la primera columna de la hoja de trabajoRango ("A" y N + 1) .Value = MyArray (N)Siguiente NEnd Sub |
Tenga en cuenta que se utiliza un bucle For… Next para recorrer la matriz en iteración. El primer elemento de la matriz siempre comienza en cero y la función Límite superior se usa para obtener el número máximo de elementos.
Después de ejecutar este código, su hoja de trabajo se verá así:
Usar un parámetro de límite en una función de división
El parámetro de límite permite realizar un número específico de divisiones desde el inicio de la cadena. Desafortunadamente, no puede proporcionar una posición de inicio o un rango de divisiones para realizar, por lo que es bastante básico. Puede crear su propio código VBA para crear una función para hacer esto, y esto se explicará más adelante en este artículo.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena, I como variante, N como entero'Cadena de muestra con delimitadores de comaMyString = "Uno, Dos, Tres, Cuatro, Cinco, Seis"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, ",", 4)'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'Iterar a través de la matrizPara N = 0 a UBound (MyArray)'Coloque cada división en la primera columna de la hoja de trabajoRango ("A" y N + 1) .Value = MyArray (N)Siguiente NEnd Sub |
Después de ejecutar este código, su hoja de trabajo se verá así:
Solo los tres primeros valores divididos se muestran por separado. Los últimos tres valores se muestran como una cadena larga y no se dividen.
Si elige un valor límite mayor que el número de delimitadores dentro de una cadena, esto no producirá un error. La cadena se dividirá en todos sus componentes como si no se hubiera proporcionado el valor límite.
Uso del parámetro de comparación en una función dividida
El parámetro Comparar determina si el delimitador distingue entre mayúsculas y minúsculas o no. Esto no es aplicable si los delimitadores son comas, punto y coma o dos puntos.
Nota: En su lugar, siempre puede colocar Option Compare Text <> en la parte superior de su módulo para eliminar la distinción entre mayúsculas y minúsculas para todo el módulo.
| 123456789101112131415 | Sub SplitByCompareExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena, I como variante, N como entero'Cadena de muestra con delimitadores XMyString = "OneXTwoXThreexFourXFivexSix"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, "X", vbBinaryCompare)'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'iterar a través de la matrizPara N = 0 a UBound (MyArray)'Coloque cada división en la primera columna de la hoja de trabajoRango ("A" y N + 1) .Value = MyArray (N)Siguiente NEnd Sub |
En este ejemplo, la cadena que se va a dividir utiliza el carácter "X" como delimitador. Sin embargo, en esta cadena, hay una mezcla de caracteres "X" en mayúsculas y minúsculas. El parámetro Comparar en la función Dividir utiliza un carácter "X" en mayúsculas.
Si el parámetro Comparar se establece en vbBinaryCompare, los caracteres "x" minúsculas se ignorarán y su hoja de trabajo se verá así:
Si el parámetro Comparar se establece en vbTextCompare, entonces los caracteres "x" minúsculas se usarán en la división y su hoja de trabajo se verá así:
Tenga en cuenta que el valor de la celda A6 está truncado porque contiene un carácter "x" en minúscula. Debido a que la división no distingue entre mayúsculas y minúsculas, cualquier delimitador que forme parte de una subcadena provocará una división.
Este es un punto importante a tener en cuenta al utilizar un delimitador de texto y vbTextCompare. Puede terminar fácilmente con un resultado incorrecto.
Uso de caracteres no imprimibles como carácter delimitador
Puede utilizar caracteres no imprimibles como delimitador, como un retorno de carro (un salto de línea).
Aquí usamos vbCr para especificar un retorno de carro <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena, I como variante, N como entero'Cadena de muestra con delimitadores de retorno de carroMyString = "Uno" & vbCr & "Dos" & vbCr & "Tres" & vbCr & "Cuatro" & vbCr & "Cinco" & vbCr & "Seis"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, vbCr, vbTextCompare)'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'Iterar a través de la matrizPara N = 0 a UBound (MyArray)'Coloque cada división en la primera columna de la hoja de trabajoRango ("A" y N + 1) .Value = MyArray (N)Siguiente NEnd Sub |
En este ejemplo, se crea una cadena utilizando vbCr (carácter de retorno de carro) como delimitador.
Cuando se ejecuta este código, su hoja de trabajo se verá así:
Uso de la función de unión para revertir una división
La función Unir volverá a unir todos los elementos de una matriz, pero utilizando un delimitador especificado. Si no se especifica ningún carácter delimitador, se utilizará un espacio.
| 123456789101112131415 | Sub JoinExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena, I como variante, N como enteroAtenuar destino como cadena'Cadena de muestra con delimitadores de comaMyString = "Uno, Dos, Tres, Cuatro, Cinco, Seis"'Coloque MyString en la celda A1Rango ("A1"). Valor = MyString'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, ",")'Use la función Unir para recrear la cadena original usando un delimitador de punto y comaTarget = Join (MyArray, ";")'Coloque la cadena de resultado en la celda A2Rango ("A2"). Valor = ObjetivoEnd Sub |
Este código divide una cadena con delimitadores de coma en una matriz y la vuelve a unir mediante delimitadores de punto y coma.
Después de ejecutar este código, su hoja de trabajo se verá así:
La celda A1 tiene la cadena original con delimitadores de coma y la celda A2 tiene la nueva cadena unida con delimitadores de punto y coma.
Uso de la función de división para hacer un recuento de palabras
Teniendo en cuenta que una variable de cadena en Excel VBA puede tener hasta 2 Gb de longitud, puede usar la función de división para contar palabras en un fragmento de texto. Obviamente, Microsoft Word lo hace automáticamente, pero esto podría ser útil para un archivo de texto simple o texto copiado de otra aplicación.
| 1234567891011121314 | Sub NumberOfWordsExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena'Cadena de muestra con delimitadores de espacioMyString = "Uno Dos Tres Cuatro Cinco Seis"'Quite los espacios doblesMyString = Reemplazar (MyString, "", "")'Elimine los espacios iniciales o finalesMyString = Recortar (MyString)'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString)'Muestra el número de palabras usando la función UBoundMsgBox "Número de palabras" y UBound (MyArray) + 1End Sub |
Uno de los peligros de este código de recuento de palabras es que será arrojado por espacios dobles y espacios iniciales y finales. Si están presentes, se contarán como palabras adicionales y el recuento de palabras terminará siendo inexacto.
El código usa las funciones Reemplazar y Recortar para eliminar estos espacios adicionales.
La línea de código final muestra el número de palabras encontradas usando la función UBound para obtener el número máximo de elementos de la matriz y luego incrementándolo en 1. Esto se debe a que el primer elemento de la matriz comienza en cero.
División de una dirección en celdas de hoja de trabajo
Las direcciones de correo suelen ser largas cadenas de texto con delimitadores de coma. Es posible que desee dividir cada parte de la dirección en una celda separada.
| 123456789101112131415 | SubdirecciónEjemplo ()'Crear variablesDim MyArray () como cadena, MyString como cadena, N como entero'Configurar cadena con la dirección de Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EE. UU."'Use la función de división para dividir la cadena usando un delimitador de comaMyArray = Dividir (MyString, ",")'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'iterar a través de la matrizPara N = 0 a UBound (MyArray)'Coloque cada división en la primera columna de la hoja de trabajoRango ("A" y N + 1) .Value = MyArray (N)Siguiente NEnd Sub |
Al ejecutar este código, se usará el delimitador de coma para colocar cada línea de la dirección en una celda separada:
Si solo desea devolver el código postal (último elemento de la matriz), puede usar el código:
| 123456789101112 | SubdirecciónZipCodeExample ()'Crear variablesDim MyArray () como cadena, MyString como cadena, N como entero, temperatura como cadena'Configurar cadena con la dirección de Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EE. UU."'Use la función de división para dividir la cadena usando un delimitador de comaMyArray = Dividir (MyString, ",")'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'Pon el código postal en la celda A1Rango ("A1"). Valor = MyArray (UBound (MyArray))End Sub |
Esto solo usará el último elemento de la matriz, que se encuentra usando la función UBound.
Por otro lado, es posible que desee ver todas las líneas en una celda para que puedan imprimirse en una etiqueta de dirección:
| 1234567891011121314151617 | SubdirecciónEjemplo ()'Crear variablesDim MyArray () como cadena, MyString como cadena, N como entero, temperatura como cadena'Configurar cadena con la dirección de Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 EE. UU."'Use la función de división para dividir la cadena usando un delimitador de comaMyArray = Dividir (MyString, ",")'Limpiar la hoja de trabajoActiveSheet.UsedRange.Clear'iterar a través de la matrizPara N = 0 a UBound (MyArray)'coloque cada elemento de la matriz más un carácter de avance de línea en una cadenaTemp = Temp y MyArray (N) y vbLfSiguiente N'Pon la cuerda en la hoja de trabajoRango ("A1") = temperaturaEnd Sub |
Este ejemplo funciona de la misma manera que el anterior, excepto que crea una cadena temporal de todos los elementos de la matriz, pero inserta un carácter de avance de línea después de cada elemento.
La hoja de trabajo se verá así después de que se haya ejecutado el código:
Dividir cadena en celdas de hoja de trabajo
Puede copiar la matriz dividida en celdas de la hoja de trabajo <> con un solo comando:
| 12345678910 | Sub CopyToRange ()'Crear variablesDim MyArray () como cadena, MyString como cadena'Cadena de muestra con delimitadores de espacioMyString = "Uno, Dos, Tres, Cuatro, Cinco, Seis"'Use la función Split para dividir las partes componentes de la cadenaMyArray = Dividir (MyString, ",")'Copie la matriz en la hoja de trabajoRango ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub |
Cuando se haya ejecutado este código, su hoja de trabajo se verá así:
Creación de una nueva función para permitir la división desde un punto dado
El parámetro Límite de la función División solo le permite especificar un límite superior donde desea que se detenga la división. Siempre comienza desde el inicio de la cadena.
Sería muy útil tener una función similar en la que pueda especificar el punto de inicio de la división dentro de la cadena y el número de divisiones que desea ver a partir de ese punto. También solo extraerá las divisiones que haya especificado en la matriz, en lugar de tener un valor de cadena enorme como último elemento de la matriz.
Puede crear fácilmente una función (llamada SplitSlicer) usted mismo en VBA para hacer esto:
| 123456789101112131415161718192021222324 | Función SplitSlicer (Target As String, Del As String, Start As Integer, N As Integer)'Crear variable de matrizDim MyArray () como cadena'Capture la división usando la variable de inicio usando el carácter delimitadorMyArray = Dividir (Destino, Del, Inicio)"Compruebe si el parámetro de inicio es mayor que el número de divisiones; esto puede causar problemasSi Inicio> UBound (MyArray) + 1 Entonces‘Mostrar error y salir de la funciónMsgBox "El parámetro de inicio es mayor que el número de divisiones disponibles"SplitSlicer = MyArrayFunción de salidaTerminara si'Pon el último elemento de la matriz en la cadenaDestino = MyArray (UBound (MyArray))'Divida la cadena usando N como límiteMyArray = Dividir (Destino, Del, N)"Compruebe que el límite superior sea mayor que cero, ya que el código elimina el último elemento.Si UBound (MyArray)> 0, entonces'Use ReDim para eliminar el elemento final de la matrizReDim Preserve MyArray (UBound (MyArray) - 1)Terminara si'Devuelve la nueva matrizSplitSlicer = MyArrayFunción final |
Esta función está construida con cuatro parámetros:
- Objetivo - cadena: esta es la cadena de entrada que desea dividir
- Del - cadena o carácter no imprimible: este es el carácter delimitador que utiliza, p. ej. coma, dos puntos
- Comienzo - número: esta es la división inicial de su porción
- norte - número: este es el número de divisiones que desea hacer dentro de su porción
Ninguno de estos parámetros es opcional o tiene valores predeterminados, pero puede incluirlo en el código de la función si desea ampliarlo más.
La función usa la función Dividir para crear una matriz usando el parámetro Inicio como Límite. Esto significa que los elementos de la matriz mantendrán las divisiones hasta el parámetro de inicio, pero el resto de la cadena será el último elemento y no se dividirá.
El último elemento de la matriz se transfiere de nuevo a una cadena utilizando la función UBound para determinar qué elemento es.
A continuación, la cadena se vuelve a dividir en la matriz, utilizando N como variable límite. Esto significa que se realizarán divisiones para la cadena hasta la posición N, después de lo cual el resto de la cadena formará el último elemento de la matriz.
La declaración ReDim se usa para eliminar el último elemento, ya que solo queremos que los elementos específicos queden en la matriz. Tenga en cuenta que se utiliza el parámetro Preserve; de lo contrario, se perderán todos los datos de la matriz.
Luego, la nueva matriz se devuelve al código desde el que se llamó.
Tenga en cuenta que el código está "a prueba de errores". Los usuarios a menudo harán cosas extrañas que usted no consideró. Por ejemplo, si intentan usar la función con el parámetro Start o N mayor que el número disponible de divisiones en la cadena, es probable que la función falle.
Se incluye código para verificar el valor de Inicio y también para asegurarse de que hay un elemento que se puede eliminar cuando se usa la instrucción ReDim en la matriz.
Aquí está el código para probar la función:
| 123456789101112 | Sub TestSplitSlicer ()'Crear variablesDim MyArray () como cadena, MyString como cadena'Definir cadena de muestra con delimitadores de comaMyString = "Uno, Dos, Tres, Cuatro, Cinco, Seis, Siete, Ocho, Nueve, Diez"'Use la función Splitslicer para definir una nueva matrizMyArray = SplitSlicer (MyString, ",", 4, 3)'Limpiar la hoja activaActiveSheet.UsedRange.Clear'Copie la matriz en la hoja de trabajoRango ("A1: A" & UBound (MyArray) + 1) .Value = WorksheetFunction.Transpose (MyArray)End Sub |
Ejecute este código y su hoja de trabajo se verá así:
