How to Split Cells by the First Space in Texts in Excel

Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any way to split one cell to only two cells by the first space in the texts? In this free tutorial, we will share you some useful functions in excel to split cells per your demand.

Precondition:

In product list we use space to separate product properties.

How to Split Cells by the First Space in Texts in Excel1

Now we want split it to two parts in two different columns by the first space. See expectation below:

How to Split Cells by the First Space in Texts in Excel2

1. Split Cells to Two Columns by the First Space in Texts by Formula

Step1: In B2 enter the formula:

=LEFT(A2,FIND(" ",A2)-1)
How to Split Cells by the First Space in Texts in Excel3

Step2: Click Enter to get result. Verify that text before the first space is extracted and saved in B2 properly.

How to Split Cells by the First Space in Texts in Excel4

Step3: Drag the fill handle down till the end of the list. Verify that texts from the first space are displayed properly.

How to Split Cells by the First Space in Texts in Excel5

Step4: In C2 enter the below formula to extract the left texts after the first space.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))
How to Split Cells by the First Space in Texts in Excel6

Step5: Click Enter to get result. Verify that texts are displayed properly.

How to Split Cells by the First Space in Texts in Excel7

Step6: Drag the fill handle down till the end of the list. Now texts are split to two columns by the first space properly.

How to Split Cells by the First Space in Texts in Excel8

2. Split Cells to Two Columns by the First Space in Texts with VBA Code

You can use a VBA Code that prompts the user to select a range of cells and a destination cell, and then splits the cells in the selected range to two columns based on the first space in the text.

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, go to “Insert” on the top menu and select “Module“.

Step3: Paste the below VBA code into the new module.

Split Cells to Two Columns by the First Space in Texts vba 1.png
Sub SplitCellsByFirstSpace_ExcelHow()
    ' Prompt the user to select a range of cells
    Dim selectedRange As Range
    Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
    
    ' Prompt the user to select a destination cell
    Dim destinationCell As Range
    Set destinationCell = Application.InputBox("Select a destination cell:", Type:=8)
    
    ' Insert the formula to split the text at the first space
    For Each cell In selectedRange
        cell.Offset(0, 2).Formula = "=RIGHT(" & cell.Address(False, False) & ",LEN(" & cell.Address(False, False) & ")-FIND("" "", " & cell.Address(False, False) & ",1))"
        cell.Offset(0, 1).Formula = "=LEFT(" & cell.Address(False, False) & ",FIND("" "", " & cell.Address(False, False) & ",1)-1)"
    Next cell
    
    ' Copy the formula to the destination cell and adjacent cell
    selectedRange.Offset(0, 1).Resize(selectedRange.Rows.Count, 2).Copy destinationCell
    
    ' Convert the formula to values in the destination cells
    destinationCell.Resize(selectedRange.Rows.Count, 2).Value = destinationCell.Resize(selectedRange.Rows.Count, 2).Value
End Sub

Step4: Press F5 to run the macro or click the “Run” button on the toolbar.

Step5: A dialog box will appear asking you to select a range of cells. Click and drag your mouse to select the range of cells you want to split.

Split Cells to Two Columns by the First Space in Texts vba 2.png

Step6: Another dialog box will appear asking you to select a destination cell. Click on the cell where you want to place the split data.

Split Cells to Two Columns by the First Space in Texts vba 3.png

Step7: The macro will split the data in the selected range into two columns based on the first space in the text, and place the split data into the destination cell and adjacent cells.

Split Cells to Two Columns by the First Space in Texts vba 4.png

3. Video: Split Cells to Two Columns by the First Space in Texts

This video will demonstrate how to split cells to two columns by the first space in texts using Excel formulas and VBA code.

4. 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 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 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 Filter Cells Starts with Number or Letter in Excel

This post will guide you how to filter values starts with number or letter in one single cell in Excel. How do I filter in a list those that begin with number or letter using a formula in Excel.

1. Filter Cells Starts with Number or Letter

Assuming that you have a list of data in range B1:B4 which contain text string values. Ad you want to filter those cells values which are starting from a number or letter. The below steps will show you how to filter the data which starts with numbers or letters only.

Step1: select one blank cells in the adjacent Cell B1. And enter the following formula based on the ISNUMBER function and the LETF function.

=ISNUMBER(LEFT(B1)*1)

Step2: press Enter key to apply this formula.

filter cells starts with number1

Step3: then drag the AutoFill Handle in Cell C1 down to other cells to apply the same formula. You would see that if the data starts with a number, then returns TRUE. Otherwise, returns FALSE.

filter cells starts with number2

Step4: select the cell C1 in the helper column, go to Data tab in the Excel Ribbon, and click Filter button under Sort & Filter group. And one Filter icon will be added into the Cell C1.

