Limpiar caracteres no numéricos en Excel

Los datos son el nuevo petróleo, saber manejarlos es una de las mayores fuentes riquezas de hoy en día. Y tal como sucede con el petróleo, es imposible convertirlo en combustible si primero no los refinamos. Llamamos limpieza de datos (data cleansing, data cleaning o incluso data scrubbing) al proceso que empleamos para descubrir, corregir o eliminar datos erróneos, corruptos o imprecisos con el objetivo de refinar la calidad de la información de un registro, una tabla o una base de datos. Justamente la limpieza es uno de los primeros y más importantes pasos de cualquier procesamiento de datos, apuntando a verificar que los valores contenidos en cada campo sean correctos, conforme a un conjunto de reglas predefinido. Por ejemplo. un campo numérico debe contener solo números. En este artículo aprenderás a emplear un conjunto de técnicas que te permitirán limpiar caracteres no numéricos en Excel.

¿Qué aplicaciones puedes darle a esta técnica? A veces te encontrarás con fuentes de datos que están corruptas por malas definiciones del diccionario de datos, errores de entrada de los usuarios o problemas en la transmisión o el almacenaje. Siguiendo con el ejemplo anterior, te encuentras con campos numéricos en los que hay caracteres alfabéticos, o caracteres especiales. Esto significa que no podrás darle utilidad a la información que contienen estos campos hasta limpiar todos los caracteres no numéricos.

Hagámoslo más simple y práctico: Imagina que tienes una hoja con datos de clientes y en una columna están sus números de teléfono. Nunca se estandarizó la carga de este dato numérico por lo que los usuarios han llenado registros con errores de todo tipo. Por ejemplo, te encuentras con registros al estilo “54-911-3408-8820”, “(549)11-34088820”, “+54911-34088820 (móvil)” o peor aún “(549)11-34088820asa$%%_+”.

Te enseñaré tres métodos para limpiar caracteres no numéricos en Excel, cada uno empleando enfoques de trabajo diferente, de forma tal que tu definas cuál emplear de acuerdo a la naturaleza del problema a resolver.

Limpiar caracteres no numéricos en Excel


Para comenzar la práctica puedes descargar aquí un libro Excel donde se aplican los tres métodos descritos en el artículo: Descargar libro de práctica

Método 1: Limpiar caracteres no numéricos desarrollando una UDF (función definida por usuario).

Emplear una función definida por usuario, o UDF, puede ser una excelente solución si dispones de un conjunto pequeño de datos a corregir o si necesitas emplear el resultado de la limpieza posteriormente como argumento de otra función o fórmula. Para hacerlo sigue estos simples pasos:

  1. Teniendo abierto el libro donde se encuentran los datos a limpiar presiona Alt + F11, lo que te llevará al editor de VBA (Visual Basic para Aplicaciones).
  2. En el menú “Insertar” selecciona “Módulo“.
  3. En la ventana resultante copia y pega el siguiente código:
Function SOLONUMEROS(Rng As Range)
 'por Microsoft Excel MVP Daniel Cedeño Urbina - EXCELLENTIAS.COM
 Dim strTemp As String
 Dim n As Long
 For n = 1 To Len(Rng)
 Select Case Asc(Mid((Rng), n, 1))
 Case 48 To 57
 strTemp = strTemp & Mid(UCase(Rng), n, 1)
 End Select
 Next
 SOLONUMEROS = strTemp
 End Function

Ahora tan solo regresa al libro Excel y emplea la función SOLONUMEROS que acabas de crear, empleando como argumento único el campo que deseas limpiar, tal como en la imagen anexa.

FUNCION UDF SOLONUMEROS

Es fácil adaptar esta UDF para otros fines de  limpieza. Por ejemplo, si quieres limpiar los caracteres especiales y mantener los alfanuméricos tan solo debes editar la sección del código que hace referencia a los valores ASCII de los caracteres numéricos (“Case 48 to 57“).

CaracteresValores ASCII
Nulo0
Espacio32
A – Z65 – 90
a – z97 – 122
0 – 948 – 57
caracteres especiales0 – 47, 58 – 64, 91 – 96, 123 – 127

De esta forma el código para limpiar caracteres especiales y mantener alfanuméricos sería:

