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