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" <(E-Mail Removed)> wrote in message
news:4917FAF9-6478-4488-8F29-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:A320D4D3-A734-4646-B69E-(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message
news:2CD7BBCB-154C-4BE2-8AF7-(E-Mail Removed)...
> 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