This post will guide you how to extract another cell value if one specified cell equals to a given value or an adjacent cell value using a formula in Excel 2013/2016/2019/365. Normally, if you want to do some operation when a given cell equals a certain value, and you can create a new formula based on IF function to test that value, then do something if the test result is TRUE, otherwise, do something else if the test result is FALSE.
1. Get Another Cell Value If One Cell Equals in Excel
Assuming that you have a list of data in range A1:C5, and you want to extract cell values from column C when the corresponding cell value in Column A equals the adjacent cell values in Column B, if True, return the corresponding Cell value in Column C. How to achieve it.
You need to enter the above formula into a blank cell and then drag it down to other cells to apply this formula.
Note: Cell A1 and Cell B1 are the two cells that you wish to compare, and Cell C1 is the cell value that you want to extract.
There is another example based on the following data table, you just simply want to get cell Value in Column B when the Cell value in Column C equals to “70$”.
You can use the below formula:
=IF(C1="70$",B1,"")
The logic test for the above formula is:
C1=”70$”
This will return True Value if Cell C1 value equals a certain value “70$”, otherwise, returns FALSE.
We only need to take action when the result of the logic test is TRUE. And here it will return the corresponding cell value in Column B when the cell value in Column C equals “70$”. And if the cell value is not “70$”, it will return an empty string.
For the above formula, if you did not specify that empty string when the logic test is FALSE, and the formula would display FALSE whenever the Cell value is not “70$”.
Actually, you can also do something more complicated operation as well. For example, you wish to increase the sales when the price is equal to “70$” by 100. In this case, you can could use the below formula based on IF function:
=IF(C1="70$",B1+100,"")
From the above screenshot, and you would see that the original value in Column B has been increased by 100 when the cell value in Column C is “70$”.
2. Video: Get Another Cell Value If One Cell Equals in Excel
This video will demonstrate how to retrieve another cell value if a specific cell matches a certain criteria in Excel.
3. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
This post will guide you how to vlookup values in a range of cells and then return True or False values in Excel. How to use the VLOOKUP function to vlookup a value in a list of data, and return True or False values in a selected cells.
In Excel, you can use the VLOOKUP function to look for a value in a column in a table and then returns TRUE from a given column in that table if it finds something. If it doesn’t, it returns FALSE.
1. Excel Vlookup Return True or False
Assuming that you have a list of data in range of cells A1:A6, and you want to search the range A1:A6 to look for values in range C1:C2. If found, then return True value, otherwise, return False. How to achieve it. You can use the VLOOKUP function in combination with the IF unction and the ISNA function to vlookup value. Like this:
Type this formula in the formula box of Cell B1, and press Enter key in your keyboard. And drag the AutoFill handle over other cells to apply this formula.
2. Video: Excel Vlookup Return True or False
This video will introduce you to a simple and effective formula for performing a VLOOKUP function in Excel to search for values in a range of cells and then return True or False values based on the existence of the lookup value.
3. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel VLOOKUP function The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
Excel ISNA function The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….
This post will guide you on how to average only positive or negative numbers of a range in Excel 2013/2016/2019/365.
Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative numbers ignored. In this article, we will help you to construct a formula with AVERAGE and IF functions to get average of only positive numbers or negative numbers.
Refer to above left side table, we can see both positive numbers and negative numbers are listed in range A2:E4. We want to calculate average of positive numbers and negative numbers separately and save results in H2 and I2 correspondingly.
In this instance, we will enter below formula into H2. We build this formula with AVERAGE and IF function.
The two functions are used frequently in Excel when running mathematics and logical expression. The AVERAGE function returns the average of numbers from a given range reference. The IF function returns “true value” or “false value” based on the result of provided logical test. It is one of the most popular function in Excel.
Range reference A2:E4 represents all numbers in this range.
This formula will execute IF function firstly to filter and keep all positive numbers in current array. If numbers are greater than 0, they are positive numbers. If logical expression “A2:E4>0” is true (number in A2:E4 is greater than 0), this number will be saved in array A2:E4.
After comparing each number in range A2:E4 with 0, below numbers are filtered and saved. Then AVERAGE function will calculate the average of these numbers.
After entering the formula, press Ctrl + Shift + Enter to load result because this is an array formula. But on Excel 365 you can directly press Enter as usual to load result.
3. Video: Average Only Positive or Negative Numbers of a Range in Excel
This video will demonstrate how to easily calculate the average of only positive or negative numbers within a range in Excel.
4. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value],
Excel AVERAGE function The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the below steps to solve this question by formula quickly.
Prepare a list of products and there are some duplicates among the list. See example below:
And we want to count the total product type in one year, how can we do count?
If we just use sum formula to do count, then duplicates will be included, so we need another formula to do count excludes the duplicates. See steps below.
1. Count Unique Values Excluding Duplicates by Formula in Excel
Step 1: In E2 which is saved the total product type number, enter the formula:
Where B1:B11 is the range you want to count the unique values.
Step 2: Click Enter and get the result in E2. We can check the result is 6, and the duplicates are not included.
In above sample, we do count for unique values and get the result 6, because we have six products A\B\C\D\E\F, and if we want to only count the unique values exclude all duplicates like product A\B\C (they appeared more than one season) how can we do count? See below steps.
2. Count Unique Values Excluding All Duplicates by Formula in Excel
Step 1: In E2 which is saved the total product type number, enter the formula:
Where B1:B11 is the range you want to count the unique values.
Step2: Click Enter and get the result in E2. We can check the result is 3 (Product D\E\F are unique values and only appeared in one season).
3. Video: Count Only Unique Values Excluding Duplicates
If you want to learn how to count only unique values excluding duplicates in Excel, this video will show you a simple and effective formula that you can use in any situation.
4. Related Functions
Excel SUMPRODUCT function The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel COUNTIF function The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel ROW function The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel SUM function The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two dates. However, if you want to find the number of days a ticket has been opened, you can use the if function.
This function calculates the number of days between two dates. It requires the date to be in an Excel-compatible format. Using the IF function, you can also specify the year, month, and day.
1. Calculate the Number of Days a Ticket Has Been Opened in Excel
It is important to track the time it takes to resolve support tickets whenever you are dealing with them. You can use Excel’s built-in function to calculate the backlog to do this. When your team members work long hours, the number of tickets they must resolve each day can increase significantly. In order to calculate the backlog, you first need to know the average resolution time of tickets. Once you have the average resolution time, you can subtract the open date from the current date to calculate the days a ticket has been open.
To calculate how many days a ticket has been open, you must first determine the date when the ticket was authored. You can also find out the number of days between a ticket’s date of issuance and its closing date by adding up the days between the two dates. Here’s an example. The ticket was created on April 18, 2022, and is still open. You can also calculate the number of days the ticket has been open using Excel. The formula is easy to use and makes analyzing your data easier.
=IF(ISBLANK(D5),TODAY()-C5,D5-C5)
If you want to calculate the number of days the spreadsheet has been open, you can use the IF function. In this case, you will need to input a date in a format that Excel understands.
2. Conclusion
You can use the IF function to Calculate the number of days a ticket remains open. You can enter the values in the formula, and a calculation will display the number of days that have passed since it was opened. The result of this calculation will be the number of days between the original ticket creation date and the date it was last reopened. By just putting in the values in the manner mentioned above, you can calculate the days opened!
3. Related Functions
Excel ISBLANK function The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel TODAY function The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
Excel DAYS function The Excel DAYS function returns the number of days between two dates.The syntax of the DAYS function is as below:= DAYS (end_date,start_date)…
This post will guide you how to align duplicate values within two columns based on the first column in your worksheet in Excel. How do I use an formula to align two columns duplicate values in Excel.
1. Aligning Duplicate Values in Two Columns using Formula
Assuming that you have two columns including product names in your worksheet. and you want to find duplicate values in those two columns(Column A and Column B) and align them based on the first column in a new column(Column A and Column B). How to do it. And you can use an formula based on theIF function, the ISNA function, the MATCH function, and the INDEX function. Like below:
Type this formula into the first cell in Column C, and press Enter key on your keyboard to apply it. then drag the AutoFill handle down to other cells to apply this formula.
Note: Cell A1 is the first cell in the first column, and B1:B7 is the range of cells in the second column.
2. Aligning Duplicate Values in Two Columns with VBA Code
You can also align duplicate values within two columns using VBA Macro in Excel, just do the following steps:
Step1: Press “Alt” and “F11” keys at the same time to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, select “Insert” from the menu bar, then choose “Module” to create a new module.
Step3: Copy and paste the below VBA code into the new module. Close the Visual Basic Editor and return to the Excel sheet.
Sub CheckDuplicateValues_ExcelHow()
Dim sourceRange As Range, lookupRange As Range, outputRange As Range
Dim i As Long, matchIndex As Variant
' prompt the user to select the source range
On Error Resume Next ' to handle the cancel button
Set sourceRange = Application.InputBox("Select the source range:", Type:=8)
On Error GoTo 0 ' reset the error handling
If sourceRange Is Nothing Then Exit Sub ' exit if the user cancels
' prompt the user to select the lookup range
On Error Resume Next
Set lookupRange = Application.InputBox("Select the lookup range:", Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
' prompt the user to select the output range
On Error Resume Next
Set outputRange = Application.InputBox("Select the output range:", Type:=8)
On Error GoTo 0
If outputRange Is Nothing Then Exit Sub
For i = 1 To sourceRange.Cells.Count
matchIndex = Application.Match(sourceRange.Cells(i), lookupRange, 0)
If IsError(matchIndex) Then
outputRange.Cells(i).Value = ""
Else
outputRange.Cells(i).Value = Application.Index(sourceRange.Resize(, 2), matchIndex, 2)
End If
Next i
End Sub
Step4: Press “Alt” and “F8” keys at the same time to open the Macro dialog box. Select the macro ” CheckDuplicateValues_ExcelHow” from the list and click the “Run” button to execute the code.
Step5: The code will prompt you to select the source range, lookup range, and output range.
Step6: The code will run and populate the output range with the values that match between the source and lookup ranges.
You should now have the results in the output range.
3. Video: Aligning Duplicate Values in Two Columns
This video will show you how to align duplicate values within two columns in Excel using both a formula and a VBA code.
4. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel INDEX function The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel ISNA function The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….
This post will guide you on how to count numbers with leading zeros in Excel 2013/2016/2019/365. When working with numbers that have leading zeros, it can be challenging to count the number of zeros that appear before the actual value. Fortunately, there are two main methods for counting leading zeros in Excel: using a formula or using VBA code.
In this post, we’ll walk through the steps for each method, so you can choose the one that works best for your needs.
1. Count Numbers with Leading Zeros using Formulas
Suppost you have a list of product ID in your worksheet, and the product ID contains the different number of leading zeros in the front of string. And if you use the COUNTIF function to count numbers with leading zeros, but it will remove those leading zeros, it means that 1289 and 001289 are the same strings.
So how to preserve the leading zeros while counting numbers. And you can use another Excel function named as SUMPRODUCT.
For example, you want to count strings with leading zeros in the range A2:A6, you can write down the following formula based on the SUMPRODUCT function.
Note: when you enter this formula into Cell B2, you still need to press Ctrl+Shift+Enter keys to convert this formula as Array formula.
2. Count Numbers with Leading Zeros using User Defined Function with VBA Code
Step1: press ALT+F11 to open the VBA editor.
Step2: Click on Insert -> Module to create a new module.
Step3: Copy and paste the following code into the module:
Function CountLeadingZeros_ExcelHow(cell As Range) As Long
Dim count As Long
Dim value As String
value = cell.value
count = 0
While Left(value, 1) = "0" And Len(value) > 1
count = count + 1
value = Mid(value, 2)
Wend
If Left(value, 1) = "0" Then count = count + 1
CountLeadingZeros_ExcelHow = count
End Function
The VBA code uses a While loop to count the number of leading zero characters in the specified cell. And it checks whether the leftmost character of the cell’s value is “0” and whether the length of the cell’s value is greater than 1.
If both conditions are true, it increments a count variable and removes the leftmost character from the cell’s value using the Mid function.
Step4: Save the module and go back to your Excel worksheet.
Step5: Type the following formula in a blank cell to count the number of leading zero characters in cell A2.
=CountLeadingZeros_ExcelHow(A2)
Step6: Press Enter to see the result.
3. Video: Count Numbers with Leading Zeros
This video explains how to count numbers with leading zeros in Excel using a formula or VBA code. And it provides step-by-step instructions for both methods and demonstrates how to use them in practice.
4. Related Functions
Excel SUMPRODUCT function The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel COUNTIF function The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel SUM function The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
5. Related Posts
How to insert leading zeros to number or text Assuming that you want to insert leading zeros to number in Cell A2, then you can try to use the TEXT function to create an excel formula. You can also use the CONCATENATE function to add the specific digit of leading zeros into each number…
How to keep or remove leading zeros If you want to keep the leading zeros as you type in a cell, you need to change the cell format to Text before you type the zip code number.You can remove or delete leading zeros in front of the cells with excel VBA macro…
Count the number of words in a cell If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..
Array formulas often need to use “natural number” as parameters of the function, such as the second parameter of the LARGE function, OFFSET function in addition to the first parameter. Through the manual way to enter a constant array will be more trouble, and easy to make mistakes. Then we can use the ROW or COLUMN function in EXCEL to generate a sequence, this method is very convenient and fast.
2. Generating two-dimensional Array from one-dimensional Array
Below we will show you how to construct a new two-dimensional array with two columns of data.
a. One-dimensional range rearrangement to generate two-dimensional array
If there is a list of students and the name column of that list contains 10 students’ names, we need to randomly place the students’ names in the name column into the cell range of 5 rows and 2 columns (a new two-dimensional array).
We can use the following steps to randomize the names of students in column B to a 5 row 2 column range, such as the cell range E1:F5.
STEP1# Select the cell range E1:F5
STEP2# Enter the following array formula in the formula bar
STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.
STEP4# You will see that the students’ names have been randomly placed in a two-dimensional range of array.
Let’s see how this array formula works.
=RANDBETWEEN(A2:A11^0,999)
The RANDBETWEEN function is used to generate an array of 10 values, where the elements are random integers between 1 and 1000. Since the elements are randomly generated, the size of the array elements is randomly ordered. The array formula generates an array of random integers as follows.
={484;203;468;525;702;220;13;163;386;54}
=RANDBETWEEN(A2:A11^0,999)/1%+A2:A11
The random integer array generated above is multiplied by 100 and then added to the ordinal array of 1 to 10. This ensures that the last two digits of the array elements are ordinal numbers 1 to 10.
The ROW function generates a vertical array {1;2;3;4;5), and then subtracts a constant array {1,0} to produce a two-dimensional array of 5 rows and 2 columns.
=SMALL()
The result is taken as the second argument to the SMALL function, which sorts the array after multiplication and addition processing. Since the original size of the array is random, after sorting, the ordinal number corresponding to the last two digits of each element is randomly ordered.
=RIGHT()
The RIGHT function is used to extract the last two digits of each element, and the INDEX function is used to return the student’s name in the corresponding position in column B. In this way, the names in column B can be randomly populated into a two-dimensional array of 5 rows and 2 columns.
b. Combining two columns of data to create a two-dimensional array
We can use the VLOOKUP function to query from the right to the left, and we can use the array operation and IF function to swap two columns of data to generate a new two-dimensional array.
Here is an example of how the VLOOKUP function can be used to reverse the query by constructing a new array.
If you have a table of employee information, and you need to find the employee’s number by the employee’s name, you can use the VLOOKUP function in combination with the IF function to construct a two-dimensional array to find the corresponding employee number.
The employee information table is as follows:
To find the employee number by name, the steps are as follows.
STEP1# Select the cell B3
STEP2# Enter the following formula in the formula bar and press Enter
=VLOOKUP(B2,IF({1,0},E2:E11,D2:D11),2,)
STEP3# As you can see, Jerry’s employee number has been found.
Let’s see how the above formula works.
The core part of the formula is IF ({1,0},E2:E11,D2:D11), which uses a horizontal array {1,0} and two vertical arrays to perform operations to achieve the position of the column where the employee’s name and work number are swapped. Its returned memory arrays are：
The VLOOKUP function then queries the employee’s name in the two-dimensional array generated by the IF function and returns the corresponding employee number.
3. Extracting Sub-arrays From Data
In daily work, it is often necessary to extract part of the data from a column and reprocess it. For example, If you want to find out the list of employees who meet the specified requirements in the employee table.
The following describes how to extract some data from a column to form a subarray.
Suppose you have an employee salary table and you want to find out the names of employees whose salary is greater than $2000. The salary table is as follows.
You can refer to the following steps to obtain a list of employees who meet the requirements.
STEP1# First you need to select the cell range E2:E11
STEP2# Enter the following formula in the formula bar
STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.
Let’s see how the above formula works.
=IF(C2:C11>2000,A2:A11)
First use the IF function to determine whether the salary meets the conditions, if the salary is greater than $2000, then return the employee’s ID, otherwise return the logical value FALSE.
=ROW(INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″)))
The COUNTIF function is used to calculate the number of scores greater than 100 and is combined with the ROW function and INDIRECT function to generate a sequence of natural numbers from 1 to n.
The IF function is used to determine the type of data in the cell range. If the cell value is the text, then let the cell’s line number multiplied by 100, and then add the cell’s column number, and then return a numeric result; if the cell value is not text type, then return the logical value FALSE.
The COUNTIF function is used to calculate the number of text values in the range of cells A1:C10, and combined with the ROW function and INDIRECT function to generate a series of natural numbers from 1 to n.
The TEXT function is used to convert the location information to R1C1 reference style, and then use the INDIRECT function to return to the cell reference.
Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.
5. Fill the Merged Cells by Array Formula
In the merged cells, only the first cell has a value, while the rest of the cells are empty cells. When we work with the data, we may need to fill the empty cells in the merged cells with the corresponding values to meet the needs of the calculation.
The following is a product sales table, we need to fill the empty cells in the merged cells with the corresponding region name. The data table is as follows:
You can fill the data into the merged cells by using the following array formula.
This formula assigns a non-empty cell in column A to the row number of that cell, and returns the error value #DIV/O! for empty cells, and finally returns a memory array.
Finally, the LOOKUP function is used to perform a fuzzy search and return the corresponding region name.
6. Convert Two-dimensional Array to one-dimensional Array
Some functions only support one-dimensional array as their arguments, not two-dimensional array. For example, the second argument of the MATCH function, the second argument of the LOOKUP function, and so on. If you want to complete the query in a two-dimensional array, you need to first convert the two-dimensional array to a one-dimensional array.
In the figure below, the cell range A1:C4 is a two-dimensional array, by using the following formula you can return the maximum value less than or equal to 100 in the cell range; LOOKUP function will perform a fuzzy search from a one-dimensional array returned by the SMALL formula, and return the value that matches the conditions.
The formula is as follows.
=LOOKUP(100,SMALL(A1:C4,ROW(1:12)))
Let’s see how this formula works:
=SMALL(A1:C4,ROW(1:12))
Because the cell range is 4 rows and 3 columns, it is a two-dimensional array containing 12 elements. You can generate a sequence of natural numbers from 1 to 12 by using the ROW function. Then use the SMALL function to sort the two-dimensional array and return a one-dimensional memory array. The result is as follows:
The LOOKUP function performs a fuzzy lookup by row and ignores the error value #NUM!. Finally, the maximum value less than or equal to 100 is returned, which is 99.
If you’re working with data in Excel and notice that there are some missing numbers in a sequence, it can be frustrating to try and figure out where they are. Fortunately, there are a few ways to find these missing numbers quickly and easily.
This post will guide you how to find missing numbers in a sequence with a formula in Excel. How do I identify missing numbers in a consecutive series in Excel. How to find missing serial number in Excel 2013/2016.
This post will guide you through two methods to find missing numbers in a sequence in Excel: using a formula and using VBA code.
1. Find Missing Numbers in a Sequence in Excel
Assuming that you have a serial number list in Column B, and you want to find the missing number in this sequence list. How to achieve it.
Type this array formula into a blank cell, and then press Ctrl + Shift + Enter keys in your keyboard.
The missing numbers are listed in cells.
Note: this formula will check the given sequence from 1 to 20 if there are missing numbers. If so, returns the missing numbers.
Or you can use another array formula based on the Small function, the IF function, the Countif function and the row function to achieve the same result. Like this:
2. Find Missing Numbers in a Sequence using VBA Code in Excel
To find missing numbers in a sequence in Excel with VBA code, you can follow these steps:
Step1: Press Alt + F11 to open the Visual Basic Editor (VBE) or click Developer > Visual Basic.
Step2: In the VBE window, right-click your workbook and click Insert > Module.
Step3: In the module window, paste the following code:
Sub FindMissingNumbers_ExcelHow()
Dim rng As Range
Dim outRng As Range
Dim i As Long
Dim n As Long
Dim found As Range
' Prompt the user to select the range of data to export
Set rng = Application.InputBox(prompt:="Select the range that contain a sequence list", Type:=8)
Set outRng = Application.InputBox(prompt:="Select one cell as output range", Type:=8)
n = Application.Min(rng) 'get the minimum number in the input range
For i = 1 To Application.Max(rng) - n + 1 '
Set found = rng.Find(n, LookIn:=xlValues, LookAt:=xlWhole)
If found Is Nothing Then
outRng.Value = n
Set outRng = outRng.Offset(1)
End If
n = n + 1
Next i
End Sub
Step4: Press ALT + F8 on your keyboard to open the Macro dialog box. Select the FindMissingNumbers_ExcelHow macro from the list and click the Run button.
Step5: Select one range of cells that contains a sequence to find missing numbers.
Step6: Select one cell as output range to place the missing numbers.
Step7: The missing numbers in the sequence will be listed in the output range.
3. Video: Find Missing Numbers in a Sequence in Excel
This video will demonstrate both a formula and VBA code to help you find missing numbers in a sequence in Excel.
4. Related Functions
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel COUNTIF function The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
In our daily work, we usually have the problem of counting the average of a period. If the given dates are continuous, we can handle this situation very well, and if the dates are discontinuous, how do we count the average for different periods? In this article, we will use some examples to show you how to count the average for two dates by Average formula in different situations.
1. Count the Average Between Continuous Dates
First prepare a table with given dates and sales.
You can see the given dates are continuous, so we can count average by Average formula directly.
Step1: In B7, enter the below formula to count the average between dates 1/28/2019 and 3/28/2019.
2. Count the Average Between Discontinuous Dates using Formula
If in above table the dates are discontinuous and unordered, how can we count the average of sales? See screenshot below.
You can follow below steps to count the average for two discontinuous dates.
Step1: Prepare another table to save the Start Date, End Date and Average.
Step2: In Start Date enter the start date of the period you want to count the average. In End Date enter the end date of the period you want to count the average. For example, we count the average for period 2/28/2019 and 4/28/2019.
3. Count the Average Between Discontinuous Dates using User Defined Function with VBA Code
You can also use a User Defined Function with VBA Code to count the average between two given dates in Excel. Just follow these steps:
Step1: Open the Visual Basic Editor (VBE) by pressing Alt + F11 or going to the Developer tab and clicking on Visual Basic.
Step2: Insert a new module by going to the Insert menu and selecting Module.
Step3: Write your user defined function in the module using the Function keyword and the desired name, parameters and return value. Save and close the VBE.
Function MyAverage_Excelhow(rng1 As Range, rng2 As Range) As Double
Dim sum As Double
Dim count As Long
Dim i As Long
Dim crit1 As Variant
Dim crit2 As Variant
' Check if the ranges have the same size
If rng1.Rows.count <> rng2.Rows.count Or rng1.Columns.count <> rng2.Columns.count Then
MyAverage = CVErr(xlErrValue) ' Return a #VALUE! error
Exit Function
End If
' Get the criteria from cells B10 and B11
crit1 = ActiveSheet.Range("B10").Value
crit2 = ActiveSheet.Range("B11").Value
' Loop through the ranges and calculate the sum and count of the values that meet the criteria
For i = 1 To rng1.Cells.count
If rng1.Cells(i).Value >= crit1 And rng1.Cells(i).Value <= crit2 Then
sum = sum + rng2.Cells(i).Value
count = count + 1
End If
Next i
' Calculate and return the average
MyAverage_Excelhow = sum / count
End Function
Note: you need to change both two criterion variables in cell B10, B11 as you need.
Step4: Go back to Excel and enter a cell where you want to use the function. Type the following formula:
=MyAverage_Excelhow(A2:A7,B2:B7)
Step5: press Enter to apply this formula. And the average result will be returned.
4. Video: Count the Average Between Two Dates
This video will show you how to count the average between two dates in Excel using a simple formula or a VBA code.
5. Related Functions
Excel SUMPRODUCT function The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel AVERAGE function The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
This post will guide you on how to extract the first word from a text string in Excel. Sometimes, you may have a long string of text and only need the first word for analysis or sorting purposes. Fortunately, there is a simple formula that can do this, or you can use VBA code to create a more powerful and flexible solution.
In this post, we will explore both methods so that you can choose the one that best suits your needs.
1. Get First Word using Formula in Excel
If you want to extract the first word from a text string in a cell, you can use a combination of the IF function, the ISERR function, the LEFT function and the FIND function to create a complex excel formula.
You can use the LEFT function to extract the first word, but the problem is that how to get the length of the first word in the text. So you can use the FIND function to get the position of the first space character in the text in a cell, the number is then subtracted by 1 to get the length of the first word.
So Assuming that you want to get the first word in a text string in Cell B1, then you can write down an excel formula as follows:
This formula returns the length of the first word in the text in Cell B1. It goes into the LEFT function as its num_chars argument.
=LEFT(B1, FIND(” “,B1)-1)
This formula extracts the first word in the text in cell B1. The LEFT function extracts the left-most characters and the number of characters that you need to extract is specified by the result returned by the FIND function.
= ISERR(FIND(” “,B1))
The ISERR function will check if the FIND function do not find any space character in the text, it means that it contains only one word or a blank Cell.
If the ISERR function returns TRUE, then returns the value of Cell B1, otherwise, returns the first word of the text in Cell B1.
2. Get First Word using User Defined Function with VBA Code
You can create a User Defined Function in VBA code to extract the first word from a text string in Excel, here are the steps:
Step1: Open the Visual Basic Editor by pressing Alt + F11.
Step2: In the VBA Editor, go to Insert > Module to create a new module.
Step3: In the new module, enter the following code. Save the module and close the VBA Editor.
Function GetFirstWord_Excelhow(str As String) As String
Dim arr() As String
arr = Split(str, " ")
GetFirstWord_Excelhow = arr(0)
End Function
Step4: Go back to your Excel worksheet and enter a text string in a cell.
Step5: In a blank cell, enter the following formula:
=GetFirstWord_Excelhow (B1)
Where B1 is the cell containing the text string.
Step6: Press Enter to see the first word extracted from the text string.
The function splits the input string into an array of words using the space character as the delimiter, and returns the first word in the array using the index 0.
Note: this User Defined Function assumes that the first word is separated from the rest of the string by a space character. If your text string uses a different delimiter, you can modify the Split function accordingly.
3. Video: Get First Word from Text String
This video will show you how to extract the first word from a text string in Excel using either a formula or VBA code.
4. Related Formulas
Split Multiple Lines from a Cell into Rows If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
Extract nth word from text string If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…
Get last word from text string If you want to get the last word from a text string, you can create an excel formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function..…
count specific words in a cell or a range If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…
Extract word that starting with a specific character Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…
5. Related Functions
Excel LEFT function The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel FIND function The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Excel ISERR function The Excel ISERR function returns TRUE if the value is any error value except #N/A.The syntax of the ISERR function is as below:= ISERR (value)…
This post will guide you how to remove trailing commas from cell in Excel. How do I remove comma character at the end of a cell with a formula in Excel 2013/2016.
1. Remove Trailing Commas using Formula
if you want to remove trailing commas from you Excel cells, and you can create a formula to check for a comma at the end of a string and then removes the final comma character. you can use the IF function, the RIGHT function, the LEFT function and the LEN function to remove the commas from each cell, like this:
You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill handle over to the range of cells that you want to apply this formula.
2. Remove Last or Trailing Commas using User Defined Function with VBA Code
You can also use a user defined function with VBA code to remove last or trailing commas in Excel. Here are the steps to create and use such a function:
Step1: Press Alt + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor window, right-click on any sheet name and select Insert > Module.
Step3: In the code window, paste the following VBA code. Press Ctrl + S to save the workbook as a macro-enabled file (.xlsm).
Function RemoveLastComma_Excelhow(rng As Range) As String
Dim s As String
s = rng.Value
If Right(s, 1) = "," Then
s = Left(s, Len(s) - 1)
End If
RemoveLastComma_Excelhow = s
End Function
Step4: Go back to the worksheet where you have the data with commas.
Step5: In an empty cell, enter the formula:
=RemoveLastComma_Excelhow(A1)
Where A1 is the cell that contains the comma-separated values.
Step6: press Enter key and you will see that the last or trailing comma is removed from each cell.
3. Video: Remove Last or Trailing Commas
In this video, you will learn how to use the LEFT and LEN functions to extract the text before the last comma, and how to create a User Defined Function with VBA code that can remove the trailing commas.
4. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel LEFT function The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
Excel RIGHT function The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
Excel LEN function The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
This post will guide you how to get nth match based on one criteria with INDEX and MATCH in Excel. In the previous post, we used one excel formula to get the position of the nth occurrence of a value based on one criteria.
1. Get nth Match with One Criteria using INDEX/MATCH Formula
At this moment, we can also use it in the new formula to extract the nth match value using INDEX function. Like the below array formula:
For example, if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula:
The SMALL function returns the position of the second occurrence of the string text “jenny” in the range B2:B10. If you want to get the third or nth match value in a range, just need to modify the nth in the formula as your need.
The First INDEX function returns bonus value of the second match based on the position number returned by the SMALL function. So it returns $165.
2. Related Formulas
Find nth Occurrence with Multiple Criteria Using INDEX/MATCH If you want to find the nth occurrence with multiple criteria, you can use a combination with the INDEX function, SMALL function, nested IF function and ROW function to create a complex excel formula like this:=INDEX(Array,SMALL(IF(Range1…
Reverse a List or Range If you want to reverse a list or range, you can use a combination of the INDEX function, the COUNTA function, the ROW function or ROWS function to create a new formula. you can use the following formula:=INDEX($A$2:$A$5,COUNTA($A$2:$A$5)-ROWS($C$2:C2)+1)…
Transpose Values Based on the Multiple Lookup Criteria If you want to lookup the value with multiple criteria, and then transpose the last results, you can use the INDEX function with the MATCH function to create a new formula.…
Lookup the Value with Multiple Criteria If you want to lookup the value with multiple criteria in a range, you can use a combination with the INDEX function and MATCH function to create an array formula.…
Lookup the Next Largest Value If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..
3. Related Functions
Excel INDEX function The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel SMALL function The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Excel ROW function The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
This post will guide you how to convert Julian date to a calendar date in Excel. How do I convert a given Julian Date to a calendar date with a formula or VBA Code in Excel.
1. What is Julian Date?
Julian day number is the integer assigned to a whole solar day in the Julian day count starting from noon Universal time, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar), a date at which three multi-year cycles started (which are: Indiction, Solar, and Lunar cycles) and which preceded any dates in recorded history.
2. Convert Julian Date to Calendar date Using Formula
If you want to convert Julian date to a calendar date for your date values, and you can use a formula based on the IF function, the LEFT function and the MOD function. Like this:
Type this formula into cell c2, and press Enter key on your keyboard to apply this formula.
Then go to HOME tab, select Date as Number Format in the Number group.
Keep to select the Cell C2, and drag the AutoFill Handle down to other cells to apply this formula to convert the Julian date format into Calendar date.
3. Convert Julian Date to Calendar Date with VBA Code
You can also use a User Defined function with VBA Code to convert Julian date to a Calendar date in Excel. just do the following steps:
Step1: open the VBA editor in Excel by pressing Alt + F11
Step2: Insert a new module and paste the below code into the module.
Function JulianToCalendar_ExcelHow(julianDate As Double) As Date
Dim year As Integer
year = IIf(Left(julianDate, 2) < 20, 2000, 1900) + Left(julianDate, 2)
JulianToCalendar_ExcelHow = DateSerial(year, 1, 1) + (julianDate Mod 1000) - 1
End Function
Step3: you can use the formula in a blank cell to convert it to a calendar date.
=JulianToCalendar_ExcelHow(B2)
This function takes a Julian date as input (in the format of a double) and returns a calendar date (in the format of a Date object).
4. Convert calendar date to Julian date using Formula
To convert a calendar date to a Julian date in Excel, you can use the following formula:
where C2 is the cell containing the calendar date you want to convert.
Let’s See How this Formula Works:
The TEXT function is used to convert the year portion of the date to a two-digit number in YY format. The formula TEXT(C2,”yy”) extracts the last two digits of the year from the date in cell C2.
The DATEVALUE function is used to convert a text string in the format “1/1/YY” to a date value. The formula DATEVALUE(“1/1/”&TEXT(C2,”yy”)) returns the date value for January 1 of the same year as the date in cell C2.
Subtracting the January 1 date value from the date value in cell C2 gives the number of days between January 1 and the date in cell C2.
Adding1 to this number gives the day of the year for the date in cell C2.
The TEXT function is used again to format the day of the year as a three-digit number with leading zeros. The formula TEXT((C2-DATEVALUE(“1/1/”&TEXT(C2,”yy”))+1),”000″) returns the day of the year in DDD format.
5. Convert calendar date to Julian date with VBA Code
You can create a User Defined function with VBA Code to convert a calendar date to a Julian date in Excel, just do the following steps:
Step1: open the VBA editor in Excel by pressing Alt + F11
Step2: Insert a new module and paste the below code into the module.
Function ConvertToJulianDate_ExcelHow(myDate As Date) As String
Dim myYear As String
Dim julianDay As String
myYear = Format(myDate, "yy")
julianDay = Format(myDate - DateSerial(year(myDate), 1, 1) + 1, "000")
ConvertToJulianDate_ExcelHow = myYear & julianDay
End Function
Step3: You can then call the function from a worksheet cell or from another VBA macro.
For example, to convert the date in cell C2 to a Julian date in cell E2, you could use the formula in cell E2.
=ConvertToJulianDate_ExcelHow(C2)
The function calculates the Julian date by extracting the year from the calendar date using the Formatfunction, and then subtracting the date of January 1 of that year from the calendar date, adding 1 to get the number of days since January 1, and then formatting the result as a 3-digit number using the Format function.
6. Video: Convert between Julian date and Calendar Date in Excel
In this video, you will learn how to convert between Julian dates and calendar dates in Excel using both formulas and VBA code.
7. Related Functions
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
This post will guide you how to find the first value greater than a specified value in a range of cells in Excel. How do I find the last value greater that a given value in a row with a formula in Excel.
Assuming that you have a list of data in range B1:B6, in which contain numeric values. And you want to find the first number from the list that is greater than a given number (30). Or you wish to find the last one number from your data that is greater than a number 30. The below will show you one formula to accomplish it.
1. Find the Last Value Greater Than X Using Formula
If you want to identify the last value in your range B1:B6 that is greater that number 30, you can use the following array formula based on the LOOKUP function and the IF function. Like this:
=LOOKUP(9.999999999E+307,IF(B1:B6>30,B1:B6))
You can type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.
2. Find the Last Value Greater Than X with VBA Code
You can also use a user-defined function (vba code) to find the last value greater than a given value in Excel. You just need to do the following steps:
Step1: Open your Excel workbook and press Alt + F11 to open the Visual Basic Editor.
Step2: In the editor, click on “Insert” and select “Module” from the drop-down menu.
Step3: In the new module, enter the following code, and save the module and return to the Excel worksheet.
Function LastGreaterThanbyExcelhow(R As Range, Val As Variant) As Variant
Dim i As Integer
LastGreaterThanbyExcelhow = ""
For i = R.Cells.Count To 1 Step -1
If R.Cells(i).Value > Val Then
LastGreaterThanbyExcelhow = R.Cells(i).Value
Exit Function
End If
Next i
End Function
Step4: In a blank cell, enter the formula:
=LastGreaterThanbyExcelhow(A1:A6, 30)
Where A1:A6 is the range of cells you want to search for the last value greater than the given value and “30” is the given value.
Step5: Press Enter to calculate the function and display the result.
Note: The function will return an empty string (“”) if no value is found greater than the given value.
3. Find the First Value Greater Than X Using Formula
If you want to find the first value in a range that is greater that number 30, you can use the following formula based on the INDEX function and the MATCH function to identify the first value in the range B1:B6 greater that number 30. Like this:
=INDEX(B1:B6,MATCH(TRUE,INDEX(B1:B6>30,0),))
You just need to type the above formula into a blank cell in your worksheet, and make sure to press Ctrl +shift +Enter keys on your keyboard to change your formula as array formula.
4. Find the First Value Greater Than X with VBA Code
You can also use a user-defined function in Excel to find the first value greater than a given value in a range of cells. Here is the VBA Code:
Function FindFirstGreaterThanExcelhow(TargetValue As Double, RangeToSearch As Range) As Double
Dim cell As Range
For Each cell In RangeToSearch
If cell.Value > TargetValue Then
FindFirstGreaterThanExcelhow = cell.Value
Exit Function
End If
Next cell
FindFirstGreaterThanExcelhow = CVErr(xlErrValue)
End Function
In a cell, enter the formula:
=FindFirstGreaterThanExcelhow(30, A1:A6)
Where 30 is the value you want to search for and A1:A6 is the range of cells to search.
Press Enter to execute the formula and the function will return the first value greater than the target value in the specified range.
5. Video: Find the Last or First Value in a Range Greater Than X in Excel
This video will demonstrate how to find the last or first value in a range greater than X in Excel using a formula or a User Defined Function (UDF).
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel INDEX function The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel MATCH function The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
This post will guide you how to remove duplicates and replace with blank cells in Excel 2013/2016. How do I replace the duplicates with blank cells using a formula in Excel.
1. Replace Duplicates with Blank Cells Using Formula
Assuming that you have a list of data in range B1:C4, and you want to remove duplicate values in this range of cells and replace it with blank cells. How to achieve it. And if you just use the Remove Duplicates command, it will remove all duplicate rows only. So you can use a formula to remove duplicates and replace it with blank cell based on the IF function, and the COUNTIF function.
Just like this:
=IF(B1="","",IF(COUNTIF($B1:B4,B1)=1,B1,""))
Type this formula into a blank cell, such as: D1 and press Enter key in your keyboard, and then drag the AutoFill Handle down to Cell F4 to apply this formula.
2. Replace Duplicates with Blank Cells using Remove Duplicates
You can replace duplicates with blank cells in Excel by using Remove Duplicates feature. Just do the following steps:
Step1: Select the range of cells containing duplicates that you want to replace with blank cells.
Step2: Click on the “Data” tab in the Excel ribbon. Click on the “Remove Duplicates” button in the “Data Tools” group.
Step3: In the “Remove Duplicates” dialog box, make sure that all columns are selected.
Step4: Click “OK” to remove the duplicates.
3. Replace Duplicates with Blank Cells with VBA Code
You can also use the VBA code to replace duplicate with blank cells in Excel. it can use the Application.InputBox function to select a range and a destination cell. Just do the following steps:
Step1: Press Alt + F11 to open the Visual Basic Editor.
Step2: In the Visual Basic Editor, select Insert > Module to create a new module.
Step3: Copy and paste the VBA code provided into the new module. Save the workbook as a macro-enabled workbook with a .xlsm extension.
Sub ReplaceDuplicatesWithOne_excelhow()
Dim sourceRange As Range
Dim destinationCell As Range
Dim cell As Range
Dim values As Variant
Dim i As Long, j As Long
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
'Select the range to search for duplicates
Set sourceRange = Application.InputBox(prompt:="Select range to search for duplicates", Type:=8)
'Select the destination cell to place the result
Set destinationCell = Application.InputBox(prompt:="Select destination cell for the result", Type:=8)
'Copy the values of the selected range to an array
values = sourceRange.Value
'Loop through the array and add unique values to a dictionary, replacing duplicates with blank cells
For i = 1 To UBound(values, 1)
For j = 1 To UBound(values, 2)
If dict.Exists(values(i, j)) Then
values(i, j) = ""
Else
dict.Add values(i, j), ""
End If
Next j
Next i
'Paste the result to the destination cell
destinationCell.Resize(UBound(values, 1), UBound(values, 2)).Value = values
End Sub
Step4: Press Alt + F8 to open the Macro dialog box. Select the macro you want to run from the list of macros and click the Run button.
Step5: select the range to search for duplicates.
Step6: select one destination cell to place the result.
Step7: The VBA code will then execute and replace duplicates with blank cells or keep one copy of each duplicate value. The result will be placed in the selected destination cell.
4. Show Only Duplicate Values
If you want to replace all unique values with blank cells and only show duplicates values in Excel, you can use another formula based on the IF function and COUNTIF function. Like this:
=IF(B1="","",IF(COUNTIF($B1:B4,B1)>1,B1,""))
Type this formula into a blank cell and then drag the AutoFill Handle down to other cells to apply this formula.
Let’s see the result:
5. Show Only Unique Values
If you want only to show the unique values and remove all duplicate values and replace with blank cells in Excel, you can use the following formula based on the IF and COUNTIF function.
This video will guide you through the steps on how to replace duplicates with blank cells in Excel using built-in Excel functions or VBA code.
7. Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…
Excel COUNTIF function The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
If you frequently work with large sets of data in Excel, you may have encountered the issue of dealing with blank cells when performing calculations. The presence of blank cells in a formula can often cause errors or affect the accuracy of your results. To avoid this problem, it’s important to know how to ignore blank cells in a formula in Excel.
This post will guide you how to ignore blank cells in a formula in Excel. How do I ignore blank cells when performing calculations in a formula in Excel.
1. Ignore Blank Cells in a Formula Using IF Function
If you have a formula to calculate a range of cells, and you want to ignore the blank cells. And you can use the IF function, theISNUMBER function, the AND or OR function to achieve the result.
For example, you want to add 1 to all cells in range D2:D7, and if you directly use the formula =D2+1, and the blank cells also will be added 1. So if you want to ignore all blank cells in this formula, you need to use IF function to check if it is a blank cell, if TRUE, then ignore it. Otherwise, apply this formula. Just use the following formula:
=IF(D2<>"",D2+1, "")
2. Ignore Blank Cells Using ISNUMBER and IF Functions
You can also use the IF function in combination with the ISNUMBER function to achieve the same result. Like this:
=IF(ISNUMBER(D2),D2+1, "")
3. Video: Ignore Blank Cells in a Formula
This video will demonstrate how to ignore blank cells in a formula in Excel, allowing you to perform calculations without affecting the accuracy of your results.
4. Related Functions
Excel ISNUMBER function The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…
This post will guide you on how to show only positive values in Excel. There are several ways to accomplish this, including formatting cells, using conditional formatting, and using VBA code.
We will discuss each method in detail and provide step-by-step instructions on how to implement them.
1. Video: Show Only Positive Values
In this video, we will explore various methods, such as formatting cells, using conditional formatting, and VBA code, to show only positive values in Excel.
2. Show Only Positive Numbers in a Range with Format Cells
To only display positive numbers in a range using the Format Cells feature in Microsoft Excel, follow these steps:
Step1: Select one range that you want to format.
Step2: Right-click and select “Format Cells” from the context menu.
Step3: In the Format Cells dialog box, select the “Number” tab. Select “Custom” from the Category list. In the “Type” field, enter the following format code: 0;[White0 , Click “OK” to apply the formatting.
Step3: only positive numbers in the selected range will be displayed.
Note: this formatting will not change the underlying values in the cells, only their appearance.
3. Show Only Positive Numbers in a Range with Conditional Formatting
If you only want to highlight or format positive numbers in a range, you can also use the Conditional Formatting feature in Microsoft Excel, just do the following steps:
Step1: Select one range that want to format.
Step2: Click on the “Conditional Formatting” button in the “Home” tab of the ribbon. Select “HighlightCellRules” and then “LessThan” from the dropdown menu.
Step3: In the “Less Than” dialog box, enter “0” in the “Value” field. Choose “Custom Format…”, then set the font color as white.
Step3: Click “OK” to apply the formatting.
This will only show all cells in the selected range that contain a value greater than zero.
4. Show Only Positive Numbers in a Range with VBA Code
If you want to show positive numbers in a range using VBA code, you can use a loop to iterate through each cell in the range and check if the value is greater than zero. If the value is positive, you can keep it in the cell. Otherwise, you can set font color as white.
Just refer to the following steps:
Step1: Press Alt + F11 on your keyboard. This will open the Visual Basic Editor.
Step2: Go to the menu bar at the top of the Visual Basic Editor and click on Insert -> Module. This will create a new module in the project.
Step3: Copy the following VBA code you want to run and paste it into the module. Save the VBA code and close the Visual Basic Editor.
Sub ShowPositiveNumbers_excelhow()
Dim rng As Range
Dim cell As Range
On Error Resume Next ' Handle cancel button click in InputBox
Set rng = Application.InputBox(prompt:="Select a range of cells", Type:=8)
On Error GoTo 0 ' Restore default error handling
If Not rng Is Nothing Then ' Check if a range was selected
For Each cell In rng
If cell.Value < 0 Then
cell.Font.Color = RGB(255, 255, 255)
End If
Next cell
End If
End Sub
Step4: Press Alt + F8 to open the Macros dialog box. Select the macro you want to run and click on the Run button.
Step5: select a range of cells that you want to filter all positive values.
Step5: You can see that only positive values are shown in your selected range of cells.
5. Show Only Positive Values after Calculating
Assuming that you have a list of data, and you want to sum the range of cells A1:C5 and if the summation result is greater than 0 or is a positive number, then display this result. Otherwise, display as a blank cell.
You can create a formula based on the IF function, and the SUM function to sum all values in the range A1:C5 and just show only positive values. Like this:
=IF(SUM(A1:C1)<0, "",SUM(A1:C1))
Type this formula into the formula box of the cell D1, then drag AutoFill Handler over other cells to apply this formula.
You will see that the returned results of this formula only show positive values.
6. Related Functions
Excel SUM function The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed in the correct format, which is very common in daily work. When we are faced with a string of unformatted phone numbers, we find it very confusing and difficult to see the layout of the phone number. Therefore, it is very important for us to learn how to format phone numbers in Excel worksheets and Google worksheets.
In today’s tutorial, we will introduce how to format phone numbers through Excel built-in cell format function, Excel function or formula. These functions can be applied directly to Microsoft Excel worksheet and Google sheet to format phone numbers. We can also customize the formatting in excel worksheets and google worksheets to format different phone number formats depend on different countries.
In addition to how to format phone numbers, we also introduce the phone number formats of some countries, including USA, UK, France, China, Brazil, etc. Each country offers two phone number formats depending on the international or domestic call you are dialing from. We also list the ways to format phone numbers for these countries.
Format Phone Numbers to Built-in Excel Format
Let’s start with the built-in “Format Cells” function in Excel to convert number format to phone number format.
In excel, there is a self-contained phone number format. This feature is available in Excel worksheets, Google worksheets. In worksheet, there is a special format in “Format Cells” options to format phone numbers according to the position of the numbers. The layout of phone numbers may vary from country to country in the number of digits and symbols, such as the placement of parentheses and hyphens. Here are the specific steps to perform this operation.
Below steps are operated on Excel 2016 for Mac.
STEP 1# Select the cell or a list that needs to be formatted.
STEP 2# In the Home tab (Home tab is displayed on the top in the ribbon), select the Format drop-down menu, then select “Format cells…“. You can also just right-click on your selection and select “Format Cells…” from the pop-up menu.
STEP 3# In the pop-up “Format Cells” dialog box, select the fist “Number” tab, click “Special” from the Category list, then you can see a list of formatting types on the right side of the dialog box.
STEP 4# Select the “Phone Number” option from the list. Then you can see that your phone number is formatted correctly in Sample field. In our example, 2134567890 is formatted to (213)456-7890.
STEP 5# Click “OK” to apply this format to all phone numbers in the selected range.
It should be noted that the format of the cell can not be text, this function does not work well for text. If there is a leading “0”, we need to remove this “0” first, and then use custom formatting to add “0” back. Let’s look at the following example of custom formatting.
Format Phone Numbers to A Custom Format
If you do not find a preset format that meets your requirements in Excel worksheet or Google worksheet, you can set up a custom format via “Format Cell” function to display the numbers as phone numbers. Again, we still use the “Cell Format” setting to apply custom format to the numbers. Here are the steps for formatting a phone number using custom format.
STEP 1# Select the cell or a list that needs to be formatted.
STEP 2# In the Home tab (Home tab is displayed on the top in the ribbon), select the Format drop-down menu, then select “Format cells…“. You can also just right-click on your selection and select “Format Cells…” from the pop-up menu.
STEP 3# In the pop-up “Format Cells” dialog box, select the fist “Number” tab, click “Custom” from the Category list, then you can see a list of formatting types on the right side of the dialog box. Some types are preset; some are saved in daily work per your demand.
STEP 4# You can select a built-in format that is most similar to the one you want to create. If there is not a similar one, you can type a proper phone number format you want in “Type” textbox. Then custom phone number format is reflected in Sample field. Note that all symbols entered, including spaces, will be reflected in the phone number format.
Here we enter (###) ###-#### to reflect phone number. Each # represents a number.
STEP 5# Click “OK” to apply this format to all phone numbers in the selected range.
Custom format helps us format phone numbers without restrictions. Using a good concatenation symbol, we can enter any phone number format we want. We can also enter zero and plus sign directly into the custome phone number format.
Phone Numbers with Preceding “+” Sign or Leading “0”
You can add leading zero in parentheses:
Enter (0###) ###-#### in Type:
You can add leading zero outside the parentheses:
Enter 0 (###) ###-#### in Type:
You can add +1 in front of all phone numbers:
Enter (+1) (###) ###-#### in Type:
Using the types listed above we can add a leading 0 or country code (or area code) to a phone number if the original phone number does not have a leading 0 or code.
Format Phone Number with Excel TEXT Function
The text function can quickly handle phone numbers with the same format.
In the cell just next to phone number, enter =TEXT(A1,”(###) ###-####”).
Format a phone number table
In my example, the phone numbers are saved in a table, so when you enter theTEXT function in B2, the value parameter is the phone numbers table, and when you press ENTER to run TEXT, a new table is created.
Format phone numbers with different formats (different lengths)
If phone numbers of different lengths are mixed together, the TEXT function alone cannot help us format the phone numbers, so we need the help of the IF function. We can first sort the phone numbers by region or length of the phone number, and then use the IF and TEXT functions to format the phone numbers. Note that this formula can only format two types of phone numbers.
For example, the length of the phone number is different. Some contain 11 digits and some contain 10 digits. For phone number with 11 digits, format text to “(###) # ###-####”, for phone number with 10 digits, format text to “####-###-###”.
List of International Phone Number Format Standards
International standards for phone number formats are issued by the International Telecommunication Union. Each country and region has its own way of writing phone numbers. Even for the same phone number, the format of the phone number is different for domestic calls and international calls.
A standard phone number should be “+ (country code) (area code) phone number”. For example, +(1)(425) 555-0100 is the phone number for the U.S. If you are calling a U.S. phone number, their phone number starts with +1, followed by the correct 10 digits of the phone number (the first three are the area code). The other countries follow the similar rules.
When dialing an international call, you need to add 001 (the U.S. international call area code) plus the U.S. local area code in front of phone number or cell phone number.
For example, in the dialing screen, type 001 860 xxxx xxxx, you can call Connecticut from abroad. Here 00 is the international call access code, the leading 0 is usually ignored in writing.
US Phone Number Format
International Call +1 860 XXX XXXX
Domestic Call +0860 XXX XXXX
(The prefix code 860 is the area code for Connecticut, USA)
UK Phone Number Format
International Call +44 20 XXXX XXXX
Domestic Call +020 XXXX XXXX
(The prefix code 20 is the area code for London, UK)
Australia Phone Number Format
International Call +61 2 XXXX XXXX
Domestic Call +02 XXXX XXXX
(The prefix code 2 is the area code for Sydney, Australia)
People’s Republic of China Phone Number Format
International Call +86 10 XXXX XXXX
Domestic Call +010 XXXX XXXX
(The prefix code 10 is the area code for Beijing, RPC)
Japan Phone Number Format
International Call +81 3 XXXX XXXX
Domestic Call +03 XXXX XXXX
(The prefix code 3 is the area code for Tokyo, Japan)
South Korea Phone Number Format
International Call +82 2 XXX XXXX
Domestic Call +02 XXX XXXX
(The prefix code 2 is the area code for Seoul, South Korea)
India Phone Number Format
International Call +91 80 XXXX XXXX
Domestic Call +080 XXXX XXXX
(The prefix code 80 is the area code for Bangalore, India)
Related Functions
Excel IF function The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel Text function The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.
We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.
Cell Contains Specific Text in Excel & Google Sheets
Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.
If you want to check if a given cell contains a specific text, then you can use theSEARCH function in combination with theISNUMBER function to create a nested formula. You can refer to this general formula below.
In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.
If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.
If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.
For the example in this article, you can use the following formula to find specific text.
From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.
Let’s see how this formula works:
When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.
For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.
Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets
You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.
If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.
As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.
If Cell Contains Specific Text Then in Excel & Google Sheets
If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.
You can build an IF nested formula by nesting the above formula into the IF function as follows.
Note: Because this formula uses the SEARCH formula, the result is case-insensitive.
Sum If Cell Contains Text in Google sheets & Excel
If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.
=SUMIFS(sum_range, criteria_range,specific_text)
If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.
As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.
Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.
Conditional Formatting If Cell Contains in Google Sheets & Excel
In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.
STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.
STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.
STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.
STEP 4: You will see that all cells that contain the expense string are automatically highlighted.
In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.
STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting
STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.
STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box
STEP 4: Select a highlight color in Formatting style and click the Done button.
STEP 5: Let’s see the final result.
Related Functions
Excel ISNUMBER function The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel SUM function The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Excel SEARCH function The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…
Excel Find function The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND (find_text, within_text,[start_num])…
Excel SUMIFS Function The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
Excel Filter function The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
