How to Get Another Cell Value If One Cell Equals in Excel

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 can use the following formula:

=IF(A1=B1,C1,"")

You need to enter the above formula into a blank cell and then drag it down to other cells to apply this formula.

get another cell value if equals1

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$”.

get another cell value if equals2

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.

get another cell value if equals3

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$”.

get another cell value if equals4

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

Excel Vlookup Return True or False

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:

=IF(ISNA(VLOOKUP(A1,$C$1:$C$2,1,FALSE)), "FALSE", "TRUE")

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.

excel vlookup return true1

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

How to Average Only Positive or Negative Numbers of a Range

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.

=IF(logical_test,[value_if_true],[value_if_false])

1. Average Only Positive Numbers of a Range

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.

In cell H2, enter the formula:

=AVERAGE(IF(A2:E4>0,A2:E4,""))

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.

2. Average Only Negative Numbers of a Range

In I2, enter the formula:

=AVERAGE(IF(A2:E4<0,A2:E4,""))

Press Ctrl + Shift + Enter 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],…)….

How to Count Only Unique Values Excluding Duplicates in Excel

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:

Count Only Unique Values Excluding Duplicates 1

And we want to count the total product type in one year, how can we do count?

Count Only Unique Values Excluding Duplicates 2

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:

=SUMPRODUCT(1/COUNTIF(B1:B11,B1:B11))

Where B1:B11 is the range you want to count the unique values.

Count Only Unique Values Excluding Duplicates 3

Step 2: Click Enter and get the result in E2. We can check the result is 6, and the duplicates are not included.

Count Only Unique Values Excluding Duplicates 4

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:

=SUM(IF(FREQUENCY(MATCH(B1:B11,B1:B11,0),ROW(B1:B11)-ROW(B1)+1)=1,1))

Where B1:B11 is the range you want to count the unique values.

Count Only Unique Values Excluding Duplicates 5

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

Count Only Unique Values Excluding Duplicates 6

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],…)…

Calculate Days Open in Excel

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.

calculate days in excel1

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)
calculate days in excel1

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

How To Align Duplicate Values within Two Columns in Excel

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 the IF function, the ISNA function, the MATCH function, and the INDEX function. Like below:

=IF(ISNA(MATCH(A1,$B$1:$B$7,0)),"",INDEX($A$1:$B$7,MATCH(A1,$B$1:$B$7,0),2))

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.

How to align duplicate values within two columns1

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.

How To Align Duplicate Values within Two Columns in Excel vba1.png
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.

How To Align Duplicate Values within Two Columns in Excel vba2.png

Step5: The code will prompt you to select the source range, lookup range, and output range.

How To Align Duplicate Values within Two Columns in Excel vba3.png
How To Align Duplicate Values within Two Columns in Excel vba4.png
How To Align Duplicate Values within Two Columns in Excel vba5.png

Step6: The code will run and populate the output range with the values that match between the source and lookup ranges.

How To Align Duplicate Values within Two Columns in Excel vba6.png

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

How to Count Numbers with Leading Zeros

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.

=SUMPRODUCT(--($A$2:$A$6=A2))

You can enter this formula into Cell B2, then press Enter key. Then you can drag AutoFill Handle down to other cells to apply this formula.

count string with leading zero1

You can also use another excel array formula to count strings with leading zeros as follows:

=SUM(IF($A$2:$A$10=A2,1,0))
count string with leading zero2

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.

How to Count Numbers with Leading Zeros vba 2.png

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

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays.

1. Generate Array with ROW or COLUMN Functions

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.

The following formula produces a vertical array of natural numbers from 1 to 10.

{=ROW (1:10)}
Excel Array Construction1

The following formula produces a horizontal array of natural numbers from 1 to 10.

{=COLUMN(A:J)}
Excel Array Construction1

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

Excel Array Construction1

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

Excel Array Construction

STEP2# Enter the following array formula in the formula bar

=INDEX(B2:B11,RIGHT(SMALL(RANDBETWEEN(A2:A11^0,999)/1%+A2:A11,ROW(1:5)*2-{1,0}),2))
Excel Array Construction

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Excel Array Construction

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.

={484;203;468;525;702;220;13;163;386;54}/1%+A2:A11

The above array formula returns the following array:

{48401;20302;46803;52504;70205;22006;1307;16308;38609;5410}

=ROW(1:5)*2-{1,0}

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.

Excel Array Construction

=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:

Excel Array Construction

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,)
Excel Array Construction

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:

{"Tom",1;"Jerry",2;"Jacey",3;"Wendy",4;"William",5;"Ocean",6;"scott",7;"charies",8;"ceila",9;"nicole",10}
Excel Array Construction

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.