filter cells starts with number3

Step5: click filter icon in the Cell C1, and check True to filter out all data that starts with number. Click Ok button.

filter cells starts with number4

Step6: let’s see the last result:

2. Video: Filter Cells Starts with Number or Letter in Excel

This video will guide you through using a formula with the ISNUMBER function and Sort & Filter feature to effectively filter cells that start with a number or letter in Excel.

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

How to Calculate Average by Month

To calculate average with given criteria we can apply AVERAGIFS function. If the criteria or condition is a period for example one or more months, we can provide month information and then calculate average based on the given month. There are some date and time functions to return months or dates can represent a month. In this article, we will show you how to calculate average by month with AVERAGEIFS function, we use logical expressions with EOMONTH function to represent month.

In this article, we will introduce you the syntax, arguments and basic usage of above-mentioned functions, and use them to build a formula, and let you know the calculation steps of the formula.

1. EXAMPLE

How to Calculate Average by Month 1

Refer to the left-hand side table, we can see some kinds of fruits are listed in “Fruit” column. Numbers in “Amount” column represent the sales on different dates. Dates belong to different months are recorded in “Date” column. In the right-hand side table, we want to calculate the average of amounts based on given months. In Month column, E2 and E3 are actually dates. For the following calculation we enter dates 05/20/2021 and 06/20/2021 into E2 and E3, and they are the earliest date shown in “Dates” column for May and June. But for looking clearly, we just show them in mmm-yyyy format and hide the date part.

How to Calculate Average by Month 2

To calculate the average of numbers based on given criteria, we need to apply AVERAGEIFS function here. In this case, our criteria is a whole month not a given period with start date and end date, so we can enter “DATE(2021,5,1)” as start date and “DATE(2021,5,31)” as end date. We can also apply EOMONTH function to return the last date of the given month.

Before creating a formula to calculate average value, we can name range references.

Select range B2:B13, in name box enter “Dates”, then press Enter.

Select range C2:C13, in name box enter “Amounts”, then press Enter.

How to Calculate Average by Month 3
How to Calculate Average by Month 4

2. CREATE A FORMULA with AVERAGEIFS & EOMONTH FUNCTIONS

Step1: In F2, enter the below formula:

=AVERAGEIFS(Amounts,Dates,">="&E2,Dates,"<="&EOMONTH(E2,0))
How to Calculate Average by Month 5

You can enter named range like “Amounts” into your formula directly, or you can also select the range by dragging the handle as well.

Step2: Press Enter after typing the formula.

How to Calculate Average by Month 6

Pick up amounts from May, they are 5000 in C2, 4000 in C3, 6000 in C6, 5500 in C7, 8500 in C10, and 9000 in C11. Total 6 numbers. The average is (5000+4000+6000+5500+8500+9000)/6=6333.33. The formula works correctly.

3. FUNCTION INTRODUCTION

AVERGAEIFS function can be seen as AVERAGE+IFS. It returns the average of some numbers in range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

For AVERAGEIFS functions, it can handle wildcards like asterisk ‘*’ and question mark ‘?’; it also supports logical operations like ‘>=’,’<=’. If we need entering wildcards or logical operators to build criteria, they should be enclosed into double quotes (““).

EOMONTH function returns the last date for a given date.

Syntax:

 =EOMONTH(start_date, months)

Argument months is a serial number, n months represents n months before or after the start date. For example, if A1 is 05/20/2021, we enter =EOMONTH(A2, 1), it returns 06/30/2021 (set cell in proper date format). If we enter =EOMONTH(A2, -1) (negative value in months), it returns 04/30/2021. If we enter =EOMONTH(A2, 0), it returns 05/31/2021.

4. Formula EXPLANATION

AVERAGEIFS – average range

Named range “Amounts” is the average range in this case.

In the formula bar, select “Amounts”, press F9, values in this range are expanded in an array.

How to Calculate Average by Month 7

AVERAGEIFS – criteria range1/criteria range2

Named range “Dates” is the criteria range. We have only one criteria range in this case.

In the formula bar, select “Dates”, press F9, numbers which represent corresponding dates in this range are expanded in an array.

How to Calculate Average by Month 8

AVERAGEIFS – criteria range1

The first criteria is “>=”&E2. Date 5/20/2021 is recorded in E2, in logical expression “>=”&E2, E2 is converted to a five digits number which represents the date 5/20/2021. To concentrate operator “>=” and E2, we use “&” to connect them.

In the formula bar, select “>=”&E2, press F9, number 44336 is displayed instead of the date in E2.

How to Calculate Average by Month 9

AVERAGEIFS – criteria range2

