We have something different for today’s Workday Adaptive Planning Tips & Tricks post – an Excel tip! Our Adaptive Planning users also use Excel, so we thought they’d find this helpful.
Have you ever wanted to get rid of spaces in some of your Excel cells? Maybe you do not want a cell to have separate words and instead want to use an underscore character. Or maybe you are in a situation where you want to adjust a prefix for a certain range of cells. You can use Excel’s find and replace functionality, but this approach could lead to a time consuming effort if you want to pick and choose the cells where it applies.
Excel’s SUBSTITUTE function can help you solve this problem. The SUBSTITUTE function is used to find a specific set of characters and replace it with something else while also giving you the ability to define details within cells.
The syntax of the function is:
|=SUBSTITUTE (text, old_text, new_text)|
- This is the source that will be changed; this is typically a cell reference.
- This is the subtext that will be replaced.
- This is what will replace the old subtext
If the cell in A5 consists of “Happy Birthday” then it can be updated to “Happy_Birthday” via the following:
|=SUBSTITUTE (A5, “ “, “_”)|
In addition, the parameter that defines the new text can also be a cell reference. This gives you the ability to quickly change the results of a large set of data by simply updating a single cell.
This approach will allow you to quickly find and replace characters within specific cells instead of having to manually go through a set of cells via Excel’s find and replace functionality.
The team at Revelwood has been recognized by Adaptive for its thought leadership in the space, commitment to its Adaptive Insights practice, and its rapid achievements of milestones. Visit Revelwood’s Knowledge Center for our Adaptive Insights Tips & Tricks or sign up here to get our Adaptive Insights Tips & Tricks delivered directly to your inbox. Not sure where to start with Adaptive Insights? Our team here at Revelwood can help! Contact us firstname.lastname@example.org for more information.