Hi all,
How can I count the number of characters in a string so it works as the example below Len() works quit well but I would like it to stop on blanks.
<some function> (me) ''' return 2
<some function>(me_rest) ''' return 7
<some function>(rest s) ''' return 4
Thankful for help.
\Jonas
Count characters in a string using VBA in excel
Suren Reddy
Hi,
you can use something like this:
Function LenOfFirstWord(byval YourString As String) As Long
Dim i As Long
i = InStr(YourString, " ")
If i > 0 Then
LenOfFirstWord = i-1
Else
LenOfFirstWord = Len(YourString)
End If
End Function
The above will return 0 for " hello world", so if you want leading spaces ignored use LTrim to remove them.
--
SvenC
Rattlerr
Couple of ways, use the Split function or Instr
MsgBox Len(Split("me")(0)) ''' return 2
MsgBox Len(Split("me_rest")(0)) ''' return 7
MsgBox Len(Split("rest s")(0)) ''' return 4
Dim strText As String
Dim lngLen As Long
strText = "rest s"
lngLen = InStr(strText, " ") - 1
If lngLen < 0 Then lngLen = Len(strText)
MsgBox lngLen
Ratheesh&#42;MCP&#42;
Thanks both of you. I appreciate your help a lot!
Best regards
Jonas