The second criteria is “<=”&EOMONTH(E2,0). EOMONTH function returns the last day of one month which is decided by the entered date. For E2 date is 5/20/2021, the second argument months is 0, so EOMONTH() returns the last day of May, it is 05/31/2021.

In the formula bar, select “<=”&EOMONTH(E2,0), press F9, number 44336 is displayed instead of the date 05/31/2021.

How to Calculate Average by Month 10

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=AVERAGEIFS({5000;4000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500},{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369},">=44336",{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369},"<=44347")

Now for the criteria range, we keep the numbers which can meet our two conditions “>=44336” and “<=44347”. So only numbers marked in bold meet our conditions.

{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369}

For the qualified numbers, we mark “True” in the array, otherwise, “False” will be recorded. Then the original criteria range only contains “True” and “False”.

{True;True;False;Flase;True;True;False;Flase;True;True;False;Flase}

In logical expression, “True” is coerced to “1” and “False” is coerced to “0”. So, values in the array are converted to numbers:

{1;1;0;0;1;1;0;0;1;1;0;0}

After above steps, in current calculation step, we have two arrays.

{5000;4000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500}

– average range

{1;1;0;0;1;1;0;0;1;1;0;0}

– reference

Some numbers in average range are excluded in next calculation step if number’s corresponding value in reference range is 0. So only below numbers are kept and participate in next calculation step “calculate average”.

{5000;4000;0;0;6000;5500;0;0;8500;9000;0;0}

Calculate average of these number:

(5000+4000+6000+5500+8500+9000)/6=6333.33

5. Related Functions

  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

How to Get Row Number From a Vlookup in Excel

This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel.

Assuming that you have a list of data in range A1:B5, you can use the VLOOKUP function to lookup a value in the first column and then return the corresponding cell value in the same row. But now I do not want to get the cell value, and I want to get the row number from a VLookup in Excel. How to accomplish it.

1. Get Row Number From a Vlookup

If you want to return a row number from a Vlookup, you can use a formula based on the MATCH function. For example, you want to lookup a string value “excel” in range A1:A5 and return the row number where the searching value is found. Like this:

=MATCH("excel",A2:A5,0)

You need to type this formula into a desired cell and press Enter key to apply this formula. You would see that the row number returned.

get row number from vlookup1

Note: the above formula only returns a relative row number, and if you want to get a absolute row number, you can use another formula based on the ROW function and the MATCH function, like this:

=MATCH("excel",A1:A5,0) + ROW(A1:A5)-1

Type this formula into a blank cell and press Enter key to apply it.

get row number from vlookup2

2. Video: Get Row Number from a VLOOKUP in Excel

This video will show you how to use the MATCH function to get a relative row number and how to combine it with the ROW function to get an absolute row number from a VLOOKUP in Excel.

3. Related Functions

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

How to Count Spaces before the Text String

This post will guide you how to count the number of spaces before the text string in a cell in Excel. How to count the leading spaces before the text string in one cell with formula in excel. How do I create an excel formula that counts the number of spaces before the text string.

1. Count number of spaces before a string using a Formula

Assuming that you have lots of cells contain text string with different levels of indentation on them. You want to count the number of spaces before each test string in cells, and then you want to know how to count the number of spaces before the text string in each cell.

You can create an excel formula based on the FIND function, the LEFT function, and the TRIM function.

For example, let’s count the number of spaces before text string in Cell B1, you can write down the following excel formula:

=FIND(LEFT(TRIM(B1),1),B1)-1
count spaces before text1

Let’s see how this formula works:

The TRIM function can be used to remove all spaces at the start or end of the text string, then you can get a text string without any spaces. The returned result will pass into the LEFT function to get the leftmost characters of the result without spaces.

Then use the FIND function to search for that characters in the original string, and it returns the position of the first character of the searching characters in the original string. The result is subtracted 1 to get the number of spaces before the text string in Cell B1.

When you use this formula, you do not need to care how many other spaces there are or where they are.

If there are no spaces at the end of the text string in each cell, then you also can use the following formula:

=LEN(B1)- LEN(TRIM(B1))
count spaces before text2

2. Count Spaces before the Text String using User Defined Function with VBA Code

You can create a user-defined function in Excel using VBA (Visual Basic for Applications) to count the number of spaces before a text string. Just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: Click Insert > Module to insert a new module.

Step3: Type the following code into the module:

How to Count Spaces before the Text String vba 1.png
Function CountSpaces_Excelhow(str As String) As Integer
    Dim i As Integer
    For i = 1 To Len(str)
        If Mid(str, i, 1) <> " " Then
            Exit For
        End If
    Next i
    CountSpaces_Excelhow = i - 1
End Function

Step4: Enter the formula into a cell, then press Enter key to apply it.

