These tutorials based on readers' questions detail how to combine formulas with Excel's data validation and feature a Word Replace trick.
I recently answered questions from readers Venkat and Vekaria, who couldn't get the solutions in two of my articles to work for them. In Venkat's case, I offered a quick solution off the top of my head that didn't work, but from there, the solution came easily to Venkat. Vekaria's solution was in the article, but because I was applying it differently, it wasn't obvious. Fortunately, both solutions are easy to implement.
I'm using Office 365 on a Windows 10 64-bit system, but you can use earlier versions of both Excel and Word. The Excel data validation formula works in the browser, but the Word Replace code doesn't. You can work with your own data or download the demonstration .xlsx and .docx files.
LEARN MORE: Office 365 Consumer pricing and features
More limits to data validation formula
Venkat read Use Excel data validation to prevent duplicate values in a column and got close to the needed solution, but it wasn't a complete success. This article combines a formula and data validation to omit duplicates within a column. It's an all-or-nothing solution, and Venkat wants to allow duplicate 0s. Fortunately, the formula is easily modified to allow for this situation. Before we get to Venkat's modified formula, let's review the basic formula.
Figure A shows a simple data set in a Table object, and we want to make sure that all the values in the Membership Number field are unique. Add the data validation control as follows:
- Select all existing data cells in the column in question, which in this case is B3: B6.
- Click the Data tab and choose Data Validation from the Data Validation dropdown in the Data Tools group.
- In the resulting dialog, choose Custom from the Allow dropdown.
- In the Formula control, enter the formula (Figure B)
making sure to use straight (not curly) quotes. If you're working with your own data, be sure to update the name of the Table and Column.
- Click OK.
Test the control by trying to enter a duplicate value (Figure C). As you can see, Excel rejects the value. At this point, Excel will allow you to enter 0 once, but not twice. The INDIRECT() function accommodates the structured referencing for the Table object. (You could use a named range instead, but a Table object is dynamic.)
This formula works for Venkat to a point, but it requires a slight modification to allow duplicate 0s. You might try an IF() function that captures 0s, but it won't work as expected because the logic isn't right. In this case, it works sort of like an except clause, and it just doesn't work. That was my first suggestion (without testing), and it didn't work.
What we need is either/or logic, and we can use an OR() operator for that, and here's what Venkat came up with instead:
In this case, the function returns a unique value or a 0, even when 0 isn't unique. It works great!
Thanks to Vendak for the fun challenge and for getting back to me with a working formula so I could share it with everyone.
Word Replace trick
My TechRepublic article Seven time-saving Replace tricks for changing Word formatting has numerous quick Replace tricks for formatting changes, but the solution didn't solve Vekaria's exact problem. Vekaria wanted to add to an existing term without changing the destination formatting. For instance, Vekaria wanted to replace the words term, Term, TERM, and term with new term, New Term, NEW TERM, and new term. It sounds impossible, right? Fortunately, it's quite easy using the ^& code.
The ^& code instructs Word to replace the found string with itself, and we need to do nothing regarding the formatting. Let's take a quick look at using the simple strings shown in Figure D. As you can see, there are several instances of the word term, all formatted a bit differently.
Now, let's Replace as follows:
- Click Replace in the Editing group on the Home tab.
- In the Find What control enter term--the word, you want to keep.
- In the Replace With control, enter new ^&. There's a space between new and ^.
- Click Replace All.
As you can see in Figure E, the Replace code worked a bit of magic. Every time Word encounters the word term, it adds the word new (there's a space after new) in front of the found string, term. Formatting isn't lost but applied to new.
If you have an interesting Find & Replace trick, please share it in the Comments section below.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- How to use named ranges to quickly navigate an Excel workbook (TechRepublic)
- How to add a drop-down list to an Excel cell (TechRepublic)
- 50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic)
- Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
- How to use Windows 10 Task Scheduler to run Office 365 tasks (TechRepublic)
- Microsoft Office has changed, how you use it should too (ZDNet)
- Best cloud services for small businesses (CNET)
- Best to-do list apps for managing tasks on any platform (Download.com)
- More must-read Microsoft-related coverage (TechRepublic on Flipboard)