У Фила есть ряд ячеек, содержащих текст. Часть текста слишком длинная, поэтому ему нужно разбить ее на несколько ячеек. Например, если в ячейке находится «быстрая коричневая лиса», и ему нужно разделить текст так, чтобы не было больше 12 символов, Фил легко это сделает. Но он хочет убедиться, что разделение выполняется на пробел перед 12-м символом, а не на точном 12-м символе. Фил не знает, как это сделать.

Вы могли подумать, что можете использовать инструмент «Текст в столбцы» в Excel, но он не подходит для этой работы. Если вы настроите инструмент для разделения текста на основе разделителей, таких как пробел, то в результате вы получите одно слово в каждой ячейке. Если вы настроили инструмент для разделения текста по «фиксированной ширине», он не разбивает слова по пробелам; он просто следит за тем, чтобы каждый кусок был любого размера, который вы указали.

Вы можете использовать формулу для получения желаемых результатов, но в результате получаются удивительно длинные формулы. Например, если в ячейке A3 находится слишком длинный текст, можно использовать следующую формулу для разделения первого фрагмента этого текста на пробел перед 12-м символом:

=IF(LEN($A3)>12,IF(ISERROR(FIND(" ",MID($A3,12,1)

&MID($A3,11,1)&MID($A3,10,1)&MID($A3,9,1)&MID($A3,8,1)

&MID($A3,7,1)&MID($A3,6,1)&MID($A3,5,1)&MID($A3,4,1)

&MID($A3,3,1)&MID($A3,2,1))),MID($A3,1,1),MID($A3,1, 13-FIND(" ",MID($A3,12,1)&MID($A3,11,1)&MID($A3,10,1)

&MID($A3,9,1)&MID($A3,8,1)&MID($A3,7,1)&MID($A3,6,1)

&MID($A3,5,1)&MID($A3,4,1)&MID($A3,3,1)&MID($A3,2,1))))

,RIGHT($A3,LEN($A3)))

Помните — все это одна формула, чтобы получить первый кусок. Формулы для получения второго, третьего, четвертого и последующих фрагментов даже длиннее. Ясно, что использование формулы — не лучший подход.

Остается использовать макрос. Макрос может проверить текстовую строку и легко разделить ее на части нужной длины. Рассмотрим следующую пользовательскую функцию:

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

Функция принимает два или три параметра. Первый параметр — это строка, которую нужно разделить, второй — какой фрагмент вы хотите от строки, а третий (и необязательный) параметр — это желаемая длина каждого фрагмента. Если вы опустите третий параметр, тогда функция предполагает, что вы хотите, чтобы каждый фрагмент состоял максимум из 12 символов. В качестве примера, предполагая, что текст находится в ячейке A5, следующий код вернет второй фрагмент текста, где каждый фрагмент имеет длину до 12 символов:

=SplitMe(A5,2)

Функция вернет хорошие результаты при условии, что каждое слово в текстовой строке не длиннее указанной целевой длины для каждого фрагмента. Если это так, то вы получите странные результаты, в том числе некоторые фрагменты, не содержащие полных слов.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3899) применим к Microsoft Excel 97, 2000, 2002 и 2003.