=CountSpaces_Excelhow(B1)
How to Count Spaces before the Text String vba 2.png

3. Video: Count Spaces before the Text String

This video will show you how to count spaces before the text string in Excel using a formula or VBA code.

4. Related Formulas

  • Get first word from text string
    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..…
  • 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..…
  • 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..…
  • 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.…

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 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 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)…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

How to Concatenate Text based on unique values in Another Column in Excel

This post will guide you how to concatenate text values based on unique values in another column in Excel. How do I concatenate cells based on specific criteria in Excel.

1. Concatenate Text Based on unique Values in Another Column

Assuming that you have a list of data in range A1:B6, in which contain product IDs and product Names. And you want to concatenate product names based on unique ID values (There are duplicated ID values in Column A), How to do it. You need to extract unique product IDs in another range, and then concatenating text values based on newly created range with a User Defined Function. Here are the steps:

Step1: you can use an Excel Array formula based on the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to extract the unique product ID values.

=IFERROR(INDEX($A$2:$A$6, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$6), 0)),"")

Type this formula into cell C2, and press Ctrl + Shift + Enter keys on your keyboard to change it as array formula.  And then drag the AutoFill Handle down to other cells until getting blank cells.

concatenate text based on special criteria1

Step2: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step3: then the “Visual Basic Editor” window will appear.

Step4: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step5: paste the below VBA code (code from here) into the code window. Then clicking “Save” button.

concatenate text based on special criteria2
Function Combinerows(CriteriaRng As Range, Criteria As Variant, _
ConcatenateRng As Range, Optional Delimeter As String = " , ") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRng.Count <> ConcatenateRng.Count Then
        Combinerows = CVErr(xlErrRef)
        Exit Function
    End If

    For i = 1 To CriteriaRng.Count
       If CriteriaRng.Cells(i).Value = Criteria Then
           strResult = strResult & Delimeter & ConcatenateRng.Cells(i).Value
       End If
       Next i

       If strResult <> "" Then
           strResult = Mid(strResult, Len(Delimeter) + 1)
       End If

     Combinerows = strResult
     Exit Function
     ErrHandler:
     Combinerows = CVErr(xlErrValue)
End Function

Step6: back to the current worksheet, then type the following formula in a blank cell, and then press Enter key.

=combinerows(A2:A6,C2,B2:B6)

Step7: drag the AutoFill handle over other cells to  concatenate text based on unique product ID values.

concatenate text based on special criteria3

2. Video: Concatenate Text based on unique values in Another Column

This video will show how to use a formula in combination with a User defined function with VBA code to concatenate text based on unique values in another column in Excel.

3. Related Functions

  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
  • 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 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])….

How to Remove the First/Last Word from Text string in Cell

This post will guide you how to remove the first and the last word from a text string in cells using a formula or User defined function with VBA code in Excel 2013/2016/2019/365. How do I use a formula to remove first and last word of a text string in Excel.

1. Remove the First Word from Text String using Formula

If you want to remove the first word from a text string in cells in Excel, you can use a formula based on the RIGHT function, the LEN function and the FIND function. Like this:

=RIGHT(B1,LEN(B1)-FIND(" ",B1))

Type this formula into a blank cell and press Enter key on your keyboard and then drag the AutoFill Handle down to other cells to apply this formula.

remove first last word in cell1

2. Remove the Last Word from Text String using Formula

If you want to remove the last word from a text string, you can use a formula based on the LEFT function, the TRIM function, the FIND function, and the SUBSTITUTE function. Like this:

=LEFT(TRIM(B1),FIND("~",SUBSTITUTE(B1," ","~",LEN(TRIM(B1))-LEN(SUBSTITUTE(TRIM(B1)," ",""))))-1)

Type this formula into a blank cell and press Enter key on your keyboard and then drag the AutoFill Handle down to other cells to apply this formula.

remove first last word in cell2

3. Remove the First Word from Text String using User Defined Function with VBA Code

You can create a User Defined Function in VBA to remove the first word from a text string in Excel by following these steps:

Step1: Open the Visual Basic Editor (VBE) by pressing Alt+F11.

Step2: In the VBE, click on Insert -> Module to create a new module.

Step3: In the module, enter the following code:

How to Remove the FirstLast Word from Text string in Cell vba 1.png
Function RemoveFirstWord_ExcelHow(ByVal inputString As String) As String
    Dim firstSpaceIndex As Integer
    firstSpaceIndex = InStr(1, inputString, " ")
    If firstSpaceIndex > 0 Then
        RemoveFirstWord_ExcelHow = Mid(inputString, firstSpaceIndex + 1, Len(inputString))
    Else
        RemoveFirstWord_ExcelHow = ""
    End If
 
End Function

Step4: Save the module and return to the Excel workbook.

