We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# get 8 words in a string

on
Medium Priority
195 Views
I am trying to get the first 8 words from a string with this function I get sub string error.

Public Function Get8Words(sInput As String) As String
Dim sTmp() As String

sTmp() = Split(sInput, " ", 9)
sTmp(UBound(sTmp)) = vbNullString<<<<<< Error here
Get8Words = Join(sTmp, " ")
End Function
Comment
Watch Question

## View Solutions Only

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Public Function Get8Words(sInput As String) As String
Dim sTmp() As String

sTmp() = Split(sInput, " ")
redim preserve sTmp(0 to 7) as string
Get8Words = rtrim(Join(sTmp, " "))
End Function

Not the solution you were looking for? Getting a personalized solution is easy.

High School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009
Commented:
Same thing...works for me...

Option Explicit

Private Sub Command1_Click()
Dim source As String

source = "the big fat cat ate fish and purred very loudly"

Dim dest As String
dest = Get8Words(source)

Debug.Print source
Debug.Print dest
End Sub

Public Function Get8Words(sInput As String) As String
Dim sTmp() As String

sTmp() = Split(sInput, " ", 9)
sTmp(UBound(sTmp)) = vbNullString
Get8Words = Join(sTmp, " ")
End Function

Output:

the big fat cat ate fish and purred very loudly
the big fat cat ate fish and purred

Commented:
The same code work for me as well. Maybe you should check what is the sInput string.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile