Counting Spaces?

[dabluebery]

Does anyone know of an Excel function, or more specifically, a series of excel functions, that can be used to count spaces, or any character or string I specify.

I'm not really interested in using VBA to accomplish this, though I understand I could easily solve my problem this way. I want to exhaust the inherent excel functions first.

Thanks,

Rob

 

[Tetra201]

Is there a formula to count the number of spaces between each word in a string?

Is this what you need?

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

 

[hiker95]

mlo356,

The following is based on the string structure that you have posted.

How about something like this?

Excel 2007
	A	B
1	What a cool formula	3
2	What a cool formula	6
3	What a cool formula	4
4
Sheet1

Cell Formulas
Range	Formula
B1	=LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))

The formula in cell B1, copied down:

=LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))

 

[mlo356]

mlo356,

The following is based on the string structure that you have posted.

How about something like this?

Excel 2007

	A	B
1	What a cool formula	3
2	What a cool formula	6
3	What a cool formula	4
4

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =LEN(A1)-LEN(SUBSTITUTE(A1, " ", "")) <tbody> </tbody>

<tbody>
</tbody>

The formula in cell B1, copied down:

=LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))

Thanks! This is very helpful. Instead of a total number of spaces, I am looking for the individual counts of spaces between each text. For example:

	A	B	C	D
1		Spaces Between What & A	Spaces Between A & Cool	Spaces Between Cool & Formula
2	What A Cool Formula	2	2	3

<tbody>
</tbody>

Can the formula be modified to do this?

Thanks!

 

[FDibbins]

To do that, 1st extract the text you want, using MID (and another MID), then use the substitute on that

 

[mlo356]

To do that, 1st extract the text you want, using MID (and another MID), then use the substitute on that

Hi. The problem is, I don't know how many words will be in each string and the spaces between each words will vary. It will vary each row. so I am looking to identify where there are 2 "pieces" of text and count the number of spaces in between those 2 text.

 

[Aladin Akyurek]

Hi. The problem is, I don't know how many words will be in each string and the spaces between each words will vary. It will vary each row. so I am looking to identify where there are 2 "pieces" of text and count the number of spaces in between those 2 text.

Computing on text is better done as a whole in VBA. The following is also possible which also recruit a generic UDF...

In B2 control+shift+enter, not just enter, and copy across:

=IFERROR(SMALL(IF(V(FREQUENCY(IF(MID($A2,ROW($1:$255),1)=" ",ROW($1:$255)),IF(1-(MID($A2,ROW($1:$255),1)=" "),ROW($1:$255)))),V()),COLUMNS($B2:B2)),"")

For the foregoing formula to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Note that this formula yields the number of consecutive spaces in-between the words in a sentence (string).

 

[Rick Rothstein]

Here is a UDF (user defined function) that will output the values you are asking for...

Function CountSBW(ByVal S As String, Instance As Long) As Variant
  Dim X As Long
  On Error GoTo NoSuchInstance
  S = Replace(Trim(S), " ", Chr(1))
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!" & Chr(1) & "]" Then Mid(S, X) = " "
  Next
  CountSBW = Len(Split(Application.Trim(S))(Instance - 1))
  Exit Function
NoSuchInstance:
  CountSBW = ""
End Function

The SBW in the function's name is short for "Spaces Between Words". To use this function, pass it the text that you want to parse as the first argument and the instance of the group of spaces you want to count. So if the first text to be parsed is in cell A1, put this formula in cell B1 (normally entered)...

=CountSBW($A1,COLUMNS($B:B))

and copy it across for as many columns as you think you will ever have groups of spaces between words to parse, then copy all of those formulas down to the bottom of your data. Note that you should change the two B column references to whatever the column letter is for the column that the first formula is placed in.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountSBW just like it was a built-in Excel function. For example, as shown above...

=CountSBW($A1,COLUMNS($B:B))

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Tetra201]

The following UDF returns a string with numbers of spaces between the words in the input string.
For the example from Post #13, =SPACES(A2) returns "223".

Function SPACES(ByVal s As String) As String
    Application.Volatile
    TmpStr = Trim(s)
    TmpArr = Split(WorksheetFunction.Trim(s), " ")
    For i = LBound(TmpArr) To UBound(TmpArr) Step 1
        TmpStr = Replace(TmpStr, TmpArr(i), Chr(1))
    Next i
    TmpArr = Split(TmpStr, Chr(1))
    TmpStr = ""
    For i = LBound(TmpArr) + 1 To UBound(TmpArr) - 1 Step 1
        TmpStr = TmpStr & CStr(Len(TmpArr(i)))
    Next i
    SPACES = TmpStr
End Function

 

[Rick Rothstein]

The following UDF returns a string with numbers of spaces between the words in the input string.
For the example from Post #13, =SPACES(A2) returns "223"

You might want to put a delimiter in there just in case there could be 10 or more spaces between words.

 

[Tetra201]

You might want to put a delimiter in there just in case there could be 10 or more spaces between words.

Thank you for the useful suggestion. Here is an updated code; for the example from Post #13, it returns "2-2-3".

Function SPACES(ByVal s As String) As String
    Application.Volatile
    TmpStr = Trim(s)
    TmpArr = Split(WorksheetFunction.Trim(s), " ")
    For i = LBound(TmpArr) To UBound(TmpArr) Step 1
        TmpStr = Replace(TmpStr, TmpArr(i), Chr(1))
    Next i
    TmpArr = Split(TmpStr, Chr(1))
    TmpStr = ""
    For i = LBound(TmpArr) + 1 To UBound(TmpArr) - 1 Step 1
        TmpStr = TmpStr & CStr(Len(TmpArr(i))) & "-"
    Next i
    SPACES = Left(TmpStr, Len(TmpStr) - 1)
End Function