Step5: In a blank cell, enter the following formula:

=RemoveFirstWord(B1)

Where B1 is the cell that contains the text string you want to remove the first word from.

Step6: Press Enter to display the result.

How to Remove the FirstLast Word from Text string in Cell vba 2.png

4. Remove the Last Word from Text String using User Defined Function with VBA Code

If you also want to remove the last word from a text string in excel using a User Defined Function with VBA code, and you can refer to the above steps, and just using the following code:

How to Remove the FirstLast Word from Text string in Cell vba 2.png
Function RemoveLastWord_ExcelHow(ByVal inputString As String) As String
    Dim lastSpaceIndex As Integer
    lastSpaceIndex = InStrRev(inputString, " ")
    If lastSpaceIndex > 0 Then
        RemoveLastWord_ExcelHow = Left(inputString, lastSpaceIndex - 1)
    Else
        RemoveLastWord_ExcelHow = ""
    End If
End Function

In Cell E1, type the following formula, press Enter key to apply it:

=RemoveLastWord_ExcelHow(B1)
How to Remove the FirstLast Word from Text string in Cell vba 4.png

5. Video: Remove the First/Last Word from Text string in Cell

This video will demonstrate how to remove the first/last word from a text string using a formula and VBA code.

6. 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 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 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 Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • 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)…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

How to Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This post will guide you how to find the largest value in a column and then return the adjacent cell value with a formula in Excel. How do I find the max value and return the cell value from the left adjacent column in Excel.

1. Find the Largest Value and Return the Adjacent Cell Value

Assuming that you have a list of data in range A1:B5, and you want to find the largest value from the Sales column and then return the adjacent cell value in product column. How to do it. You can use a formula based on the VLOOKUP function and the MAX function to achieve the result. Like this:

=VLOOKUP(MAX(A2:A5), A2:B5, 2, FALSE)

Type this formula into a blank cell and press Enter key on your keyboard.

find largest value return adjacent cell value1

You should see that the adjacent cell value is extracted.

The A2:B5 is the data range that you want to use in the VLOOKUP function.  And the Number 2 is the column number that the matched value is returned, it should be the product column number.

2. Find the Max Value and Return Value from Left Adjacent Cell

If you want to find the max value in Sales column and return value from the left adjacent cell in your worksheet, you need to use the INDEX function to create a formula in combination with the MATCH function and the MAX function. Like this:

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

Type this formula into a blank cell and press Enter key on your keyboard.

find largest value return adjacent cell value2

3. Video: Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel

This video will demonstrate how to use a formula to find the largest value in a column and return the adjacent cell value in Excel.

4. 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 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 MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • 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])….

Convert State Names To Abbreviations

Assume that you got a task to convert the full state’s name into the abbreviations in MS Excel, and for doing this task, you might choose to do it manually, which is an acceptable way only if you don’t have any other way to do it!

But fortunately, there is a way to convert a state’s name into abbreviations, and after reading this article carefully, you would get to know about that way by which you can do this task in a few seconds.

So let’s get straight into it.

abbr state names1

1. Convert State Names To Abbreviations

a. General Formula

The Formula to convert state names to the abbreviations in a matter of seconds is mentioned as follows:

=VLOOKUP(States_Name,states_Data,2,0)

b. Explanation of Syntax

Before getting knowledge about how to use this Formula to get the work done, we first need to understand how each syntax of the Formula contributes to making this Formula worthful:

  • VLOOKUP: The VLOOKUP function retrieves data from a range or table row by row.
  • Parenthesis (): The primary function of this symbol is to group the elements.

Let’s See How This Formula Works

A simple formula based on VLOOKUP may be used to convert complete state names to their two-letter abbreviation. The Formula in B2 in the example is:

=VLOOKUP(A2, states_Data,2,0)
abbr state names1

Where “ states_Data ” refers to the designated E2:F7 range:

This means that this method is based on a database with columns for the full state name and the 2-letter abbreviation. Because we’re using VLOOKUP, we need the whole name in the first column. The table has been dubbed “states_Data” for clarity’s sake.

VLOOKUP is set to retrieve the lookup value from column A. The table array represents the designated range “ states_Data,” The column index is 2 (to extract the abbreviation from the second column). The final option, range lookup, has been set to zero (FALSE).

VLOOKUP finds the matching entry in the “states_Data” database and returns the 2-letter abbreviation.

2. Convert Abbreviations to State Names (Lookup In Reverse)

What if you have an abbreviation of the states and want to search up the complete state name in the sample lookup table? You’ll need to use INDEX and MATCH instead in such a situation. With a lookup value in A2, this Formula will yield the whole state name as shown in the lookup table:

=INDEX(D2:D7,MATCH(A2,E2:E7,0))
abbr state names1

