Office Q&A: How to combine formulas with Excel

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

  Multiracial project team working together on a laptop analyzing the result online

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 the case of Venkat, I offered a quick solution from the top of my head that didn't work, but from there, the solution easily reached Venkat. Vekaria's solution was in the article, but since he was applying it differently, it was not obvious. Fortunately, both solutions are easy to implement.

I am using Office 365 on a Windows 10 64-bit system, but can use earlier versions of Excel and Word. The Excel data validation formula works in the browser, but the Word replacement code does not. You can work with your own data or download the demo files .xlsx and .docx.

MORE INFORMATION: Features and prices of the Office 365 consumer

More limits for the data validation formula

Venkat read Use Excel data validation to avoid duplicate values ​​in a column and He approached the necessary solution, but it was not a complete success. This article combines a formula and data validation to omit duplicates within a column. It is an all or nothing solution, and Venkat wants to allow duplicate zeros. Fortunately, the formula is easily modified to allow this situation. Before we get to the modified Venkat 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 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 drop-down menu in the Data Tool Group.
  3. In the resulting dialog box, choose Customize from the Allow drop-down menu.
  4. In the Formula control, enter the formula ( Figure B )
    = COUNTIF (INDIRECT ("Table1 [Membership Number]"), B3) <= 1
    making sure to use straight quotes (not curly). If you are working with your own data, be sure to update the name of the Table and the Column.
  5. Click OK.

Figure A

  officeqa-a.jpg officeqa-18jpg [19199090] 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 when 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 reference for the Table object. (Instead, you could use a named range, but a Table object is dynamic.)

Figure C

  officeqa-c.jpg officeqa-cjpg [19659018font>te19659030] http: / / “height =” 465 “width =” 620 “/>

This formula works for Venkat to some extent, but requires a slight modification to allow duplicate 0s. You can try an IF () function that captures 0s, but it will not work as expected because the logic is not correct. In this case, it works as a clause except and simply does not work. That was my first suggestion (without evidence), and it didn't work.

What we need is logical, and we can use an OR () operator for that, and this is what happened to Venkat:

= 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 is not unique. It works very well!

Thanks to Vendak for the fun challenge and for responding to me with a formula that works to be able to share it with everyone.

Word replacement trick

My TechRepublic article Seven replacement tricks to save time to change Word format has numerous quick replacement tricks for formatting changes, but the solution did not solve Vekaria's exact problem. Vekaria wanted to add to an existing term without changing the target format. For example, Vekaria wanted to replace the words term, Term, TERM and term with new term, New term, NEW TERM and new term . Sounds impossible, right? Fortunately, it is quite easy to use the code ^ &.

The ^ & code tells Word to replace the string found with itself, and we don't need to do anything about the format. Let's take a quick look at the use of the simple chains shown in Figure D . As you can see, there are several instances of the word term, all with a slightly different format.

Figure D

  officeqa-d.jpg "data-original =" 9970-90b220d68ee3 / resize / 770x / 9d6ee64c45bd10b894af7f364e4d065a / officeqa-d.jpg

We can use Replace to add a word format and retain the target format http. [196590://wwwtechrepubliccom/"height="465"width="620″/>

Now, replace as follows:

  1. Click Replace in the Edit group on the Home tab.
  2. In the Search control, enter the term : the word you want to keep.
  3. In the Replace with control, enter new ^ & . There is a space between new and ^.
  4. Click Replace All.

As you can see in Figure E the code Replace worked a little magic. Every time Word finds the word term it adds the word new (there is a space after again) in front of the string found, term . The format is not lost but is applied again.

Figure E

  officeqa-e.jpg "data-original =" b00f-274d6ae75849 / resize / 770x / a0876dcbf280bc2c1132dc416bf1db2f / officeqa-e.jpg

The code ^ and will replace the search term with itself.

If you have an interesting search and replace trick, 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. Do not send files unless requested; Initial requests for help arriving with attachments will be deleted without reading. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please solve the problems in my workbook and fix what is wrong" you probably won't receive an answer, but "Can you tell me why this formula does not return the expected results?" might. Mention the application and the version you are using. TechRepublic does not reimburse me for my time or experience in helping readers, nor do I request a fee from the readers I help. You can contact me at [email protected]

See also

Affiliate Disclosure: TechRepublic can earn a commission for the products and services shown on this page.

For More Updates Check out Blog, Windows Softwares Drivers, Antivirus, Ms Office, Graphic Design Don’t Forget to Look Our Facebook Page Get Into Pc like us & follow on Twitter- @getinpc

Please Note: This content is provided and hosted by a 3rd party server. Sometimes these servers may include advertisements. does not host or upload this material and is not responsible for the content.

About The Author