Excel Array Construction

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

Excel Array Construction

STEP2# Enter the following formula in the formula bar

=T(OFFSET(B1,SMALL(IF(C2:C11>2000,A2:A11),ROW(INDIRECT("1:"&COUNTIF(C2:C11,">2000")))),))
Excel Array Construction

STEP3# Press CTRL + SHIFT +ENTER shortcut keys to convert the above formula into an array formula.

Excel Array Construction

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.

Excel Array Construction

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

Excel Array Construction

=SMALL(IF(C2:C11>2000,A2:A11),ROW( INDIRECT(“1:”&COUNTIF(C2:C11,”>2000″))))

Use the SMALL function to find the employee number whose salary is greater than $2000 and return the following memory array.

={1;3;5;10}
Excel Array Construction

=OFFSET(B1,{1;3;5;10},)

The OFFSET function extracts the employee’s name from the result returned by the SMALL function and returns the following array of employee names.

={"Tom";"Jacey";"William";"nicole"}
Excel Array Construction

=T(OFFSET(B1,{1;3;5;10},))

Finally, the T function is used to convert the multi-dimensional reference returned by the OFFSET function into a memory array.

Excel Array Construction

4. Extracting Sub-array from a two-dimensional Array

The cell range A1:C10 contains data of text and numeric type, see the figure below.

Excel Array Construction

If you want to extract all the text-based data from the specified cell range A1:C10, then you can use the following array formula.

=T(INDIRECT(TEXT(SMALL(IF(A1:C10>="",ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT("1:"&COUNTIF(A1:C10,"*")))), "r0c00"),))
Excel Array Construction

Let’s see how the above formula works.

=IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10))

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.

={FALSE,102,FALSE;FALSE,202,FALSE;FALSE,302,FALSE;FALSE,402,FALSE;FALSE,502,FALSE;FALSE,602,FALSE;FALSE,702,FALSE;FALSE,802,FALSE;FALSE,902,FALSE;FALSE,1002,FALSE}
Excel Array Construction

=ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”)))

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.

={1;2;3;4;5;6;7;8;9;10}
Excel Array Construction

=SMALL(IF(A1:C10>=””,ROW(A1:C10)/1%+COLUMN( A1:C10)),ROW(INDIRECT(“1:”&COUNTIF(A1:C10,”*”))))

The SMALL function is used to extract the position information of the cell where the text is located and return a memory array.

={102;202;302;402;502;602;702;802;902;1002}
Excel Array Construction

=INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),)

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.

Excel Array Construction

=T(INDIRECT(TEXT({102;202;302;402;502;602;702;802;902;1002}, “r0c00”),))

Finally, the multi-dimensional references returned by the INDIRECT function are converted to memory arrays using the T function.

Excel Array Construction

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:

Excel Array Construction

You can fill the data into the merged cells by using the following array formula.

=LOOKUP(ROW(A2:A13),ROW(A2:A13)/(A2:A13>""),A2:A13)
Excel Array Construction

Let’s See How This Formula Works:

=ROW(A2:A13)/(A2:A13>””)

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.

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;#DIV/0!;10;#DIV/0!;#DIV/0!;#DIV/0!}
Excel Array Construction

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)))
Excel Array Construction

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:

={16;60;78.6;79;97;97;99;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}
Excel Array Construction

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.

Find Missing Numbers in a Sequence in Excel

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.

You can use an excel array formula based on the SMALL function, the IF function, the ISNA function, the MATCH function, and the ROW function. Like this:

=SMALL(IF(ISNA(MATCH(ROW(B$1:B$20),B$1:B$20,0)),ROW(B$1:B$20)),ROW(B1))

Type this array formula into a blank cell, and then press Ctrl + Shift + Enter keys in your keyboard.

find missing number1

The missing numbers are listed in cells.

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:

=SMALL(IF(COUNTIF($B$1:$B$10,ROW($1:$20))=0,ROW($1:$20),""),ROW(B1))
find missing number2

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.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBE window, right-click your workbook and click Insert > Module.

Find Missing Numbers in a Sequence in Excel vba 1.png

Step3: In the module window, paste the following code:

Find Missing Numbers in a Sequence in Excel vba 2.png
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.

Find Missing Numbers in a Sequence in Excel vba 3.png

Step5: Select one range of cells that contains a sequence to find missing numbers.

Find Missing Numbers in a Sequence in Excel vba 4.png

Step6: Select one cell as output range to place the missing numbers.

Find Missing Numbers in a Sequence in Excel vba 5.png

