Count number of characters in a range
This tutorial shows how to count the total number of characters in a range, including spaces, through the use of an Excel formula or VBA
Example: Count number of characters in a range
|
=SUMPRODUCT(LEN(B5:B7))
|
This formula uses a combination of the Excel SUMPRODUCT and LEN functions to return the total number of characters, including spaces, from the selected range. The LEN function is used to return the number of characters from individual cells that are within the selected range and then the SUMPRODUCT function is used to add the number of characters from each cell in the selected range to return the total number of characters.
|
Sub Count_number_of_characters_in_a_range()
'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(B5:B7))"
ws.Range("D5").Formula = "=SUMPRODUCT(LEN(B5:B7))"
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Range Reference: Select the range from which 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 Reference: Select the range from which 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 using VBA with a For Loop
VBA
Sub Count_number_of_characters_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
totalchar = 0
'count the total number of characters in a range by looping through each cell
For x = 5 To 7
totalchar = totalchar + Len(Range("B" & x).Value)
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 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 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.
EXPLANATION
This tutorial shows how to count the total number of characters in a range, including spaces, by using an Excel formula or VBA
This tutorial provides one Excel method that uses a combination of the Excel SUMPRODUCT and LEN functions to calculate the total number of characters, including spaces, from a selected range.
This tutorial provides two VBA methods used to return a total number of characters, including 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 method uses the Len function to count the number of characters in a cell and then loops through each of the cells in the specified range and sums the total number of characters that it calculated from each cell in a specified range.
FORMULA
=SUMPRODUCT(LEN(range))
=SUMPRODUCT(LEN(range))
ARGUMENTS
range: A range of cells that contain the characters that you want to count.
range: A range of cells that contain the characters that you want to count.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of characters in a range excluding spaces | How to count the total number of characters in a cell, excluding 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 |