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

 

[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".

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

Here is another way to write your SPACES function...

[table="width: 500"]
[tr]
	[td]Function Spaces(ByVal S As String) As String
  Dim V As Variant
  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
End Function[/td]
[/tr]
[/table]

which, in turn, led me to another way to write the code I posted in Message #17...

[table="width: 500"]
[tr]
	[td]Function CountSBW(ByVal S As String, Instance As Long) As String
  On Error GoTo NoSuchInstance
  CountSBW = Len(Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(MID(""" & S & " "",ROW(1:" & Len(S) & "),1)="" "",""*"","" "")")), "")))(Instance - 1))
NoSuchInstance:
End Function[/td]
[/tr]
[/table]

 

[Tetra201]

Here is another way to write your SPACES function...
...
which, in turn, led me to another way to write the code I posted in Message #17...

Cool.

Is there a difference between Application.Transpose and WorksheetFunction.Transpose?
(like in case of Application.Match and WorksheetFunction.Match)

 

[Rick Rothstein]

Is there a difference between Application.Transpose and WorksheetFunction.Transpose?
(like in case of Application.Match and WorksheetFunction.Match)

I think there are supposed to be some speed differences, but I am not sure which is supposed to be faster... other than that, they function the same. Actually, I thought Application and WorksheetFunction were interchangeable, functionality-wise... are you saying that is not true for the MATCH function? If so, in what way does Application.Match differ from WorksheetFunction.Match?

 

[Tetra201]

I think there are supposed to be some speed differences, but I am not sure which is supposed to be faster... other than that, they function the same. Actually, I thought Application and WorksheetFunction were interchangeable, functionality-wise... are you saying that is not true for the MATCH function? If so, in what way does Application.Match differ from WorksheetFunction.Match?

When there is no match, the Application.Match allows to easily trap the error:

TmpVar = Application.Match(lookup_value, lookup_array, 0)
If Not IsError(TmpVar) Then ...

The WorksheetFunction.Match does not -- it throws an RTE '1004'.

 

[Rick Rothstein]

When there is no match, the Application.Match allows to easily trap the error:

TmpVar = Application.Match(lookup_value, lookup_array, 0)
If Not IsError(TmpVar) Then ...

The WorksheetFunction.Match does not -- it throws an RTE '1004'.

Thanks... I'll have to investigate this some later today (I'm going to sleep now).

 

[mole999]

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

 

[mlo356]

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

Thank You So Much! This is working perfectly so far. Does exactly what I need and it gives me a count of words in the string. I'll spend the rest of the day trying to figure out how/why it works. I am fairly new to VBA.

Thanks to everyone who helped/chimed in. I will be testing out the other suggestions as well and report back.

This forum never cease to amaze me. Invaluable wealth of knowledge.

 

[Tetra201]

Glad to have been of help.

Still, I am joining mole999 in being curious -- why do you need to count the number of single-width spaces between the words?

 

[István Hirsch]

Thank You So Much! This is working perfectly so far. Does exactly what I need and it gives me a count of words in the string. I'll spend the rest of the day trying to figure out how/why it works. I am fairly new to VBA.

Thanks to everyone who helped/chimed in. I will be testing out the other suggestions as well and report back.

This forum never cease to amaze me. Invaluable wealth of knowledge.

If you want to count the words in the string just Trim it and count the spaces +1.

 

[mole999]

Chip Pearson provides this as a formula > =IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)