Scribd
Upload
95 views2 pages

Excel: Limpiar y Reemplazar Texto

The document contains code snippets for several text cleaning and manipulation functions in VBA: 1) A Sub routine called EliminarEspacios that trims whitespace from cells in a defined range. 2) A function called LIMPIAR_TEXTO that removes non-alphanumeric characters from a text value based on ASCII codes. 3) A function called SubstituteMultipleCS that performs multiple find-and-replace operations on a text string in a case-sensitive manner. 4) A Sub routine called reemplazar that uses the native Replace method to find and replace text in cells.

Uploaded by

Profesor Sofista
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
95 views2 pages

Excel: Limpiar y Reemplazar Texto

The document contains code snippets for several text cleaning and manipulation functions in VBA: 1) A Sub routine called EliminarEspacios that trims whitespace from cells in a defined range. 2) A function called LIMPIAR_TEXTO that removes non-alphanumeric characters from a text value based on ASCII codes. 3) A function called SubstituteMultipleCS that performs multiple find-and-replace operations on a text string in a case-sensitive manner. 4) A Sub routine called reemplazar that uses the native Replace method to find and replace text in cells.

Uploaded by

Profesor Sofista
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

'Eliminar espacios

Sub EliminarEspacios()
Dim cell As Range, AreaToTrim As Range
Set AreaToTrim = Worksheets("Hoja1").Range("B3:B18")
For Each cell In AreaToTrim
cell = Trim(cell)
Next cell
End Sub

'Limpiar texto de caracteres especiales


Public Function LIMPIAR_TEXTO(ByVal vThisRange As Range)
Dim MiTexto As String
Dim i As Long
MiTexto = [Link]

'<<<<<LISTA ASCII PERMITIDA >>>>


'48 TO 57 SSon números del 0 al 9
'65 to 90 son letras del alfabeto anglosajón, en mayúsculas, A-Z
'97 to 122 son letras del alfabeto anflosajón, en minúsculas, a-z
'164 to 165 la letra Ñ en mayúscula y minúscula
'32 es el espacio
'<<<<<<<<<<<<<>>>>>>>>>>>>>>>>

For i = 1 To Len(MiTexto)
Select Case Asc(Mid(MiTexto, i, 1)) 'Sacar código Ascii de cada carácter
Case 48 To 57, 65 To 90, 97 To 122, 164 To 165, 32
LIMPIAR_TEXTO = LIMPIAR_TEXTO & Mid(MiTexto, i, 1)
Case Else
'no hacemos nada, que lo ignore
End Select
Next i

End Function
'Reemplazar sensible a Mayusculas y Minusculas
Function SubstituteMultipleCS(text As String, old_text As Range, new_text As Range)
Dim i As Single
For i = 1 To old_text.[Link]
Result = Replace(text, old_text.Cells(i), new_text.Cells(i))
text = Result
Next i
SubstituteMultipleCS = Result
End Function

'Buscar y Reemplazar
Sub reemplazar()
[Link] What:="Dato a buscar", Replacement:="Valor a reemplazar"
End Sub

You might also like