Function SOLONUMEROSYLETRAS(Rng As Range)
 'por Microsoft Excel MVP Daniel Cedeño Urbina - EXCELLENTIAS.COM
 Dim strTemp As String
 Dim n As Long
 For n = 1 To Len(Rng)
 Select Case Asc(Mid((Rng), n, 1))
 Case 48 To 57, 65 To 90
 strTemp = strTemp & Mid(UCase(Rng), n, 1)
 End Select
 Next
 SOLONUMEROSYLETRAS = strTemp
 End Function

FUNCION UDF SOLONUMEROS - EJEMPLO 2

Método 2: Limpiar caracteres no numéricos empleando una fórmula.

La promesa de hacer de la limpieza de caracteres no numéricos una tarea simple se rompe en el punto en que intentamos hacerlo a través de una fórmula, usando tan solo funciones predefinidas de Excel. Existen diferentes enfoques para abordar y solucionar este problema desde fórmulas, pero hay uno en particular que me encanta emplear. Se trata de una de esas fórmulas que funciona como magia, un secreto que cuando pones en acción deja a todos boquiabiertos, intentando entender cómo llegaste a esta solución (debo reconocer que quizás me guste mucho porque soy de esos tipos raros que busca solucionar todo en Excel a través de fórmulas 😀 ). Aquí va la fórmula a emplear:

=SUMA(EXTRAE(0&B2;K.ESIMO.MAYOR(ESNUMERO(--EXTRAE(B2;FILA(INDIRECTO("1:"&LARGO(B2)));1))*FILA(INDIRECTO("1:"&LARGO(B2)));FILA(INDIRECTO("1:"&LARGO(B2))))+1;1)*10^FILA(INDIRECTO("1:"&LARGO(B2)))/10)

Toma en cuenta que en esta fórmula B2 contiene los datos que vas a limpiar de caracteres no numéricos, y la fórmula es matricial, por lo que la introduces presionando CONTROL+SHIFT+ENTER. Podrás reconocer que la fórmula es matricial porque quedará cargada entre corchetes “{}”, tal como en la imagen anexa.

LIMPIAR CARACTERES NO NUMERICOS EN EXCEL

Método 3: Limpiar caracteres no numéricos empleando VBA.

Otro enfoque que puedes emplear para resolver este problema es aprovechar VBA para hacer una limpieza masiva de caracteres no numéricos en un rango que definas. Es una solución eficiente y rápida que puedes implementar para limpiar rangos más grandes de información, o incluir como parte de una rutina de VBA más completa. Los pasos a seguir son:

  1. Teniendo abierto el libro donde se encuentran los datos a limpiar presiona Alt + F11, lo que te llevará al editor de VBA (Visual Basic para Aplicaciones).
  2. En el menú “Insertar” selecciona “Módulo“.
  3. En la ventana resultante copia y pega el siguiente código:
Sub LIMPIARCARACTERES()
 'por Microsoft Excel MVP Daniel Cedeño Urbina - EXCELLENTIAS.COM
 Dim Rng As Range
 Dim WorkRng As Range
 On Error Resume Next
 xTitleId = "LIMPIADOR DE CARACTERES NO NUMERICOS"
 Set WorkRng = Application.Selection
 Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
 For Each Rng In WorkRng
 xOut = ""
 For i = 1 To Len(Rng.Value)
 xTemp = Mid(Rng.Value, i, 1)
 If xTemp Like "[0-9]" Then
 xStr = xTemp
 Else
 xStr = ""
 End If
 xOut = xOut & xStr
 Next i
 Rng.Value = xOut
 Next
 End Sub

Ahora tan solo debes regresar al libro Excel donde se encuentran los datos a limpiar y ejecutar la macro LIMPIARCARACTERES, te pedirá que selecciones el rango de datos a limpiar y al aceptar procederá a borrar todos los caracteres no numéricos del rango definido.


Si conoces de otra técnica para limpiar caracteres no numéricos en Excel no dudes de compartirla en los comentarios, y si te resultó útil este artículo compártelo en las redes sociales: me ayudas a llegar a cada vez más usuarios de Excel y ayudas a otros a solucionar algún problema técnico, o simplemente conocer una nueva técnica que les será útil en su día a día.

Read the latest car news and check out newest photos, articles, and more from the Car and Driver Blog.

Una respuesta

  1. Pedro 13/06/2016

Deja un comentario:

¿Te vas sin unirte a nuestro Boletín Semanal?

Recibe gratis en tu correo cada semana nuevos recursos para hacer cosas asombrosas en Excel

Odiamos el SPAM tanto como tú, tus datos están seguros con nosotros.