Step7: The missing numbers in the sequence will be listed in the output range.

Find Missing Numbers in a Sequence in Excel vba 6.png

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

How to Count the Average Between Two Dates in Excel

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.

Count the Average Between Two Dates 1

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.

=AVERAGE(B2:B4)
Count the Average Between Two Dates 2

Step2: Click Enter to get the result.

Count the Average Between Two Dates 3

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.

Count the Average Between Two Dates 4

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.

Count the Average Between Two Dates 5

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.

Count the Average Between Two Dates 6

Step3: In Average, enter the formula:

=AVERAGE(IF((A2:A7>=B10)*(A2:A7<=B11),B2:B7))
Count the Average Between Two Dates 7

In above formula, A2:A7 is the given date range, B10 is the start date, B11 is the end date, B2:B7 is the sales range.

Step4: After entering the formula, click Ctrl + Shift + Enter to get the result.

Count the Average Between Two Dates 8

Check the formula bar, you can find the formula is displayed as below. That’s an array formula.

Count the Average Between Two Dates 9

Notes:

1. In step 4, if we just click Enter after entering the formula, then we will get an error here. That’s because above formula is an array formula.

Count the Average Between Two Dates 10

2. If we just want to use a normal formula to count the average and click Enter to get the result, we can use below formula:

 =SUMPRODUCT(--(A2:A7>=B10),--(A2:A7<=B11),B2:B7)/SUMPRODUCT(--(A2:A7>=B10),--(A2:A7<=B11))

We can get the same result.

Count the Average Between Two Dates 11

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.

Adding Comma Character at End of Cells vba1.png

Step2: Insert a new module by going to the Insert menu and selecting Module.

Adding Comma Character at End of Cells vba1.png

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.

How to Count the Average Between Two Dates in Excel vba1.png
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

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.

How to Count the Average Between Two Dates in Excel vba2.png

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],…)….

How to get first word from text string

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:

=IF(ISERR(FIND(" ",B1)),B1, LEFT(B1, FIND(" ",B1)-1))

Let’s see how this formula works:

=FIND(” “,B1)-1

get first word1

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)

get first word1

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

get first word3

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(ISERR(FIND(” “,B1)),B1, LEFT(B1, FIND(” “,B1)-1))

get first word4

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.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA Editor, go to Insert > Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: In the new module, enter the following code. Save the module and close the VBA Editor.

How to get first word from text string vba 1.png
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.

How to get first word from text string vba 2.png

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.

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

How to Remove Last or Trailing Commas in Excel

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:

=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

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.

remove last commas1

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.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor window, right-click on any sheet name and select Insert > Module.

Adding Comma Character at End of Cells vba1.png

Step3: In the code window, paste the following VBA code. Press Ctrl + S to save the workbook as a macro-enabled file (.xlsm).

How to Remove Last or Trailing Commas in Excel vba1.png
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.

How to Remove Last or Trailing Commas in Excel vba2.png

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

How to get nth Match with One Criteria using INDEX/MATCH

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:

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

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:

=INDEX(D2:D10, SMALL(IF(B2:B10="jenny", ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))

Let’s see how this formula works

= SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2)

get nth match with one criteria1

The detailed description for this formula, please continue reading: get the position of nth occurrence of a value in column

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.

=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))

get nth match with one criteria1

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

How to Convert Julian Date to a Calendar date in Excel

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:

=("1/1/"&(IF(LEFT(B2,2)*1<20,2000,1900)+LEFT(B2,2)))+MOD(B2,1000)-1

Type this formula into cell c2, and press Enter key on your keyboard to apply this formula.

convert julian date to calendar date1

Then go to HOME tab, select Date as Number Format in the Number group.

convert julian date to calendar date2

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.

convert julian date to calendar date3

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.

How to Convert Julian Date to a Calendar date vba 3.png
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)
How to Convert Julian Date to a Calendar date vba 4.png

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:

=TEXT(C2,"yy")&TEXT((C2-DATEVALUE("1/1/"&TEXT(C2,"yy"))+1),"000")

where C2 is the cell containing the calendar date you want to convert.

How to Convert Julian Date to a Calendar date 10.png

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.
  • Adding 1 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.

How to Convert Julian Date to a Calendar date vba 1.png
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)
How to Convert Julian Date to a Calendar date vba 2.png

The function calculates the Julian date by extracting the year from the calendar date using the Format function, 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)…

How to Find the Last or First Value in a Range Greater Than X in Excel

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.

find last value greater thatn x1.png

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.

find last value greater thatn x vba1.png
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.

find last value greater thatn x vba2.png
find last value greater thatn x vba3.png

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.

