Whether you’re tidying up data for a presentation or preparing it for analysis, understanding how to manipulate strings efficiently is a key skill. In this blog, we will explore using the IF function in Excel to replace strings or manage null (blank) values in your datasets.
At its core, the IF function performs a logical test and returns one value for a TRUE result, and another for a FALSE result. It’s a fundamental part of data management in Excel, and here’s how it can be applied to handling strings:
- String Replacement: Change the contents of a cell to a new string based on a condition.
- Null Handling: Assign a default value to empty cells/missing cells from a source list to maintain data consistency.
Consider a sample dataset of product categories from cell D7 in Excel as shown:
From this list, say, you need to change the cells with “Movies” (old string) into “Entertainment” (our new string), and have that entered in cells G4 and H4 respectively. And, a default value to replace when we encounter an empty cell (null string) in cell I4:
This can be easily achieved using our good old IF function.
This function takes as input a logical test and based on the result (i.e. true/false) returns a value accordingly.
First, let us look at replacing an existing string with a new one, in our case “Movies” with “Entertainment”.
So starting from cell D7 (our first product category from the list) with IF function, we’ll check if the cell’s contents are equal to the old value (in cell G4), if yes, replace the string with the new value (from cell H4) or return the old value itself. Our formula would look something like this,
=IF(D7=$G$4,$H$4,D7)Note: G4 and H4 are locked cell references with a $ before and after, as shown in the formula.
Drag the cell till the cells you want the formula to populate and Excel will apply the same formula across those cells.
That is how we replace a string with a new string using the IF function. Now how do we handle null (empty strings)?
Similar to the previous scenario, we’ll first check if the cell’s contents are empty (from D7 onwards), if yes, we’ll replace it with the default value (from cell I4), or we’ll use another IF function to check if there’s any string replacement needed.
=IF(D7="",$I$4,IF(D7=$G$4,$H$4,D7))Our updated product category will be,
We have created a dedicated, step-by-step video on string handling, check it out here:
Do check our post on handling partial strings.
