OK I am after a VBA Function that will tell me how many splits would be required within a cell based on split character length and the closest split value of COMMA
OK, I have a cell (A33) as such,
N2NP002,N3NP001,N4NP027,N5NP012,N6NP003,N10NP010,Z401,Z217,Z218,Z219,Z220,Z2,Z22,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z230,Z231,Z232,Z233,Z234,Z235,Z236,Z33,Z237,Z238,Z239,Z240,Z241,Z222,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z23
This splits into 3 cells based on the following criteria, split at comma maintaining a length as close to 76 characters.
I have a routine that splits this as follows,
N2NP002,N3NP001,N4NP027,N5NP012,N6NP003,N10NP010,Z401,Z217,Z218,Z219,Z220,Z2 (Which is 76 Characters)
Z22,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z230,Z231,Z232,Z233,Z234,Z235,Z236 (73 Characters)
Z33,Z237,Z238,Z239,Z240,Z241,Z222,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z23 (72 Characters)
What I want is a VBA function that will tell me that 3 cells will be required for my split routine.
ie =SplitNumber(A33,76) where A33 is the input text, 76 would be the character length and to split at a COMMA.
So in the example given I would like to see the result of =SplitNumber(A33,76) as 3
Remember the catch here is that when the 77th character is a comma, then the split happens at character 76. ie So look for last comma at or before character 77 and then that value -1 is were you would split, as per the 1st split happening a Z2, where the comma is in position 77. If Z2 was Z12, Z12 would be pushed into the next row and the flow on effect would be that SplitNumber(A33,76) would result in 4.
Example result of what my routine will give when Z2 becomes Z12,
N2NP002,N3NP001,N4NP027,N5NP012,N6NP003,N10NP010,Z401,Z217,Z218,Z219,Z220 (73 Characters)
Z12,Z22,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z230,Z231,Z232,Z233,Z234,Z235 (72 Characters)
Z236,Z33,Z237,Z238,Z239,Z240,Z241,Z222,Z223,Z224,Z225,Z226,Z227,Z228,Z229 (73 Characters)
Z23 (3 Characters)
If interested, or if it helps, here is the Sub that I have to split A33 into 76 character lengths putting the result into cell C33, C34, C35 etc as required
Sub SplitTextBy76Chars()
Dim inputText As String
Dim maxLen As Integer
Dim result As String
Dim lastComma As Integer
Dim cellRow As Integer
Dim checkChar As String
' Read the input text from A33
inputText = Range("A33").Value
maxLen = 76 ' Maximum length for each split
cellRow = 33 ' Starting row for output, adjust as needed
Do While Len(inputText) > 0
' Get the first maxLen characters
result = Left(inputText, maxLen)
' Check if the 77th character is a comma
If Len(inputText) > maxLen Then
checkChar = Mid(inputText, maxLen + 1, 1)
If checkChar = "," Then
' If the 77th character is a comma, use the first 76 characters
result = Left(inputText, maxLen)
inputText = Mid(inputText, maxLen + 2)
Else
' Find the last comma within this substring
lastComma = InStrRev(result, ",")
' If a comma is found, split there; otherwise, take the full 76 characters
If lastComma > 0 Then
result = Left(result, lastComma - 1)
inputText = Mid(inputText, lastComma + 1)
Else
inputText = Mid(inputText, maxLen + 1)
End If
End If
Else
inputText = ""
End If
' Write the result to the next row
Range("C" & cellRow).Value = result
cellRow = cellRow + 1
Loop
End Sub
If you only need the count:
Public Function SplitNumber(txt, maxLen As Long)
Dim el, s As String, col As New Collection, v As String, tooLong As Boolean
For Each el In Split(txt, ",")
v = s & "," & el
tooLong = Len(v) > maxLen
If tooLong Then col.Add s
s = IIf(tooLong, el, v)
Next el
If Len(s) > 0 Then col.Add s
SplitNumber = col.Count
End Function