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

 

[István Hirsch]

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

I typed "This is a normal text" (with only one space everwhere) and got 1-1-5-2.

 

[Tetra201]

I typed "This is a normal text" (with only one space everwhere) and got 1-1-5-2.

Confirmed. Working on a fix.

 

[mlo356]

Silly question. I can't comprehend why anyone would want to count the number of spaces, other than ones (maybe). can that be explained!!

Not a dumb question at all. I didn't think that I would ever have a need for this.

For my situation, I have these old "green bar" reports that do not export very friendly out of our database. The program Monarch did a great job of scraping it but due to the inconsistency in the formatting and field alignments (when exported), I wasn't able to split 10 or so fields out into their own column. So, I have one field that really contains about 10 fields. To make matters worse, the number of spaces between each word varies except for the description. When you get to the description, it is consistently one space in between each word. I had tried many different variations of exporting to no avail.

 

[Rick Rothstein]

I typed "This is a normal text" (with only one space everwhere) and got 1-1-5-2.

Hmm, the function returns 1-1-1 for me (using XL2010 on Win8.1).

 

[mlo356]

Hmm, the function returns 1-1-1 for me (using XL2010 on Win8.1).

Im getting 1-1-5-2 using XL2013 on Win10

It's working perfect on my dataset though.

 

[Tetra201]

I typed "This is a normal text" (with only one space everwhere) and got 1-1-5-2.

Here is the fix. The algorithm and the bulk of the code was kindly provided by Rick Rothstein in Post #21, I only added two lines.

Function SPACES(ByVal S As String) As String
    S = Trim(S)
    If Len(S) Then
        For Each V In Split(Application.Trim(Join(Application.Transpose(Evaluate _
            ("IF(MID(""" & S & " "",ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))
            SPACES = SPACES & Len(V) & "-"
        Next V
        SPACES = Left(SPACES, Len(SPACES) - 1)
    End If
End Function

 

[mlo356]

Here is the fix. The algorithm and the bulk of the code was kindly provided by Rick Rothstein in Post #21, I only added two lines.

Function SPACES(ByVal S As String) As String
    S = Trim(S)
    If Len(S) Then
        For Each V In Split(Application.Trim(Join(Application.Transpose(Evaluate _
            ("IF(MID(""" & S & " "",ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))
            SPACES = SPACES & Len(V) & "-"
        Next V
        SPACES = Left(SPACES, Len(SPACES) - 1)
    End If
End Function

Works for me. Thank you both. I probably would have never figured this out.

 

[Rick Rothstein]

Here is the fix. The algorithm and the bulk of the code was kindly provided by Rick Rothstein in Post #21, I only added two lines.

Function SPACES(ByVal S As String) As String
    S = Trim(S)
    If Len(S) Then
        For Each V In Split(Application.Trim(Join(Application.Transpose(Evaluate _
            ("IF(MID(""" & S & " "",ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))
            SPACES = SPACES & Len(V) & "-"
        Next V
        SPACES = Left(SPACES, Len(SPACES) - 1)
    End If
End Function

Actually, the code, as posted, has two "flaws"... it returns a #VALUE! error if the text consists of a single word (letters without at least one space between them) or if the text contains quote marks. Here is the fix...

[table="width: 500"]
[tr]
	[td]Function Spaces(ByVal S As String) As String
  Dim v As Variant
  S = Replace(Trim(S), """", """""")
  If Len(S) Then
    For Each v In Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(MID(""" & S & """,ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))
      Spaces = LTrim(Spaces & " " & Len(v))
    Next
  End If
  Spaces = Replace(Spaces, " ", "-")
End Function[/td]
[/tr]
[/table]

 

[mlo356]

Actually, the code, as posted, has two "flaws"... it returns a #VALUE! error if the text consists of a single word (letters without at least one space between them) or if the text contains quote marks. Here is the fix...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Spaces(ByVal S As String) As String
  Dim v As Variant
  S = Replace(Trim(S), """", """""")
  If Len(S) Then
    For Each v In Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(MID(""" & S & """,ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))
      Spaces = LTrim(Spaces & " " & Len(v))
    Next
  End If
  Spaces = Replace(Spaces, " ", "-")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the revision.

Also, it does work if the text begins with or contains a number.

 

[Tetra201]

While the problem has been resolved already, here is my own algorithm, hopefully foolproof:

Function SPACES(ByVal S As String) As String
    Z = Chr(1)
    S = Trim(S)
    For i = 1 To Len(S) Step 1
        If Mid(S, i, 1) <> " " Then Mid(S, i, 1) = Z
    Next
    Do: S = Replace(S, Z & Z, Z)
    Loop Until InStr(1, S, Z & Z) = 0
    A = Split(S, Z)
    For i = LBound(A) + 1 To UBound(A) - 1 Step 1
        SPACES = SPACES & Len(A(i)) & " "
    Next i
    SPACES = Replace(Trim(SPACES), " ", "-")
End Function