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

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


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=558439

*-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


--
poppy
------------------------------------------------------------------------
poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453
View this thread: http://www.excelforum.com/showthread...hreadid=558439

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" wrote in
message ...

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" wrote in
message ...

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



--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=55843

Hi Guys

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

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