How to find links in a workbook

There is no automatic way to find links used in a workbook. However links use brackets [ ] to enclose the source workbook name, and it also uses the exclamation mark at the end of the sheet name in the link. So you can search for one of these two symbols depending on what type of links you are looking for.

If you are looking for external links only, use the ‘[‘ . If you are looking for all links, external links and links within the same workbook, then use ‘!’.

Examples of links:

1. =Sheet2!$C$1
2. =[Book2]Sheet1!$C$1

Link 1 above is a link to a worksheet within the same workbook, while link 2 is a link to an external workbook.

Steps to find a link:

1. On the Edit menu, click Find. the Find and replace window will open.
2. Click Options to expand the window.
3. In the Find what box, enter “!”.
4. In the Within box, select Workbook.
5. In the Look In box, select Formulas.
6. Click Find All.
7. Your links will be listed in the box at the bottom of the Find and replace window.
8. To select the cell with a link, select the line that contains the link.

findlink1

Applies to Excel 2003

How to extract text from another text string

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:
=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

How to Find cells that have data validation

If you want to find cells in your worksheet that have data validation, then follow these steps:

1. On the Edit menu, click Go To. The Go To window will open.

2. Click Special. The Go To Special window will open.

gotospecialv

3. Click Data validation.
4. At the bottom of the window click on the radio button that says All.

All cells with data validation will be highlighted.

To find cells that match certain data validation settings then you follow similar steps with slight variations:

1. Click the cell that has the data validation settings for which you want to find matches.
2. On the Edit menu, click Go To. The Go To window will open.
3. Click Special. The Go To Special window will open.
4. Click Data validation.
5. At the bottom of the window click on the radio button that says Same.

All cells with matching data validation will be highlighted.

How to find cells that have conditional formats

If you want to find cells that have conditional formatting, then you have two choices:

1. Find cells with any conditional formatting.

2. Find cells that have conditional formatting settings identical to the settings of a specific cell.

For option 1 do the following:

1. On the Edit menu, click Go To. The Go To window will open

GoTo

2. Click Special. The Go To Special window will open.

gotospecial

3. Click Conditional formats.

4. At the bottom of the window click on the radio button that says All.

All cells with conditional formatting will be highlighted.

For option 2 do the following:

1. Click the specific cell to compare to.
2. On the Edit menu, click Go To. The Go To window will open
3. Click Special. The Go To Special window will open.
4. Click Conditional formats.

5. At the bottom of the window click on the radio button that says Same.

All cells with conditional formatting that matches the selected cell will be highlighted.