find last value greater thatn x2.png

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:

find first value greater thatn x vba1.png
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.

find first value greater thatn x vba2.png

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

6. Related Functions

  • Excel LOOKUP function
    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])….

How to Replace Duplicates with Blank Cells in Excel

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.

replace duplicates with blanks1

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.

How to Replace Duplicates with Blank Cells in Excel 10.png

Step2: Click on the “Data” tab in the Excel ribbon. Click on the “Remove Duplicates” button in the “Data Tools” group.

How to Replace Duplicates with Blank Cells in Excel 11.png

Step3: In the “Remove Duplicates” dialog box, make sure that all columns are selected.

How to Replace Duplicates with Blank Cells in Excel 12.png

Step4: Click “OK” to remove the duplicates.

How to Replace Duplicates with Blank Cells in Excel 13.png

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.

VBA to Replace Duplicates with Blank Cells in Excel 1.png
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.

VBA to Replace Duplicates with Blank Cells in Excel 2.png

Step5: select the range to search for duplicates.

VBA to Replace Duplicates with Blank Cells in Excel 3.png

Step6: select one destination cell to place the result.

VBA to Replace Duplicates with Blank Cells in Excel 4.png

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.

VBA to Replace Duplicates with Blank Cells in Excel 5.png

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:

replace duplicates with blanks2

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.

=IF(B1="", "", IF(COUNTIF(B$1:B$4,B1)>1,"",B1))
replace duplicates with blanks3

6. Video: Replace Duplicates with Blank Cells

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

How to Ignore Blank Cells in a Formula in Excel

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, the ISNUMBER 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, "")
ignore blank cells in a formula1
ignore blank cells in a formula2

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, "")
ignore blank cells in a formula3

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])…

Show Only Positive Values

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.

Show Only Positive Values 10.png

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.

Show Only Positive Values 11.png

Step3: only positive numbers in the selected range will be displayed.

Show Only Positive Values 12.png

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 “Highlight Cell Rules” and then “Less Than” from the dropdown menu.

Show Only Positive Values 13.png

Step3: In the “Less Than” dialog box, enter “0” in the “Value” field. Choose “Custom Format…”, then set the font color as white.

Show Only Positive Values 14.png

Step3: Click “OK” to apply the formatting.

Show Only Positive Values 12.png

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.

vba to Show Only Positive Values 1.png
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.

vba to Show Only Positive Values 2.png

Step5:  select a range of cells that you want to filter all positive values.

vba to Show Only Positive Values 3.png

Step5:  You can see that only positive values are shown in your selected range of cells.

Show Only Positive Values 12.png

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.

show only positve values1

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

Phone Number Format in Excel

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.

 Phone Number Format in Excel

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.

 Phone Number Format in Excel

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.

 Phone Number Format in Excel

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.

 Phone Number Format in Excel

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.

 Phone Number Format in Excel

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:

 Phone Number Format in Excel

You can add leading zero outside the parentheses:

Enter 0 (###) ###-#### in Type:

 Phone Number Format in Excel

You can add +1 in front of all phone numbers:

Enter (+1) (###) ###-#### in Type:

 Phone Number Format in Excel

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.

TEXT function syntax:

=TEXT(value, format_text)

Format a phone number

In the cell just next to phone number, enter =TEXT(A1,”(###) ###-####”).

 Phone Number Format in Excel

Format a phone number table

In my example, the phone numbers are saved in a table, so when you enter the TEXT function in B2, the value parameter is the phone numbers table, and when you press ENTER to run TEXT, a new table is created.

 Phone Number Format in Excel

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 “####-###-###”.

=TEXT([@[Phone Number]],IF(LEN([@[Phone Number]])<11,"####-###-###","(###) # ###-####"))
 Phone Number Format in Excel

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

If Cell Contain Specific Text

Cell contains specific tex

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 the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

=ISNUMBER(SEARCH(specific_text,text))

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.

=ISNUMBER(SEARCH(B4,A4))
Cell contains 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.

=ISNUMBER(FIND(B4,A4))
Cell contains specific text

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.

=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")
Cell contains specific text

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.

=SUMIFS(B4:B12,A4:A12, "*expen*")
Cell contains specific text

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))
Cell contains specific text

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.

Cell contains specific text

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.

=SEARCH(“expense”,A4)
Cell contains specific text

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

Cell contains specific text

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

Cell contains specific text

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

Cell contains specific text

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.

Cell contains specific text

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

Cell contains specific text

STEP 4: Select a highlight color in Formatting style and click the Done button. 

Cell contains specific text

STEP 5: Let’s see the final result.

Cell contains specific text

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])…