формулы, макрос VBA, диалог поиска и замены

В этом совете вы найдете 3 способа удалить возврат каретки из ячеек Excel. Вы также узнаете, как заменить разрывы строк другими символами. Все решения работают для Excel 365, 2021, 2019 и более ранних версий.

Разрывы строк в тексте могут быть вызваны разными причинами. Обычно возврат каретки появляется, когда вы копируете текст с веб-страницы, получаете книгу, уже содержащую разрывы строк, от клиента или добавляете их самостоятельно с помощью Alt+Enter.

В любом случае, сейчас вы хотите удалить символы возврата каретки, так как они не позволяют вам найти фразу и делают содержимое столбца неорганизованным, когда вы включаете опцию переноса текста.

Обратите внимание, что изначально термины “Возврат каретки” и “Перевод строки” использовались в пишущей машинке и означали 2 разных действия, можно найти больше на Вики.

Компьютеры и программное обеспечение для обработки текста создавались с учетом специфики пишущей машинки. Вот почему теперь для обозначения разрыва строки используются два разных непечатаемых символа: “Возврат каретки” (CR, код ASCII 13) и “Перевод строки” (LF, код ASCII 10). Windows использует 2 символа один за другим: CR+LF и LF для систем *NIX. Будьте осторожны: в Excel вы можете найти оба варианта. Если вы импортируете данные из .txt или .csv файл, вы, скорее всего, найдете Возврат каретки + Перевод строки. Когда вы разрываете строку с помощью Alt+Enter , Excel вставляет Перевод строки Только.

Если вы получите файлы .csv от человека, который использует Linux, Unix и т. д., вы найдете только Перевод строки опять таки.

Все эти 3 способа очень быстрые. Не стесняйтесь выбирать тот, который подходит вам лучше всего:

Кончик. Если вы ищете решение противоположной задачи, то читайте, как быстро добавить разрыв строки в ячейку Excel.

Удалить возврат каретки вручную

Плюсы: самый быстрый способ.

Минусы: никаких дополнительных функций :(.

Пожалуйста, ознакомьтесь с инструкциями по устранению разрывов строк с помощью функции «Найти и заменить»:

Выберите все ячейки, в которых вы хотите удалить или заменить возврат каретки.
Нажмите Ctrl+H, чтобы открыть Диалоговое окно «Найти и заменить».
в Найти то, что поле введите Ctrl+J. Он будет выглядеть пустым, но вы увидите крошечную точку.
в Заменить введите любое значение для замены возврата каретки. Обычно это пробел, чтобы избежать случайного соединения двух слов. Если все, что вам нужно, это удалить разрывы строк, оставьте поле «Заменить на» пустым.
нажмите Заменить все кнопку и наслаждайтесь результатом!

Удалить разрывы строк с помощью формул Excel

Плюсы: вы можете использовать цепочку формул / вложенные формулы для сложной обработки текста ячейки. Например, можно удалить возврат каретки, а затем устранить лишние начальные и конечные пробелы, а также пробелы между словами.

Или вам может понадобиться удалить возврат каретки, чтобы использовать ваш текст в качестве аргумента другой функции без изменения исходных ячеек. Например, если вы хотите иметь возможность использовать результат в качестве аргумента функции =lookup().

Минусы: вам нужно будет создать вспомогательный столбец и выполнить множество дополнительных действий.

Добавьте вспомогательный столбец в конец ваших данных. Вы можете назвать его “1 линия”.
В первой ячейке вспомогательного столбца (С2), введите формулу для удаления/замены разрывов строк. Здесь вы можете увидеть несколько полезных формул на разные случаи жизни:
- Обработка комбинаций возврата каретки/перевода строки как в Windows, так и в UNIX.
  =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2,СИМВОЛ(13),””),СИМВОЛ(10),””)
- Следующая формула поможет вам заменить разрыв строки любым другим символом (запятая+пробел). В этом случае строки не будут соединяться и не будут появляться лишние пробелы.
  =ОТРЕЗАТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2,СИМВОЛ(13),””),СИМВОЛ(10),”, “)
- Если вы хотите удалить из текста все непечатаемые символы, включая разрывы строк:
  =ЧИСТЫЙ(B2)
Скопируйте формулу в другие ячейки столбца.
Необязательновы можете заменить исходный столбец на тот, в котором были удалены разрывы строк:
- Выбрать все ячейки в столбце С и нажмите Ctrl + C, чтобы скопировать данные в буфер обмена.
- Теперь выберите ячейку B2 и нажмите сочетание клавиш Shift + F10. Затем просто нажмите В.
- Удалите вспомогательный столбец.

Макрос VBA, чтобы избавиться от разрывов строк

Плюсы: Созданный один раз, его можно повторно использовать в любой рабочей книге.

Минусы: вам необходимо иметь базовые знания VBA.

Макрос VBA из приведенного ниже примера удаляет возврат каретки из всех ячеек на открытом в данный момент рабочем листе (активный рабочий лист).

Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Для каждого MyRange в ActiveSheet.UsedRange Если 0 < InStr(MyRange, Chr(10)) Then MyRange = Replace(MyRange, Chr(10), " ") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Если вы не очень хорошо знаете VBA, см. Как вставить и запустить код VBA в Excel.

Удаление возврата каретки с помощью Text Toolkit

Если вы счастливый пользователь нашего Text Toolkit или Ultimate Suite for Excel, то вам не нужно тратить время ни на одну из вышеперечисленных манипуляций. Все, что нужно, это 3 быстрых шага:

Выберите одну или несколько ячеек, в которых вы хотите удалить разрывы строк.
На ленте Excel перейдите к Данные об аблебитах вкладка > Текст группу и нажмите кнопку Конвертировать кнопка.
На Преобразовать текст выберите переключатель «Преобразовать разрыв строки в», введите в поле «замещающий» символ и щелкните Конвертировать.

В нашем примере мы заменяем каждый разрыв строки пробелом, поэтому вы помещаете курсор мыши в поле и нажимаете клавишу Enter:

В результате у вас будет аккуратно организованная таблица с однострочными адресами:

Если вам интересно попробовать этот и еще 60 инструментов для экономии времени в Excel, вы можете загрузить пробную версию нашего Ultimate Suite. Вы будете поражены, обнаружив в несколько кликов решения для самых сложных и утомительных задач в Excel!