Splitting Text to Multiple Cells

[dfolzenlogen]

This may be a dumb question but here goes! I purchased Allen Wyatt's ExcelTips and was trying to implement a User Defined Function referenced there for Splitting Text to Multiple Cells. I copied and pasted the code into my VBA Module. When I use the function and enter the parameters, I see the result I want but the result is not returned to my spreadsheet. What am I missing?

I have the following sample text in Column G:

The quick brown fox jumped over the fence and then ran home as fast as he could.

I am using the following formula in Column I:

=SplitMe(G21,1,10)

Below is the code I am using:

Function SplitMe(sSentence As String, iPos As Integer, Optional iLen = 12)
   Dim sSegments() As String
   Dim iSegments As Integer
   Dim sRest As String
   Dim sTemp As String
   Dim iSpace As Integer
   Dim J As Integer
   
   iSegments = 0
   sRest = sSentence
   sTemp = Left(sRest, iLen + 1)
   Do Until Len(sTemp) <= iLen
      iSpace = 0
      For J = Len(sTemp) To 1 Step -1
         If Mid(sTemp, J, 1) = " " And iSpace = 0 Then iSpace = J
      Next J
      If iSpace > 0 Then
         sTemp = Left(sRest, iSpace - 1)
         sRest = Mid(sRest, iSpace + 1)
      Else
         sRest = Mid(sRest, Len(sTemp) + 1)
      End If
      iSegments = iSegments + 1
      ReDim Preserve sSegments(1 To iSegments)
      sSegments(iSegments) = sTemp
      sTemp = Left(sRest, iLen + 1)
   Loop
   iSegments = iSegments + 1
   ReDim Preserve sSegments(1 To iSegments)
   sSegments(iSegments) = sTemp
   If iPos <= iSegments Then
      SplitMe = sSegments(iPos)
   Else
      SplitMe = ""
   End If
End Function

 

[Leith Ross]

What is being returned to the cell?

What should the output from the function look like?

 

[dfolzenlogen]

What is being returned to the cell?

What should the output from the function look like?

Thanks for your quick response.

The following is being returned to the cell:

 =SplitMe(G21,1,10)
 =SplitMe(G21,2,10)
 =SplitMe(G21,3,10)
 =SplitMe(G21,4,10)

=SplitMe(G21,1,10) should return "The Quick"
=SplitMe(G21,2,10) should return "brown fox"
=SplitMe(G21,3,10) should return "jumped"
=SplitMe(G21,4,10) should return "over the"
. . . and so on

The UDF should chunk the data based on the
# set in iLen

 

[dfolzenlogen]

ok. I feel like an idiot. The column where I was entering the formula was formatted as text rather than General. Works like a charm now.
Thanks for your help though!

D

 

[Rick Rothstein]

ok. I feel like an idiot. The column where I was entering the formula was formatted as text rather than General. Works like a charm now.

Any chance you can tell us what this function is supposed to be doing, what its purpose is?