Ever got stuck in some random deductions from a data file. A corrupted data file or in case an encrypted one comprises unseen norms that deal with some unusual characters in data sets. For example, [email protected]@[email protected]@[email protected]@Away in this scenario we could find that @ has replaced almost all the space features/elements/characters. With further reading, I will investigate how we can use excel formulas to remove characters. These characters can be deliberate, coded signs or any other.
Sometimes we can also remove the characters from left and right depending on the accumulation of random stuffing. Character removal also involves a single character, or a set of characters aligned or not aligned. Isn’t it amazing to hear Excel with these user-customized functionalities?
Excel formula to remove Characters
=SUBSTITUTE ( Cell name, “remove_char”, ” “) is the most often used formula to remove characters from a string of data sets.
As mentioned, the brackets comprise of three sets:
1. The row name eg A1, A2, etc whose data need to be manipulated
2. Remove char: The character which needs to be removed. This can be a number, alphabet, sign, symbol, or space field.
3. The data inside “”: This usually mentions what u need to replace in place of the removed character. In case the user wants to only eliminate the character he/she can simply give “” without giving spaces in between them.
Substitute function thus helps remove the predefined and visible character from a data string and also allows us to replace another data or string of data in that place.
Some of the Examples of Substitute function are as follows:
1. =SUBSTITUTE(A10,” “,””) : Here we are replacing the data string at A10 position single spaces to no spaces.
2. =SUBSTITUTE(A22,CHAR(38),””): Here we are editing A22 row’s char at position 38 to no space i.e (Eliminate).
3. =SUBSTITUTE(A1,CHAR(CODE(LEFT(A2))),””): Using this we are shifting the cells and data string to the left , putting the empty string at the end. For example if A1 is _x_ y_ z_ it becomes xyz____.
Another Such formula that helps deducting the first character from a data string is = REPLACE(), however, as stated it is valid usually for the first elements in the string.
Character removing functionalities of Excel:
Excel comprises numerous functions for a single task in action. If one is thinking about removing a character from a data string, there are numerous functions manifesting the removal from different positions, indexes, styles and formatting. TRIM, REPLACE, SUBSTITUTE, LEN(Length), ALIGN, CUSTOM and many other scenarios are responsible for carrying out these tasks. However, they are limited to certain activities.
Removing certain numbers of characters from string in excel:
=RIGHT(A5,LEN(A5)-4): This removes 4 characters from the right side.
=LEFT(A2,LEN(A2)-4): This removes 4 characters from the left side.
Note: Spaces are considered in the calculating number of characters. If you want to redefine the number of removal you can simply change the value of -4 to any other. In this way, we can remove characters from the excel string using the excel formula.