Quickly change case, add text, remove characters and spaces in Excel 2007/2010

Text Tools has embodied a lot of handy operations for you to manipulate the text in a cell and range cells. It’s a quite handy and powerful Excel tool. You can fairly easily process the following operations in your worksheets with this Text tools.

Quickly change text case to UPPER, LOWER, PROPER, Sentence Case. and tOGGLE cASE. Demo
Quickly add or insert new characters to the text. Demo
Easily remove characters of the text from a specified position. Demo
Easily remove spaces from the text. Demo
Quickly remove characters of a particular type from the text. Demo

Click "Kutools">>"Text Tools". See screenshot (Fig.1 and Fig.2).


Fig. 1 Text Tools		Fig.2 Text Tools Dialog Box

Notes:

All of the above operations can be applied to a cell or a range cells.
After you click OK, you can choose Undo (or press Ctrl+Z) to reverse the change, and the undo is only one level.

Operations 1: Change the case of the text.

You can quickly change text case to UPPER, LOWER, PROPER, Sentence Case. and tOGGLE cASE with this “Change case” operation in the Text Tools, and you don’t need to handle these operations by learning how to use the Excel UPPER, LOWER and PROPER formulas.

Select “Change case” operation in the Text Tools dialog box.
Select the option from the “Change to” drop down box.
Select the cells you want to change case in the text.
Click OK to change the text case of the selected cells. See demo (Video 1).

Video 1.Changing case in the text feature demo.

Operations 2: Add new characters to the text.

You can quickly add or insert new characters to the text (at the beginning, at the end, or at a specified position) with this “Add text” operation in the Text Tools.

Select “Add text” operation in the Text Tools dialog box.
Enter the text in the “Text to add” box.
Select the specified position from the “Character position” to add text.
Select the cells you want to add text.
Click OK to add text to the selected cells. See demo (Video 2).

Note: "Skip non-text cells" option is not checked, it will add text to all text cells and the value cells. To add text only to the text cells, please select this option.

Video 2. Adding text feature demo.

Operations 3: Remove characters from the text.

You can easily remove characters of the text from a specified position (from the beginning, from the end, or beginning at a specified position) with this “Remove by position” operation in the Text Tools.

Select “Remove by position” operation in the Text Tools dialog box.
Select the characters you want to delete from the “Characters to remove” drop down box.
Choose the number of characters to be deleted from the "Characters to remove" drop-down box.
Select the cells you want to remove characters.
Click OK to remove characters from the selected cells. See demo (Video 3).

Note: “Skip non-text cells” option is not checked, it will remove characters from all text cells and the value cells. To remove characters only to the text cells, please select this option.

Video 3. Removing text by position feature demo.

Operations 4: Remove spaces from the text.

You can easily remove spaces from the text (all extra spaces, leading spaces, trailing spaces, or all spaces) with this “Remove spaces” operation in the Text Tools.

Select “Remove spaces” operation in the Text Tools dialog box.
Select the option from the “Remove” drop down box. (Such as: all extra spaces, leading spaces, trailing spaces, or all spaces)
Select the cells you want to remove spaces.
Click OK to remove spaces from the selected cells. See demo (Video 4).

Video 4. Removing spaces from a range cells feature demo.

Operations 5: Remove characters of a particular type from the text.

You can quickly remove the following particular types of characters from the text with this “Delete characters” operation in the Text Tools. Remove the non-printing characters, remove the alpha characters, remove non-numeric characters, remove non-numeric and non-alpha characters, and remove numeric characters.

Select “Delete characters” operation in the Text Tools dialog box.
Select the option from the “Delete all” drop down box.
Select the cells you want to delete particular types of characters.
Click OK to delete particular types of characters from the selected cells. See demo (Video 5).

Note: The remove “All excess spaces” option will convert multiple spaces to a single space, and removes all spaces from the beginning and the end of the text.

Video 5. Deleting particular types of characters from the text feature demo.