Excel 2019 Bible

Chapter 25: Importing and Cleaning Data

2525

3. Click the column header to the right of where you want the column to go.


4. Right-click and choose Insert Cut Cells from the shortcut menu.

Repeat these steps until the columns are in the order you want.

Randomizing the rows

If you need to arrange the rows in random order, here’s a quick way to do it. In the column

to the right of the data, insert this formula into the first cell and copy it down:

=RAND()

Then sort the data using this column as the sort key. The rows will be in random order, and

you can delete the column.

Extracting a filename from a URL

In some cases, you may have a list of URLs and need to extract only the filename. The fol-

lowing formula returns the filename from a URL. Assume that cell A2 contains this URL:

http://example.com/assets/images/horse.jpg

The following formula returns horse.jpg:

=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"/","*",LEN(A2)-

LEN(SUBSTITUTE(A2,"/","")))))

This formula returns all text that follows the last slash character. If cell A2 doesn’t contain

a slash character, the formula returns an error.

To extract the URL without the filename, use this formula:

=LEFT(A2,FIND("*",SUBSTITUTE(A2,"/","*",LEN(A2)-

LEN(SUBSTITUTE(A2,"/","")))))

This type of extraction is a good use for the Flash Fill feature. (See “Splitting text” earlier in this chapter.)

Matching text in a list

You may have some data that you need to check against another list. For example, you may

want to identify the data rows in which data in a particular column appears in a different

list. Figure 25.20 shows a simple example. The data is in columns A:C. The goal is to iden-

tify the rows in which the Member Num appears in the Resigned Members list in column F.

These rows can then be deleted.