find and replace multiple values

You can use nested SUBSTITUTE formulas in Excel to find and replace multiple values in Excel. In this example, I'll show how to replace the names of each of the 50 states with their abbreviations. You need to have one SUBSTITUTE command for each of the terms.

1. Begin by setting up the formula in a spare worksheet. Enter the formula:

SUBSTITUTE(

. . . on 50 successive lines

2. Then enter the cell with the first value you need to replace ("A2") followed by INDEX commands to refer to the terms that you're looking for:

INDEX(find,1

. . . pulled down until you get to INDEX(find,50

3. Then enter a second INDEX command referring to the terms you want to use as replacements:

),INDEX(replace,1

. . . to ),INDEX(replace,50 in the column to the right alongside the INDEX(find entries.

4. In a third column enter:

)),

. . . another 49 times with the last entry as: ))

You should have the text for the formula set up this way:

5. Next in Word or a text editor find and replace all of the paragraph breaks and tabs with nothing.

6. Copy the resulting formula back into Excel in the cell next to the first one with a state name that you want to look up the abbreviation for, and then put an equal sign in front.

7. Next set up the named ranges referred to in the formula - 'find' and 'replace'. Simply list the state names in one column. Select all of the cells and in the name box at the top left name this range, 'find'. Then do the same for the cell range with the state abbreviations, giving them the name 'replace'.

8. The formula will then generate the correct replacement for each value in column A. Just pull it down by selecting the empty cells in column B and pressing ALT + D.

Checking for overlapping numerical ranges

jdawson422 has posted a nice, easy, and useful Excel formula here, that you can use to check for overlapping ranges in a list of Bates numbers.

=OR(A1=A2,B1=B2,B1=A2,A1=B2,AND(B1>A2,B1<B2),AND(B2>A1,B2<B1))

The formula indicates when a numerical range listed in columns A and B overlaps with the range listed in the above row. It will give a "TRUE" result when the range overlaps with the previous one to various extents.

Excel formula to find last column with data

If you're entering data on an Excel spreadsheet in successive columns, sometimes using all of the columns to the right, but often only using some of these columns, you can use a formula to find which is the last column with an entry.

Use the LOOKUP formula to find the first cell in a range on a particular row that does not have data entered. It's necessary to enter the both the first column and the last possible column that may contain data - in this example column E.

=LOOKUP(2,1/ (B7:E7<>""),ROW(A:A))

So the formula lookups up 2 and then finds where in the range from columns B to E the value nearest appears and then returns the number of the row in which that value appears. So if in this example in row 7 we look for the first column in which there is no entry, which in this case is the third in the array - 1/3 equals .333, the closet value to 2 (the blank entry in the fourth column would equal 1/4 .25 - not as close to 2 as .333) and then we return the result - the number of the column where this value appears.