How do I delete an invisible space or symbol in an Excel table?

How do I delete an invisible space or symbol in an Excel table?

Method One: The processing ability of various processing methods to the space and invisible symbol comparison

1, comparison as shown

Method Two: The Len function is used to determine whether the invisible spaces and symbols are deleted.

1, using the Len function to calculate the number of cell characters

Because it is invisible space or symbol, we can not use the naked eye to determine whether the deletion is successful, then we need to use the Len function to assist the judgment. For example, if you enter "=len (A2)" In a blank cell B2, you can calculate the number of characters in the A2 cell. The Len function is used to calculate the number of cells before and after processing to determine whether the deletion succeeded.

Method Three: Use the TRIM function to remove spaces

1. The grammatical form and example of the trim function

=trim (text) is used to remove spaces before and after a string, but preserves one for connection purposes in the middle of a string. Example illustration

Method Four: Use the substitution method to remove the space

1, select the G column to replace, click "Edit" in the menu bar of the Excel software, click "Replace" in the Pull-down menu, click "Options" in the pop-up window. In the Find what box, enter a space and do not enter it in the replacement. Click "Options" to set up in more detail.

2, the Search box "by column", distinguish case, cell matching, distinguish the full/half angle of the front do not choose, and finally click on all the replacement, you can replace the entire column with no space. You need to know what to look for with the find substitution method, but you can only replace spaces, but not other invisible symbols, because they are invisible spaces or symbols.

Method Five: Use the clean function to remove symbols

1. The syntax format and example of clean function

=clean (text) to remove any worksheet information for nonprinting characters from text. The clean function is designed to remove the first 32 nonprinting characters (values 0 through 31) of 7-bit ASCII code in the text. The illustration is shown in the example.

Method VI: Use the substitute function to remove spaces and symbols

1, substitute function syntax and examples

=substitute (Text,old_text,new_text,[instance_num])

=substitute (need to replace the text, old text, new text, nth text)

The parameter instance_num--is a numeric value that specifies the New_text (new text) to replace the old_text (old text) that occurred several times.

Parameter instance_num can be omitted, which means replacing all old_text (old text) with new_text (new text).

Example illustration