In this week’s 3 tip Friday we cover more useful Excel tips and tricks - you will learn how to use Find and Replace in Excel to search for specific data in a worksheet or workbook, how to use shortcut keys to hide and unhide rows and the Excel LEN function returns the length of the specified string.
Find and Replace functionality in Excel
Excel has the functionality to help you find a text in an Excel document and replace it.
First, select the area you want to search and in the Find & Select in the Home section click on Find. You can do this quicker by pressing CTRL+F:
In the dialogue window that pops up, you have two sections:
Find what: we will enter here the text (number, character, paragraph and so on) that we want to search.
Using the Find Next button here, you can move on to the next value that matches your search text.
If you click on the Options button you will find a few criteria’s to filter your search results:
Format: this will look for a text with a specific format, so if you are looking for the number 10 and you choose accounting £ as a format option, it will only match the £10 In your sheet, if you have a number 10 somewhere, it will be ignored.
Within: you can decide here if to search only the current sheet or the whole workbook.
Search: you can choose here to look first by rows or by columns.
Look in: you can choose here if you want to look in values, formulas or even comments.
Match Case: click this if the case of the text is important. If you search for “UK” and in your sheet you have a text “uk”, you will not be able to find it if this button is clicked.
Match entire cell contents: click here if you don’t want to look within a cell but match the whole text of the cell to your search text. So if you are looking for James in your sheet and in one of the cells you have the text James Brown, you will not be able to find it.
Once you are done choosing your options, click on Replace:
Replace – we will enter here the replacement text (characters)
The same options apply here and you can decide if you want to replace each value one by one, by pressing Replace or if you want to replace every value at once the click on Replace All.
Shortcut to hide rows in Excel
A quick way to hide rows in Excel is to use the CTRL+9 shortcut. Just select the rows (or just select a block of cells from these rows) that you want to hide and press CTRL+9.
Use CTRL+Shift+9 to unhide the rows quickly.
Len Function in Excel
The Len function in Excel is used to find the number of characters in a string.
This function takes only one argument – text.
You can enter a sentence /paragraph as the argument:
Please note: the spaces will be counted as a character.
The most common way to use this function is to count the characters in a cell, using the cell reference as the argument: