Count number of characters in a cell excluding spaces
This tutorial shows how to count the total number of characters in a cell, excluding spaces through the use of an Excel formula or VBA
Example: Count number of characters in a cell excluding spaces
|
=LEN(SUBSTITUTE(B5," ",""))
|
This formula uses a combination of Excel LEN and SUBSTITUTE functions to return a total number of characters, excluding spaces, from a selected cell. 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). In this example we are returning the number of characters in cell B5, which comprises of eight characters, excluding the space.
|
Sub Count_number_of_characters_in_a_cell_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 cell, excluding spaces
ws.Range("C5") = Len(Application.WorksheetFunction.Substitute(ws.Range("B5"), " ", ""))
ws.Range("C5") = Len(Application.WorksheetFunction.Substitute(ws.Range("B5"), " ", ""))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell Reference: Select the cell that holds the characters that you want to count by changing the cell reference ("B5") in the VBA code.
Worksheet Name: Select the worksheet which contains the cell from which to count 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 ("C5") in the VBA code.
Cell Reference: Select the cell that holds the characters that you want to count by changing the cell reference ("B5") in the VBA code.
Worksheet Name: Select the worksheet which contains the cell from which to count 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 cell excluding spaces
EXPLANATION
EXPLANATION
This tutorial shows how to count the number of characters, excluding spaces, in a cell by using of an Excel formula or VBA.
Both the Excel formula and VBA make use of the LEN and SUBSTITUTE functions to count the number of characters in a cell, excluding spaces. The SUBSTITUTE function is used to replace any spaces in the selected cell with no spaces through the use of (" ","") Substitute criteria. The LEN function is then used to return the total number of characters from the selected cell (which had all the spaces removed).
FORMULA
=LEN(SUBSTITUTE(text," ",""))
=LEN(SUBSTITUTE(text," ",""))
ARGUMENTS
text: The string from which to extract the characters.
text: The string from which to extract the characters.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count number of columns in a range | How to count the total number of columns in a range using Excel and VBA methods | |
| Count number of rows in a range | How to count the total number of rows in a range 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 number of cells in a range | How to count the total number of cells in a range using Excel and VBA methods | |
| Count number of characters in a cell | How to count the total number of characters in a cell, including 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 | |
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string |