modify text splitting function to work with rows instead of columns

I am trying to modify krishna kumar's function (found here: http://www.ozgrid.com/forum/sh…=If+SplitCount+Len%28v%29) to work with rows instead of columns. I have copied the code from his post below. As per his post it is accessed by using the formula =XTRACT40($B3,COLUMNS($C3:C3))

I have tried changing it to =XTRACT40(B$3,ROW(C$3:C3)) and =XTRACT40(B$3,ROWS(C$3:C3)) (array) and several other variations with no success.

To quickly summarize what krishna kumar's code is doing; it splits up long sentences/text strings contained in a single cell and spreads them over multiple cells to the right of the cell with the long sentence/text string. In the example below any sentence/text string over 40 chars is broken up into max 40 character segments per cell.

For example, if you had "The quick brown fox leaped over the restless dog" in cell B3 and copied that formula into cells C3 and D3 you would end up with "The quick brown fox leaped over the" in cell C3 and "restless dog" in D3, even though ""The quick brown fox leaped over the" is only 35 chars long. This is because krisha kumar's code is also smart enough not to break up words, in this case 'restless' where the 't' is the 40th character.

My goal is to be able to use this function by coping the formula into the cells beneath the target cell instead of beside them. Any assistance is greatly appreciated.

Function XTRACT40(ByRef v, Optional SplitCount As Long = 1) As String 
    Dim s As String, i As Long, n As Long 
    
    If SplitCount = 1 And Len(v) < 41 Then 
        XTRACT40 = v 
        Exit Function 
    Else 
        For i = 1 To Len(v) 
            n = n + 1 
            s = Mid$(v, i, 41) 
            Select Case Asc(Right$(s, 1)) 
            Case 32, 10, 44 
                XTRACT40 = Trim$(Left$(s, 40)) 
            Case Else 
                XTRACT40 = Trim$(Left$(s, InStrRev(s, " "))) 
            End Select 
            If n = SplitCount Then 
                XTRACT40 = Trim$(XTRACT40) 
                Exit For 
            Else 
                i = i + Len(XTRACT40) 
                XTRACT40 = vbNullString 
            End If 
        Next 
    End If 
End Function

Re: modify text splitting function to work with rows instead of columns

thanks cytop

Re: modify text splitting function to work with rows instead of columns

That works great. Thank you very much.