Text Tools

The Text Tools utility provides several ways to manipulate text in selected cells without using formulas. You can:

  • Change the case of the text (UPPER CASE, lower case, Proper Case, Sentence case., and tOGGLE cASE).
  • Add new characters to the text (at the beginning, at the end, or at a specified position).
  • Remove characters from the text by position (from the beginning, from the end, or beginning at a specified position).
  • Remove spaces from the text (all extra spaces, or all spaces).
  • Remove characters of a particular type from the text (non-printing characters, alpha characters, non-numeric characters, non-numeric and non-alpha characters, or numeric characters).

Note: This utility will not preserve individual character formatting in your cells (e.g., superscript formatting). Formatting applied to entire cells is not affected.

More

  • Using Text Tools
  • Tips


Text Tools Tips

  • The Text Tools dialog box remains open when you click Apply, so you can make other modifications to the selected cells. Or, you can select another range of cells to work with.
  • After you click Apply, you can choose Undo (or press Ctrl+Z) to reverse the effects. Note that only one level of undo is available.
  • Text Tools works only with non-empty cells. Therefore, you can select entire rows or columns. Note, however, that you cannot use Text Tools to add text to empty cells.
  • If Skip non-text cells is checked, the utility works only on cells in the selection that contain text (as opposed to values). If left unchecked, the utility works with cells that contain either text or values. Blank cells and cells that contain formulas are always skipped.
  • When removing spaces from text, the All excess spaces option converts multiple spaces to a single space, and removes all spaces from the beginning and end of the text.
  • The Text Tools utility is appropriate only for text in worksheet cells. For example, you cannot use the Text Tools utility to make a chart label upper case.
  • The Text Tools utility works only with text entered directly into cells, and formulas are ignored. For example, the Text Tools utility will have no effect on a cell that has a formula such as =UPPER(A1), even though that formula displays text in its cell.


Using Text Tools

To display the Text Tools dialog box, choose Text Tools from Utilities group on the Home tab.

The Text Tools utility always works with the current range selection. The Text Tools dialog box is a "stay on top" dialog box. This means that you can easily select different cells to work with, and continue working in Excel while the dialog box remains on screen. The cell selection can consist of a discrete range, entire rows or columns, a multiple selection, or even the entire worksheet.

When you are finished using Text Tools, click Close.

Changing the case of text

  1. In the Text Tools dialog box, select the Change case operation.
  2. Select the appropriate Change to option to convert the selected text.
  3. Click Apply to modify the selected cells.

Adding text

  1. Select the cells that you want to modify.
  2. In the Text Tools dialog box, select the Add text operation.
  3. Enter the text you want to add in the Text to add box.
  4. In the Character position drop-down, select the option that corresponds to the position where you want the text to be added.
  5. If you want to limit the operations to selected cell that contain text, select Skip non-text cells. If this option is not checked, cells that contain values will also be modified.
  6. Click Apply to modify the selected cells.

Removing text by position

  1. Select the cells that you want to modify.
  2. In the Text Tools dialog box, select the Remove by position option.
  3. In the Characters to remove drop-down, choose the number of characters to be deleted.
  4. In the Character position drop-down, choose the position from which you want the characters removed.
  5. If you want to limit the operation to selected cells that contain text, select Skip non-text cells. If this option is not checked, cells that contain values will also be modified.
  6. Click Apply to modify the selected cells.

Removing spaces

  1. Select the cells that you want to modify.
  2. In the Text Tools dialog box, select the Remove spaces option.
  3. In the Remove drop-down, select the option that corresponds to the spaces that you want to remove.
  4. Click Apply to modify the selected cells.

Note: Selecting All Excess Spaces is equivalent to applying Excel's TRIM function to the cells.

Deleting characters by type

  1. Select the cells that you want to modify
  2. In the Text Tools dialog box, select the Delete characters option.
  3. In the Remove all drop-down, select the option that corresponds to the type of characters that you want to remove.
  4. Click Apply to modify the selected cells.

Note: Selecting Non-printing characters is equivalent to applying Excel's CLEAN function to the cells.

More

  • Tips