You may use this version to convert a 2-letter abbreviation to a complete state name if you wish to utilize the same-named range “ states_Data.”

=INDEX(INDEX(states_Data,0,1),MATCH(A1,INDEX(states_Data,0,2),0))
abbr state names1

By specifying a row number of zero, we may utilize INDEX to return whole columns. This is an interesting and helpful aspect of the INDEX function: if you specify zero for the row, you receive complete column.

You may convert abbreviations to complete state names using the INDEX and MATCH methods. The INDEX and MATCH functions can help you out to retrieve the complete state names depending on the abbreviations.

3. Video: Convert State Names To Abbreviations

This video will show you how to convert state names to abbreviations in Excel using a formula.

4. Related Functions

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

VLOOKUP Returns zero instead of #NA in Excel

This post will guide you how to VLookup and return zero instead of #N/A in Excel. How do I use VLookup function and return zero instead of #N/A if not found in Excel. How to display zero instead of #N/A when using VLOOKUP in Excel. How to replace #N/A with Zero when VLOOKUP.

1. VLOOKUP Returns zero instead of #N/A

The VLOOKUP function is one of the most useful function to find data in a given range of cells in Excel. And if the VLOOKUP function cannot find the result that it is looking for, and it will display a #N/A error. And if you would like it to display a “0” instead of #N/A. How to achieve it.

Assuming that you have a list of data in range B1:C7 which contain the product names and sales data, and you want to use Vlookup function to lookup the product “outlook” in range B1:C7, and the return the sales value in sales column.

Type the following formula in a blank cell and then press Enter key in your keyboard.

=IFERROR(VLOOKUP("outlook",$B$1:$C$7,2,0),0)
vlookup returns zero intead na1

From the returned result, you can know that the error message #N/A has been replaced with number 0.

Let’s try to look the product “excel ” in range B1:C7, type the following formula in a blank cell:

=IFERROR(VLOOKUP("excel",$B$1:$C$7,2,0),0)
vlookup returns zero intead na2

The sales value for product excel has been extracted from the sales column.

2. Video:VLOOKUP Returns zero instead of #N/A

This video will show you how to modify the VLOOKUP formula to return zero instead of #NA in Excel.

3. Related Functions

  • 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….

How to Convert Days to Years, Months, Days in Excel

This post will guide you how to convert a given day numbers into Years, Months, Days using a formula or a User Defined function with VBA code in Excel 2013/2016.2019/365. How do I convert days to Years, Months, Days with a formula in Excel. How to convert a general numbers to a standard date in Excel.

1. Convert Days to Years, Months, Days using Formula

If you want to convert a given day numbers to Years, Months, Days in your worksheet, you can use an Excel formula based on the DATEDIF function. Like this:

=DATEDIF(0,B1,"y") & " Years " & DATEDIF(0,B1,"ym") &  " Months " & DATEDIF(0,B1,"md") & " Days "

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 other cells to apply this formula.

convert days to years months days1

You should see that all days have been converted to Years, Months, Days format in your worksheet.

2. Convert Days to Years, Months, Days using User Defined Function with VBA Code

You can convert the given number of days in a cell to years, months and days and returns the results as a string using a User Defined Function with VBA code. Just do the following steps:

Step1: Press ALT + F11 to open the VBA editor.

Step2: In the VBA editor, insert a new module by clicking “Insert” > “Module” in the menu bar.

Step3: Copy and paste the below code into the new module.

How to Convert Days to Years, Months, Days in Excel vba1.png
Function ConvertDaysToYMD_ExcelHow(days As Long) As String
    Dim years As Long
    Dim months As Long
    Dim remainder As Long
    
    ' Calculate the years, months, and remaining days
    years = Int(days / 365)
    remainder = days Mod 365
    months = Int(remainder / 30)
    remainder = remainder Mod 30
    
    ' Format the result as a string
    ConvertDaysToYMD_ExcelHow = years & " years, " & months & " months, " & remainder & " days"
End Function

Step4: Return to Excel and enter a formula in a cell that references the ConvertDaysToYMD_ExcelHow function.

=ConvertDaysToYMD_ExcelHow(B1)

Step5: Press Enter to calculate the formula, and the result will be displayed as a string in the cell.

How to Convert Days to Years, Months, Days in Excel vba2.png

3. Convert General Number to Standard Date

Step1: select cells that contain 5-digits numbers

convert days to years months days2

Step2: go to HOME tab, click Number Format box, and then select Short Date from the drop down menu list.

convert days to years months days3
convert days to years months days4

You would notice that all selected numbers have been converted as a standard date format.

4. Video: Convert Days to Years, Months, Days in Excel

