Count number of characters in a range excluding spaces
This tutorial shows how to count total number of characters in a range, excluding spaces, through the use of an Excel formula or VBA
Example: Count number of characters in a range excluding spaces
METHOD 1. Count number of characters in a range excluding spaces
EXCEL
|
=SUMPRODUCT(LEN(SUBSTITUTE(B5:B7," ","")))
|
This formula uses a combination of the Excel SUMPRODUCT, LEN and SUBSTITUTE function to return the number of characters, excluding spaces, from a selected range. Cells B5 and B6 both have a single space, however, this formula removes these spaces from its calculation. The SUBSTITUTE function is used inside the LEN function to remove any spaces from a selected cell from which you want to count the total number of characters. The LEN function is then used to return the total number of characters from the selected cell (which had the spaces removed). The SUMPRODUCT function is then used to add the number of characters from each cell in the selected range to return the total number of characters. In this example there are a total of 12 characters in range (B5:B7), excluding spaces. If spaces weren't excluded from the calculation, the formula would have returned a value of 14.
|
Sub Count_number_of_characters_in_a_range_excluding_spaces()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'count the total number of characters in a range
ws.Range("D5").Formula = "=SUMPRODUCT(LEN(SUBSTITUTE(B5:B7,"" "","""")))"
ws.Range("D5").Formula = "=SUMPRODUCT(LEN(SUBSTITUTE(B5:B7,"" "","""")))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from where you want to count the number of characters by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from where you want to count the number of characters by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
METHOD 2. Count number of characters in a range excluding spaces using VBA with a For Loop
VBA
Sub Count_number_of_characters_in_a_range_excluding_spaces()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
totalchar = 0
'count the total number of characters, excluding spaces, in a range by looping through each cell
For x = 5 To 7
totalchar = totalchar + Len(Application.WorksheetFunction.Substitute(Range("B" & x), " ", ""))
Next x
ws.Range("D5") = totalchar
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count the total number of characters, excluding spaces, by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 7. Therefore, the range that this code will loop through will be ("B5:B7"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count the total number of characters, excluding spaces, by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 7. Therefore, the range that this code will loop through will be ("B5:B7"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
METHOD 3. Count number of characters in a range excluding spaces using VBA with a For Loop
VBA
Sub Count_number_of_characters_in_a_range_excluding_spaces()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B5:B7")
Set Rng = ws.Range("B5:B7")
totalchar = 0
'count the total number of characters, excluding spaces, in a range by looping through each cell
For Each Cell In Rng
totalchar = totalchar + Len(Application.WorksheetFunction.Substitute(Cell, " ", ""))
Next
ws.Range("D5") = totalchar
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count the number of characters, excluding spaces, by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range: Select the range from which you want to count the number of characters, excluding spaces, by changing the range reference ("B5:B7") in the VBA code.
Worksheet Selection: Select the worksheet which contains the range of cells from which to count the total number of characters by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Explanation about the formula used to count number of characters in a range excluding spaces
EXPLANATION
EXPLANATION
This tutorial shows how to count total number of characters in a range, excluding spaces, using an Excel formula and VBA.
This tutorial provides one Excel method that uses a combination of the Excel SUMPRODUCT, SUBSTITUTE and LEN functions to calculate the total number of characters, excluding spaces, from a selected range.
This tutorial provides three VBA methods used to return a total number of characters, excluding spaces, from a selected range. The first method uses the VBA formula function and the exact same formula that is provided in the Excel method. The second and third methods are very similar, as they use the Substitute function with the (" ", "") criteria to remove any spaces is a cell and then the Len function to count the number of characters in a cell. It will perform this calculation for each cell in a specified range by looping through cell and summing the total number of characters that it calculated from each cell in the specified range. The only difference between method two and three is that in method two you specify separately the column and row references to loop through, whilst in method three you specify the entire range reference to loop through.
FORMULA
=SUMPRODUCT(LEN(SUBSTITUTE(range," ","")))
=SUMPRODUCT(LEN(SUBSTITUTE(range," ","")))
ARGUMENTS
range: A range from which you want to count the number of characters, excluding spaces.
range: A range from which you want to count the number of characters, excluding spaces.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of characters in a range | How to count the total number of characters in a range, including spaces, using Excel and VBA methods | |
| Count numeric characters in a cell | How to count the total number of numeric characters in a cell using Excel and VBA methods | |
| Count numeric characters in a range | How to count the total number of numeric characters in a range using Excel and VBA methods | |
| Count number of specific characters in a range | How to count the total number of specific characters in a range using Excel and VBA methods | |
| Count number of characters in a cell excluding spaces | How to count the total number of characters in a cell, excluding spaces, using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| LEN Function | The Excel LEN function returns the number of characters in a specified string | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string |