Good to all and to all.
I am looking for ways to be able to remove special characters from multiple cells in excel.
For example, from A2:A50 to replace the special characters in the selected cells.
Example:
H&*O^&@LA --> HELLO
The function should not initially be executed through a macro.
How could I then replace the special characters?
I have the idea to do it with a macro, for example using a loop with a string and doing a replacement in each cell but I'm not sure how I could do it in the sheet without a macro.
I have tried with : =REPLACE("XYZ123",4,3,"456"), but replace asks for the positions to be replaced. In this case I would not know which positions to use.
PS: Using an extension is not a solution.
Thanks.
What the function
Replace
does if I remember correctly is to replace in X position, but what you are looking for isSUSTITUIR
. I explain:From this example that you have put:
The correct function would be this (B2 is my cell in my excel):
But, how to extend this example to any character?
A Substitute for each character : You have to create a function for each character you want to filter in this case there were 4. When you get to the last Substitute, you create the original function, that is, the one that searches for the cell
SUSTITUIR(B2;"&";"");
.Once the main function has been created, it is not necessary to repeat the call to the cell since, as it is nested, all the other functions know which cell you are in and you only need to add the following filters
"*";"")
; ,"^";"");
and"@";"")
. Obviously, if you are going to try to change more characters, a long task awaits you, you can create a list and take values from it until you complete your filter, that is up to you.I'm afraid you need VBA to solve it in an easy way. Not a macro as such, but you can create a UDF that allows you to easily clean up characters you don't care about.
I have designed a very simple UDF, fully customizable:
What this UDF does is look at the ASCII code of each character. Through a Select, we can choose which ASCII characters we allow and which we do not. Right now this UDF only supports numbers, letters without accents, and the letter ñ. Imagine that you want to clean the text that you have in A1, because in B1 we would write
=LIMPIAR_TEXTO(A1)
Several examples:
You only have to modify the part that says
Case 48 To 57, 65 To 90, 97 To 122, 164 To 165, 32
with the ASCII codes of the characters that you do want to allow. You can add that it allows line breaks, commas, parentheses, letters with accents, etc.Adapt it to your needs.