This video will show you how to convert days to years, months, and days in Excel using a formula or VBA code.

5. Related Functions

  • Excel DATEDIF function
    The Excel DATEDIF function returns the number of days, months, or years between tow dates.The syntax of the DATEDIF function is as below:=DATEDIF (start_date,end_date,unit)…

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 Count Cells that do not Contain Errors in Excel

This post will guide you how to count the number of cells that do not contain errors within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count the number of cells without any errors using a simple formula in Excel.

1. Count Number of Cells that do Not Contain Errors using Formula

You can use the SUMPRODUCT function in combining with NOT and ISERROR functions to count cells in a specified range of cells that do not contain errors

The below is a generic formula to count the number of cells that do not contain errors:

=SUMPRODUCT(--NOT(ISERR(range)))

Supposing that you wish to count the number of cells in range B1:B6 that do not contain errors, and you can use the following formula:

=SUMPRODUCT(--NOT(ISERR(B1:B6)))
count cell do not contain error1

LET’S SEE THAT HOW THIS FORMULA WORKS:

= NOT(ISERR(B1:B6))

count cell do not contain error2

The NOT and ISERROR functions can be used to identify the cells that do not contain an error. And it returns an array result with TRUE or FALSE values. The TRUE values indicate cells without errors, and FALSE values indicate cells with error values.

The array result is like this:

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}
count cell do not contain error3

= –NOT(ISERR(B1:B6))

count cell do not contain error4

Then you still need to convert the above array result to their numeric values, 1 and 0, so that the SUMPRODUCT function can calculate the sum of products of corresponding numbers. And you can do this with a double negative operator. And the final array result is like this:

{1;1;1;0;1;0}
count cell do not contain error5

The SUMPRODUCT function sums the items in the above array and return the final result.

You can also use the SUM function to count the cell that do not contain errors in range B1:B6. And the generic formula is similar with the above formula. It need to be entered as an array formula through pressing CTRL +SHIFT + Enter to convert it as array formula. The formula looks like this:

{=SUM(--NOT(ISERROR(B1:B6)))}
count cell do not contain error6

2. Count Cells that do not Contain Errors using User Defined Function with VBA Code

If you want to count the number of cells that do not contain errors in a range, you can write a user defined function with VBA code. Here are the steps to create and run the function:

Step1: Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

Step2: Click Insert > Module, and paste the following code in the Module Window.

