Hello,

How can i count Count Space, comma and fullstop in a string through macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul

Rahul,

Use the following function ...

Function CountCharacters(ByVal Str As String) As Long

Dim i As Integer

For i = 1 To Len(Str)
If Mid(Str, i, 1) = " " Or Mid(Str, i, 1) = "," Or Mid(Str, i, 1) =
"." Then
CountCharacters = CountCharacters + 1
End If
Next i

End Function

Rgds,

Alan

Rahul Gupta wrote:
Hello,

How can i count Count Space, comma and fullstop in a string through macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul

In the worksheet use a combination of LEN() and SUBSTITUTE():

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) will count the number of spaces
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) will count the number of commas, etc
--
Gary's Student


"Rahul Gupta" wrote:

Hello,

How can i count Count Space, comma and fullstop in a string through macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul

Hi Rahul,

Try something like:

'=============
Public Sub Tester()
Dim arr As Variant
Dim sStr As String
Dim i As Long
Dim iCtr As Long
Const sStr2 As String = " My name is Rahul, my " _
& "brother's name is NewName & our surname "

sStr = sStr2
arr = Array(" ", ",", ".")

For i = LBound(arr) To UBound(arr)
sStr = Replace(sStr, arr(i), vbNullString)
Next i

iCtr = Len(sStr2) - Len(sStr)

MsgBox iCtr

End Sub
'<<=============


---
Regards,
Norman


"Rahul Gupta" wrote in message
...
Hello,

How can i count Count Space, comma and fullstop in a string through macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our
surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul

Hello Again Gary,

SUBSTITUTE is not working in VBA? It is a worksheet function, as u have also
mentioned. Any other way?

This method wont help.

Regards,
Rahul.

"Gary''s Student" wrote:

In the worksheet use a combination of LEN() and SUBSTITUTE():

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) will count the number of spaces
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) will count the number of commas, etc
--
Gary's Student


"Rahul Gupta" wrote:

Hello,

How can i count Count Space, comma and fullstop in a string through macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul

But you need to test for errors; there may not be a "," like in the first example.
To put it all in one formula makes it very long and almost unreadable. Best is to use some intermediate cells, if you want a
formula approach rather than a VBA function.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Gary''s Student" wrote in message
...
| In the worksheet use a combination of LEN() and SUBSTITUTE():
|
| =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) will count the number of spaces
| =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) will count the number of commas, etc
| --
| Gary's Student
|
|
| "Rahul Gupta" wrote:
|
| Hello,
|
| How can i count Count Space, comma and fullstop in a string through macro.
|
| For Ex. if A1= My name is Rahul. Then B1=4
| For Ex. if A1= My name is Rahul, my brother's name is NewName & our surname
| is Gupta. Then B1 =14
|
| and so on.
|
| Thanks in advance,
|
| Rahul

MsgBox Len(myString) - Len(Replace(Replace(Replace(myString, " ", ""),
".", ""), ",", ""))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rahul Gupta" wrote in message
...
Hello Again Gary,

SUBSTITUTE is not working in VBA? It is a worksheet function, as u have
also
mentioned. Any other way?

This method wont help.

Regards,
Rahul.

"Gary''s Student" wrote:

In the worksheet use a combination of LEN() and SUBSTITUTE():

=LEN(A1)-LEN(SUBSTITUTE(A1," ","")) will count the number of spaces
=LEN(A1)-LEN(SUBSTITUTE(A1,",","")) will count the number of commas, etc
--
Gary's Student


"Rahul Gupta" wrote:

Hello,

How can i count Count Space, comma and fullstop in a string through
macro.

For Ex. if A1= My name is Rahul. Then B1=4
For Ex. if A1= My name is Rahul, my brother's name is NewName & our
surname
is Gupta. Then B1 =14

and so on.

Thanks in advance,

Rahul