I just discovered this little timesaver and thought I would share it with you. Okay, to be honest, my husband told me about it.
Suppose you have an Excel spreadsheet and you have a number of product codes all in one row and need to break them out. You can do text to columns but if the number of product codes in the rows are uneven, it can be time-consuming to collect them all for analysis. Using Find & Select Constants solves this problem.
Let’s use a simple example. Below is a spreadsheet showing certificate ids that customers have been issued.
If I want to break out those certificate ids to count how many of each were issued, I would first select all the data in Column C and then select Data>Text to Columns. As you can see the data is delimited with Commas.
Click Next and select Comma.
Select Finish.
Text to Columns separates all the certificate ids into separate columns.
In this example, it would be fairly easy to copy and paste the different certificate numbers into Column C but if you had rows and rows of data it would be very time-consuming especially if the certificate ids extended over into Column L and M or further. This is where Find and Select Constants comes in.
Select all of Column D and then click Find and Select.
Excel selects all the cells in Column D that contain data.
All the cells are selected so I can just select Copy and then Paste those certificate ids into Cell C9.
Excel ignores the blank cells and nicely grouped my selection so they are all contiguous. I can then delete the data in Column D since it now displays in Column C and then move on to the next column of certificates. Unfortunately you need to copy as Excel does not let you cut multiple range selections.
Hopefully you see some applications for this. I found it quite handy.