How to Count Cells that do not Contain Errors in Excel vba1.png
Function CountNonErrors_ExcelHow(rng As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If Not IsError(cell.Value) Then
            count = count + 1
        End If
    Next cell
    CountNonErrors_ExcelHow = count
End Function

Step3: Save and close the window.

Step4: In a blank cell, enter the formula:

=CountNonErrors_ExcelHow(B1:B6)

Where B1:B6 is the range you want to count.

Step5: Press Enter to get the result. This function will return the number of cells that do not contain errors in the specified range.

How to Count Cells that do not Contain Errors in Excel vba2.png

3. Video: Count Cells that do not Contain Errors in Excel

This video will show you how to count cells that do not contain errors in Excel using a simple formula and a VBA code.

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 ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (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 NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

Return Larger or Smaller of Two Values

This post will guide you how to get the larger value from two values in excel. How do I get the smaller value from two values in excel. How to calculate the smallest value or largest value from a range of cells in excel. How to compare two cells and return the one with smaller value or larger value in excel.

Assuming that you want to compare two cells in excel and both cells have a certain value and you want to select the cell with smaller value or larger value with a formula in excel.

1. Return Larger of Two Values

To get the larger value from two values, you need to create a formula based on the MAX function. Just like this:

=MAX(A1,B1)

Type this formula into the formula box of cell C1, then press Enter key in your keyboard. And drag the AutoFill Handler over other cells to apply this formula to get the larger values.

return large or small value1

2. Return Smaller of Two Values

To get the smaller of two values, you can use the MIN function. Just like the below formula:

=MIN(A1,B1)

This formula returns the smallest value in the numbers provided.

Type this formula into the formula box of cell C2, then press Enter key.

return large or small value2

3. Video: Return Larger or Smaller of Two Values

This video will explain how to use MAX or MIN function to return the larger or smaller of two values in the given range of cells in Excel.

4. Related Functions

  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

How to extract text after the second or nth specific character (space or comma)

In the previous post, we talked that how to extract text after the first occurrence of the comma character in excel. And this post explains that how to get a substring after the second or nth occurrence of the comma or space character in excel.

1. Extract Text after The Second or Nth Specific Character

If you want to extract text after the second or nth comma character in a text string in Cell B1, you need firstly to get the position of the second or nth occurrence of the comma character in text.

So you can use the SUBSTITUTE function to replace the second comma character with the hash character, then using the FIND function to look for the first hash character in text returned by substitute function, the returned value is the position of the second occurrence of the comma character in text.

Last, you can use the MID function to extract a substring that you want to extract.

So you can create a formula based on the MID function, the FIND function and the SUBSTITUTE function as follows:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",2))+1,255))

Let’s see how this formula works:

=SUBSTITUTE(B1,”,”,”#”,2)

extract text before second comma1

This function replaces the second comma character with hash character in cell B1, and the returned result goes into the FIND function as its within_text argument.

=FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1

extract text before second comma2

The FIND function will return the position of the first occurrence of the hash character in within_text string returned by the SUBSTITUTE function. Then add 1 to get the starting position after the second occurrence of the comma character in text. And it returns 11. It is fed into the MID function as its start_num argument.

=MID(B1,FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1,255)

extract text after second comma1

The MID function will extracts 255 characters from a text string in Cell B1, starting at the 11character.

=TRIM()

The TRIM function removes all extra space characters and just leave only one between words.

If you want to extract a substring after the third occurrence of the comma character, you can use the following formula:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",3))+1,255))
extract text after second comma2

2. Extract Text after The Second or Nth Specific Character using User Defined Function with VBA

You can use the VBA Split function to split a string into an array of substrings based on a specified delimiter, and then extract the desired substring based on its index in the array.

You can use a user-defined function that extracts the substring after the third occurrence of the comma character, just do the following steps:

Step1: Press the Alt + F11 keys on your keyboard to open the VBA Editor.

Step2: In the VBA Editor, insert a new module by clicking on “Insert” in the top menu and then selecting “Module“.

Step3: Copy the VBA code that you want to run and paste it into the new module.

How to extract text after the second or nth specific character vba1.png

Step4: Save the module by clicking on “File” in the top menu and then selecting “Save“.

Step5: in a blank cell, type the following formula:

=ExtractSubstring_ExcelHow(B1)

Step6: Press Enter to apply the formula and see the result in the given cell.

How to extract text after the second or nth specific character vba2.png

The function should now be applied to the selected cell and the output should be the desired substring after the third occurrence of the comma character.

3. Video: Extract Text after The Second or Nth Specific Character

This video will show you how to extract a substring after the third occurrence of the comma character in Excel using both a formula and VBA code.

4. Related Formulas

  • Extract Text between Parentheses
    If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
  • Extract Text between Brackets
    If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
  • Extract Text between Commas
    To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function…..
  • 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 ….
  • Extract text before first comma or space
    If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….
  • Extract text after first comma or space
    If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function ….
  • Extract text before the second or nth specific character
    you can create a formula based on the LEFT function, the FIND function and the SUBSTITUTE function to Extract text before the second or nth specific character…

5. Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • 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 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 Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

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

How to Count or Sum Cells with Checked Box in Excel

This post will guide you how to count the number of checked checkboxes in your worksheet in Excel. How do I Count or sum all cells with checked boxes with a formula in Excel.

Assuming that you have a list of data in range A1:C5, in which contain check boxes, product names and sale numbers. And you wish to count the number of all checked boxed in each row in your range. Or you want to sum all sales with checked checkboxes in your range, how to accomplish it. This post will show you one method to sum or count checked checkboxes with the following steps.

1. Count or Sum Cells with Checked Box

Step1: you need to select each checked box one by one, and then choose Format Control from the context menu. And the Format Control dialog will open.

count or sum cells with checked box1

Step2: click the Control tab in the Format Control dialog box, and type the cell address in the Cell link text box to link the control to a cell places the value True if the box is checked in the linked cell.

count or sum cells with checked box2

Step3: you need to repeat the above two steps to set a linked cell for other checked boxes.

count or sum cells with checked box3

Step4: then you can insert a formula based on the COUNTIF function in the blank cell that you want to place the last result, such as: Cell E1. Type the following formula into the Cell E1 to Count the number of cells with Checked box in your range A1:C5:

=COUNTIF(D2:D5,TRUE)

Then press Enter key on your keyboard to apply this formula. You would see that the number of Cells with checked checkboxes is calculated.

count or sum cells with checked box4

Step5: if you want to sum all sales values with checked checkbox in your range, you can use another formula based on the SUMPRODUCT function to accomplish it. Like this:

=SUMPRODUCT(($D$2:$D$5=TRUE)*$C$2:$C$5)

Then you need to type this formula in the desired cell, press Enter key on your keyboard to apply it. You would see that the sum of sales value with checked checkboxes is calculated.

count or sum cells with checked box5

2. Video: Count or Sum Cells with Checked Box in Excel

In this video, you will learn how to use the COUNTIF and SUMPRODUCT formulas to count or sum cells with checked boxes in Excel.

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