Office Q&A: How to combine formulas with Excel's data validation and a Word Replace trick

These tutorials based on readers' questions detail how to combine formulas with Excel's data validation and feature a Word Replace trick.

Multiracial project team working together on laptop analyzing online result

Image: Getty Images/iStockphoto

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:

  1. Select all existing data cells in the column in question, which in this case is B3: B6.
  2. Click the Data tab and choose Data Validation from the Data Validation dropdown in the Data Tools group.
  3. In the resulting dialog, choose Custom from the Allow dropdown.
  4. In the Formula control, enter the formula (Figure B)
    =COUNTIF(INDIRECT("Table1[Membership Number]"),B3)<=1
    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.
  5. Click OK.

Figure A

officeqa-a.jpg

Combine a formula and data validation to reject duplicate values in the Membership Number field.

Figure B

officeqa-b.jpg

Enter this custom rule to reject all duplicate values.

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.) 

Figure C

officeqa-c.jpg

Excel rejects the duplicate value.

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:

=OR(COUNTIF(INDIRECT("Table1[Membership Number]"),B3)<=1,B3=0)  

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.

Figure D

officeqa-d.jpg

We can use Replace to add a word and retain the destination formatting.

Now, let's Replace as follows:

  1. Click Replace in the Editing group on the Home tab.
  2. In the Find What control enter term--the word, you want to keep.
  3. In the Replace With control, enter new ^&. There's a space between new and ^.
  4. 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.

Figure E

officeqa-e.jpg

The ^& code will replace the find term with itself.

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 susansalesharkins@gmail.com.

Also see

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.