Count spaces in front of text

Hi

Is it possible to count the number of spaces that appear before a text in vba?

E.g. " Jean" = 3

I really would appreciate some help on this.

Thanks and Kind Regards

Like this:

For i = 1 To Len(ActiveCell.Value)
If Mid(ActiveCell.Value, i, 1) <> " " Then
MsgBox CStr(i - 1) & " spaces"
Exit For
End If
Next


Col

*Poppy on knees* Thank you!!!! thank you! thank you! thank you! thank you! Col

You're a life saver, I've been trying all day to figure this out. Thank you so much.

Kind Regards

Hi Poppy,

Try:

'=============>>
Public Sub Tester()
Const sStr As String = " Jean"
Dim iSpaces As Long

iSpaces = InStr(1, sStr, "J") - 1
MsgBox iSpaces
End Sub
'<<=============


---
Regards,
Norman


"poppy" <poppy.2ah72q_1152108614.9348@excelforum-nospam.com> wrote in
message news:poppy.2ah72q_1152108614.9348@excelforum-nospam.com...
>
> Hi
>
> Is it possible to count the number of spaces that appear before a text
> in vba?
>
> E.g. " Jean" = 3
>
> I really would appreciate some help on this.
>
> Thanks and Kind Regards
>
>
> --
> poppy
> ------------------------------------------------------------------------
> poppy's Profile:
> http://www.excelforum.com/member.php...o&userid=11453
> View this thread: http://www.excelforum.com/showthread...hreadid=558439
>

And maybe a little more generic:

Option Explicit

Public Sub Tester()
Const sStr As String = " Jean"
Dim iSpaces As Long

iSpaces = InStr(1, sStr, Left(Trim(sStr), 1), vbTextCompare) - 1
MsgBox iSpaces
End Sub

Norman Jones wrote:
>
> Hi Poppy,
>
> Try:
>
> '=============>>
> Public Sub Tester()
> Const sStr As String = " Jean"
> Dim iSpaces As Long
>
> iSpaces = InStr(1, sStr, "J") - 1
> MsgBox iSpaces
> End Sub
> '<<=============
>
> ---
> Regards,
> Norman
>
> "poppy" <poppy.2ah72q_1152108614.9348@excelforum-nospam.com> wrote in
> message news:poppy.2ah72q_1152108614.9348@excelforum-nospam.com...
> >
> > Hi
> >
> > Is it possible to count the number of spaces that appear before a text
> > in vba?
> >
> > E.g. " Jean" = 3
> >
> > I really would appreciate some help on this.
> >
> > Thanks and Kind Regards
> >
> >
> > --
> > poppy
> > ------------------------------------------------------------------------
> > poppy's Profile:
> > http://www.excelforum.com/member.php...o&userid=11453
> > View this thread: http://www.excelforum.com/showthread...hreadid=558439
> >

--

Dave Peterson

Got back to this one reviewing my watched threads...
If brevity is the watchword:

MyCountOfSpaces=len(TheSTring)-len(ltrim(thestring))

Any better/more efficient methods?

C

Hi Guys

Thanks once again for all the help. Really appreciate it.

Kind Regards