Подсчитайте количество разбиений внутри ячейки
ОК, мне нужна функция VBA, которая сообщит мне, сколько разбиений потребуется в ячейке на основе длины разделенного символа и ближайшего значения разделения ЗАПЯТОЙ.
ОК, у меня есть ячейка (А33) как таковая,
N2NP002,N3NP001,N4NP027,N5NP012,N6NP003,N10NP010,Z401,Z217,Z218,Z219,Z220,Z2,Z22,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z230,Z231,Z2 32, З233, З234, Z235,Z236,Z33,Z237,Z238,Z239,Z240,Z241,Z222,Z223,Z224,Z225,Z226,Z227,Z228,Z229,Z23
Это разбивается на 3 ячейки на основе следующих критериев, разделенных запятой, сохраняя длину, близкую к 76 символам.
У меня есть процедура, которая разбивает это следующим образом:
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)
Мне нужна функция VBA, которая сообщит мне, что для моей процедуры разделения потребуются 3 ячейки.
т. е. =SplitNumber(A33,76) где A33 — это входной текст, 76 — это длина символа, которую нужно разделить ЗАПЯТОЙ.
Итак, в приведенном примере я хотел бы видеть результат =SplitNumber(A33,76) как 3.
Помните, что загвоздка здесь в том, что если 77-й символ представляет собой запятую, то разделение происходит на 76-м символе. т. е. ищите последнюю запятую в символе 77 или перед ним, а затем это значение -1, где бы вы разделили, согласно первому разделению, происходящему Z2, где запятая находится в позиции 77. Если бы Z2 был Z12, Z12 был бы вставлен в следующая строка и эффект будут такими, что SplitNumber(A33,76) приведет к 4.
Пример результата того, что даст моя программа, когда Z2 станет 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 символа)
Если интересно или если это поможет, вот подзаголовок, в котором мне нужно разделить A33 на 76 символов, помещая результат в ячейку C33, C34, C35 и т. д. по мере необходимости.
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
Ответы:
Используйте абсолютную ссылку на ячейку (например, $B$4), щелкните адрес ячейки в формуле и нажмите F4, чтобы перейти на абсолютную ссылку:
Public Function SplitNumber(inputText As Range, Optional maxLen As Integer = 76)
Dim x As Integer, dic As Object, splVal, dkey
Set dic = CreateObject("Scripting.Dictionary")
x = 1
For Each splVal In Split(inputText.Value, ",")
If dic.Exists(x) Then
If Len(dic(x) & "," & splVal) <= maxLen Then
dic(x) = dic(x) & "," & splVal
Else
x = x + 1
dic.Add x, splVal
End If
Else
dic.Add x, splVal
End If
Next splVal
x = Application.Caller.Row - inputText.Row + 1
If dic.Exists(x) Then
SplitNumber = dic(x)
Else
SplitNumber = ""
End If
End Function
Пройдите мимо формулы и параметров, затем нажмите Enter и перетащите формулу вниз (или скопируйте).
тест:
Я заменил разделители в точках разделения на другой разделитель («|»), а затем создал массив, используя новый разделитель. Затем я использовал WorksheetFunction.TRANSPOSE(), чтобы распределить данные по необходимому количеству строк.
Я также добавил функцию SplitNumber() для полноты картины.
Function SplitNumber(Text As String, ChunkLength As Long) As Long
Dim Result As Variant
Result = SplitIntoDelimitedChunksByLength(Text, 76, ",")
SplitNumber = UBound(Result) + 1 - LBound(Result)
End Function
Function SplitIntoDelimitedChunksByLength(Text As String, ChunkLength As Long, Optional Delimiter As String = ",", Optional TempDelimiter As String = "|")
Dim n As Long
Dim StartOfChunk As Long
Dim Length As Long
Dim LastTempDelimiterPosition As Long
Dim NextDelimiterPosition As Long
n = 1
Do While InStr(n, Text, Delimiter) > 0
n = InStr(n, Text, Delimiter)
LastTempDelimiterPosition = InStrRev(Text, TempDelimiter, n)
NextDelimiterPosition = InStr(n, Text, Delimiter)
If (n - LastTempDelimiterPosition = ChunkLength) Or (NextDelimiterPosition - LastTempDelimiterPosition + 1 >= ChunkLength) Then
Mid(Text, n, 1) = TempDelimiter
End If
n = n + 1
DoEvents
Loop
SplitIntoDelimitedChunksByLength = Split(Text, TempDelimiter)
End Function
Если вам нужен только счетчик:
Public Function SplitNumber(txt, maxLen As Long)
Dim el, s As String, col As New Collection, v As String
For Each el In Split(txt, ",")
v = s & "," & el
If Len(v) > maxLen Then
col.Add s
s = el
Else
s = v
End If
Next el
If Len(s) > 0 Then col.Add s
SplitNumber = col.Count
End Function