Separating Text in Excel (Considerations)

Here are some things to consider before separating your cells:

·         To ensure consistent data separation, standardize all cell data and corresponding delimiters. Using Find and Replace can make standardization quicker and easier.

·         Do not use delimiters that also function as text characters.
EXAMPLE: In Eau Claire WI, the space between Eau Claire and WI could work as a delimiter. But there is also a space operating as a text character between Eau and Claire, which you would not want to separate. One solution could involve separating cities and states with commas and using the comma as your delimiter.

·         Determine if you should separate your data into three columns (e.g., Jane M. Doe or John Doe Jr.).

·         Look for consecutive delimiters (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you should select the Treat consecutive delimiters as one option.
NOTE: If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one more blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns

read more "Separating Text in Excel (Considerations)"

Hiding Columns, Rows, and Cells in Excel (Hiding Cell Contents)

You have the ability to hide the contents of individual cells if you do not need to view their contents or you simply do not want to print certain cells.

1.      Select the cell(s) to be hidden

2.      From the Home command tab, in the Cells group, click FORMAT » select Format Cells...
The Format Cells dialog box appears.

3.      Select the Number tab

4.      Under Category, select Custom

5.      In the Type text box, type three semicolons ( ;;; ) 

6.      Click OK
The cells are now hidden.

To redisplay cell information:

1.      Select the cell(s) to be redisplayed

2.      From the Home command tab, in the Cells group, click FORMAT » select Format Cells...
The Format Cells dialog box appears.

3.      Select the Number tab

4.      Under Category, make the appropriate selection
The dialog box refreshes to display options corresponding to the selected category.

5.      Select the desired options

6.      Click OK
The cell(s) reappear.

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Cell Contents)"

Hiding Columns, Rows, and Cells in Excel (Redisplaying Rows)

1.      Select at least one cell from both of the rows around the hidden row(s) to be redisplayed
EXAMPLE: If row 5 is hidden, select a cell from rows 4 and 6.
HINT: If you cannot select the appropriate cells, you can use the Go To command.

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Unhide Rows
The row appears.

Redisplaying Rows: Quick Menu Option

This option works well for redisplaying row 1, since there are not rows on both sides of row 1.

1.      Hold your cursor between row IDs where the hidden row is located
The cursor will change to an open, double sided arrow as shown here.

2.      Right click » select Unhide

read more "Hiding Columns, Rows, and Cells in Excel (Redisplaying Rows)"

Hiding Columns, Rows, and Cells in Excel (Hiding Rows)

You can hide rows containing information that you do not need to view or do not want to print.

1.      Select a cell within the row(s) to be hidden

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Hide Rows
The row is hidden.

Hiding Rows: Quick Menu Option

1.      Right click the row ID » select Hide
The row is hidden.

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Rows)"

Hiding Columns, Rows, and Cells in Excel (Redisplaying Columns)

1.      Select at least one cell from both of the columns around the hidden column(s) to be redisplayed
EXAMPLE: If column B is hidden, select a cell from both columns A and C.
HINT: If you cannot select the appropriate cells, you can use the Go To command.

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Unhide Columns
The column reappears.

Redisplaying Columns: Quick Menu Option

This option works well for redisplaying column A, since there are not columns on both sides of column A.

1.      Hold your cursor between column IDs on the right side of the hidden column
The cursor will change to an open, double sided arrow as shown here.

2.      Right click » select Unhide

read more "Hiding Columns, Rows, and Cells in Excel (Redisplaying Columns)"

Hiding Columns, Rows, and Cells in Excel (Hiding Columns)

You can hide columns of your worksheet containing information that you do not need to view or do not want to print.

1.      Select a cell within the column(s) to be hidden

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Hide Columns
The column is hidden.

Hiding Columns: Quick Menu Option

1.      Right click the column ID » select Hide

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Columns)"

Find and Replace Features in Excel (Using the Replace Feature)

The Replace feature is useful when you want to change the same piece of information throughout your worksheet.
WARNING: Use the Replace All option with caution. If you do not first select specific cells to change, this option will replace the specified information throughout the entire document. This can change the meaning of your document if text is replaced where it should not be.

1.      OPTIONAL: If you want to find and/or replace information in a specific section of your document, select the desired portion of the document
HINTS:
To select contiguous cells, click the desired cells
To select non-contiguous cells, hold down [Ctrl] + click the desired cell(s)

2.      In the Editing group, click FIND & SELECT» select Replace...
OR
Press [Ctrl] + [H]
The Find and Replace dialog box appears.

3.      In the Find what text box, type the text or data to be found

4.      In the Replace with text box, type the text or data to replace the information found

5.      OPTIONAL: Adjust the searching options

6.      Click FIND NEXT
The first occurrence is highlighted.

7.      To replace only that occurrence, click REPLACE
To replace all occurrences, click REPLACE ALL
The old information is replaced with the new.
WARNING: Use the Replace All option with caution. If you do not first select specific cells to change, this option will replace the specified information throughout the entire document. This can change the meaning of your document if text is replaced where it should not be.

8.      To find and replace additional instances of your text or data, repeat steps 6–7

9.      To close the dialog box, click CLOSE

read more "Find and Replace Features in Excel (Using the Replace Feature)"