Как убрать пробел в конце ячейки в таблице Excel
Иногда у пользователя Excel появляется проблема: в начале и конце ячейки показываются ненужные пробелы или появляются непонятные расстояния между словами. Их основная проблема в том, что они нередко незаметны для человека. Поэтому часто он ничего не подозревает, особенно если глаз достаточно замыленный большими объемами данных. Проблема еще в том, что не существует метода обнаружить визуально наличие лишних пробелов, находящихся в конце ячеек при режиме отображения «часть текста». Сегодня мы детально разберемся в том, что можно сделать для того, чтобы убрать все пробелы.
Как убрать все пробелы в конце ячеек при помощи «Найти и заменить»
Этот метод можно использовать в некоторых ситуациях, потому что для него характерна особая быстрота и простота.
Удаление двойных интервалов
Если в начале или конце ячейки стоит пробел, не рекомендуется пользоваться этим способом. Все потому, что один из них все равно будет. Тем не менее, рассмотрим, как пользоваться этим методом для того, чтобы убрать двойные пробелы:
После того, как мы введем все необходимые данные, нужно просто нажать на кнопку «Заменить все». А вот нажимать на кнопку «Заменить» не рекомендуется, потому что она заменяет только одну ошибку, и ее нужно нажимать много раз для исправления.
Бывают и более сложные ситуации. Например, у пользователя в ячейке три интервала, поэтому при выполнении в точности всех описанных выше действий останется два, поскольку описанный ранее метод убирает лишь один пропуск. Поэтому рекомендуется выполнить эту операцию несколько раз, чтобы оставшиеся пробелы были убраны.
Вы можете сами понять недостатки этого метода. Перед некоторыми словами начальный пробел остается. Точно так же некоторые конечные пробелы остались видны, просто их нельзя увидеть на глаз. Поэтому если выражение текстовое в ячейке, использовать этот способ нежелательно.
Удаление всех пробелов в тексте
Может понадобиться и удаление всех пробелов. Если это надо сделать, то нужно выполнить такие шаги:
Этот способ позволяет удалить все пробелы, которые есть в ячейке. Недостаток также понятен. С помощью данного метода можно удалить и нужные пробелы, которые ставятся между словами. Поэтому перед тем, как его использовать, нужно удостовериться, что все ячейки в диапазоне содержат не больше одного слова. По этой причине данный метод лучше всего подходит для числовых операций.
В ряде случаев могут возникать ненужные пробелы между разрядами. Очень часто это происходит, когда пользователь пытается импортировать информацию из другой программы. Также ячейка может быть в текстовом формате. На данном этапе это не является проблемой, а потом мы разберемся, что делать для того, чтобы изменить формат на числовой.
Как удалить пробелы в ячейках при помощи формулы СЖПРОБЕЛЫ
В скобках надо указать ячейку, из какой мы будем удалять пробелы. Так, как видно на следующей картинке, этот оператор смог легко удалить все пробелы в начале и конце строки, записанной в ячейке, а также – ненужные интервалы внутри строки.
Далее необходимо совершить замену тех значений, которые содержатся в первоначальной колонке, на обновленные. Самый простой метод, как сделать это – «Специальная вставка». Там нужно выбрать пункт «Значения».
Как удалить пробелы в начале и в конце ячейки формулами
Что касается исключительно пробелов, стоящих в начале и конце, то есть специальные формулы, с использованием которых можно их удалить. На этом скриншоте отчетливо видно формулу, с использованием которой мы достигли этой цели.
Так как вы поняли из описанного ранее, функция СЖПРОБЕЛЫ не подходит для удаления пробелов лишь в начале и конце строки. Чтобы оставить нетронутыми срединные пробелы, формула будет значительно сложнее и включает в себя сразу несколько операторов, как мы видим из скриншота выше.
Работает оно следующим образом: оно определяет, в каком месте находится первый символ текстовой строки. После этого данное число принимается в качестве аргумента функцией ПСТР, которая затем передает строку полностью, начиная с первой позиции.
В свою очередь, оператор ДЛСТР определяет то, насколько большой является определенная строка. После того, как мы ввели эту формулу, проверяем результат. Если пробелы исчезли, то мы все сделали правильно. Только в качестве финального этапа необходимо заменить первоначальный текст на те значения, которые мы получили выше.
Если же нам необходимо только конечные пробелы убрать, то нам надо использовать еще более сложную формулу: =ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100))).
Важно: эта формула должна вводиться в качестве формулы массива. То есть, необходимо использовать комбинацию клавиш Ctrl + Shift + Enter. У нас не очень хорошо удалось выровнять по правому краю значение, находящееся в первой колонке, потому что количество пробелов в конце каждой строки было различным. Во втором столбце мы видим, что у нас получилось решить эту проблему и текст был красиво расположен.
Как удалить разрыв строки и непечатаемые символы
В ходе переноса информации с других программ нередко появляются непечатаемые символы, которые выглядят, как пробел или же немного по-другому, но они занимают место в ячейке. Это разнообразные возвраты каретки, переводы строк, табуляция и так далее.
Описанная ранее функция СЖПРОБЕЛЫ прекрасно справляется с тем, чтобы убрать из ячейки пробелы, но она не подходит для очистки ее от непечатаемых символов. Фактически она нужна только для того, чтобы убирать символ с кодом 32 в ASCII таблице 7-битного разряда. Именно так кодируется этот символ. Но есть и значения с другими кодами, для которых нужно использовать функцию ПЕЧСИМВ. В английской локализации программы она называется CLEAN. Как мы можем понять из названия, с помощью этого оператора можно очистить ячейку от всевозможного хлама и удалять первые 32 непечатаемые символы.
Допустим, нам нужно убрать и пробелы, и непечатаемые символы в ячейке A2. В этом случае наша задача – использовать сначала формулу ПЕЧСИМВ для того, чтобы убрать непечатные знаки, после чего передать эту строку функции СЖПРОБЕЛЫ, которая убирает оставшиеся пробелы. Результат использования этой комбинации экселевских операторов мы видим на скриншоте.
По сути, каждый раз, когда с использованием описанного выше метода вы убираете разрывы строки, слова, разделенные ими, склеиваются. Как же исправить эту ситуацию?
Как видим, теперь проблема с почтовым адресом оказалась успешно решенной.
Удаление неразрывных пробелов
Очень часто пользователь, который использует функцию СЖПРОБЕЛЫ или ПЕЧСИМВ обнаруживает, что они не срабатывают. Все потому, что есть еще один непечатаемый символ, который обозначает неразрывные пробелы. Очень часто такие символы появляются, когда пользователь пытается вставить информацию из интернета сразу в таблицу Excel.
Для этого необходимо заменить символ неразрывного пробела (он имеет код 160) на пробел с помощью функции ПОДСТАВИТЬ. После этого пробел убирается таким же образом, как и раньше. Если оказывается, что рабочий лист еще и содержит непечатаемые символы, то также необходимо использовать такую формулу: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);» «)))).
Как найти и удалить непечатаемый символ
А что делать, если мы уже знаем, какой конкретной непечатаемый символ нам мешает? Или же тот символ, который нам надо удалить, не находится в пределах первых 32 в таблице ASCII? В этом случае нужно использовать функцию ПОДСТАВИТЬ, чтобы заменить его на пробел.
Если же мы не знаем код непечатаемого символа, который нам необходимо убрать, то нужно воспользоваться функцией КОДСИМВ, который позволяет его получить. Причем нам не нужно использовать функции КОДСИМВ и ПОДСТАВИТЬ по отдельности, их можно сразу использовать в одной формуле, чтобы компьютер сначала определил код мешающего символа, а потом записал его в качестве аргумента ПОДСТАВИТЬ.
Сама структура использования функции в нашем случае довольно сложная. Для того, чтобы получить тот символ, который нам нужен, нам необходимо использовать операторы ЛЕВСИМВ, ПРАВСИМВ или ПСТР. Давайте более детально раскроем, что и в каких случаях нужно использовать:
Комбинации могут быть самыми причудливыми. Например, с помощью этой формулы пользователь может определить нужные коды символов, заменить их на пробелы и сразу же их уничтожить. И все это в одной формуле: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(127);» «);СИМВОЛ(160);» «)))).
Преобразование цифр с пробелами в число
Иногда может понадобиться удалить даже те пробелы, которые используются для разделения разрядов чисел. Конечно, они позволяют значительно упростить чтение больших чисел, но при этом они не дают формулам правильно работать. Поэтому нельзя использовать пробелы в качестве разделителей, нужно применять средства форматирования для этого. Для этого нужно воспользоваться меню «Формат ячейки», которое вызывается с помощью контекстного меню.
Как посчитать пробелы при помощи формул
Иногда же пользователю необходимо определить общее количество пробелов в формуле. Для этого нужно выполнить следующую формулу: =ДЛСТР(A3) — ДЛСТР(ПОДСТАВИТЬ(A3;» «;»»)). Если же нам нужно определить, сколько всего ненужных символов в ячейке, необходимо воспользоваться такой формулой: =ДЛСТР(A3)-ДЛСТР(СЖПРОБЕЛЫ(A3)).
Как видим, вовсе несложно удалить ненужные пробелы из таблицы, даже если они скрыты или находятся на нестандартной позиции. Что касается удаления одного или нескольких пробелов, которые находятся в начале или конце строчки, так это и вовсе тривиальная задача.
Коды ASCII символов
Управляющие символы (большинство непечатные; наиболее важные подсвечены жёлтым)
Печатные символы (стандартные)
| Символ | Dec | Hex | Oct | Описание |
|---|---|---|---|---|
| 32 | 20 | 040 | Пробел | |
| ! | 33 | 21 | 041 | Восклицательный знак |
| « | 34 | 22 | 042 | Кавычка (» в HTML) |
| # | 35 | 23 | 043 | Решётка (знак числа) |
| $ | 36 | 24 | 044 | Доллар |
| % | 37 | 25 | 045 | Проценты |
| & | 38 | 26 | 046 | Амперсанд |
| ‘ | 39 | 27 | 047 | Закрывающая одиночная кавычка (апостроф) |
| ( | 40 | 28 | 050 | Открывающая скобка |
| ) | 41 | 29 | 051 | Закрывающая скобка |
| * | 42 | 2a | 052 | Звёздочка, умножение |
| + | 43 | 2b | 053 | Плюс |
| , | 44 | 2c | 054 | Запятая |
| — | 45 | 2d | 055 | Дефис, минус |
| . | 46 | 2e | 056 | Точка |
| / | 47 | 2f | 057 | Наклонная черта (слеш, деление) |
| 0 | 48 | 30 | 060 | Ноль |
| 1 | 49 | 31 | 061 | Один |
| 2 | 50 | 32 | 062 | Два |
| 3 | 51 | 33 | 063 | Три |
| 4 | 52 | 34 | 064 | Четыре |
| 5 | 53 | 35 | 065 | Пять |
| 6 | 54 | 36 | 066 | Шесть |
| 7 | 55 | 37 | 067 | Семь |
| 8 | 56 | 38 | 070 | Восемь |
| 9 | 57 | 39 | 071 | Девять |
| : | 58 | 3a | 072 | Двоеточие |
| ; | 59 | 3b | 073 | Точка с запятой |
| 62 | 3e | 076 | Знак больше | |
| ? | 63 | 3f | 077 | Знак вопроса |
| @ | 64 | 40 | 100 | эт, собака |
| A | 65 | 41 | 101 | Заглавная A |
| B | 66 | 42 | 102 | Заглавная B |
| C | 67 | 43 | 103 | Заглавная C |
| D | 68 | 44 | 104 | Заглавная D |
| E | 69 | 45 | 105 | Заглавная E |
| F | 70 | 46 | 106 | Заглавная F |
| G | 71 | 47 | 107 | Заглавная G |
| H | 72 | 48 | 110 | Заглавная H |
| I | 73 | 49 | 111 | Заглавная I |
| J | 74 | 4a | 112 | Заглавная J |
| K | 75 | 4b | 113 | Заглавная K |
| L | 76 | 4c | 114 | Заглавная L |
| M | 77 | 4d | 115 | Заглавная M |
| N | 78 | 4e | 116 | Заглавная N |
| O | 79 | 4f | 117 | Заглавная O |
| P | 80 | 50 | 120 | Заглавная P |
| Q | 81 | 51 | 121 | Заглавная Q |
| R | 82 | 52 | 122 | Заглавная R |
| S | 83 | 53 | 123 | Заглавная S |
| T | 84 | 54 | 124 | Заглавная T |
| U | 85 | 55 | 125 | Заглавная U |
| V | 86 | 56 | 126 | Заглавная V |
| W | 87 | 57 | 127 | Заглавная W |
| X | 88 | 58 | 130 | Заглавная X |
| Y | 89 | 59 | 131 | Заглавная Y |
| Z | 90 | 5a | 132 | Заглавная Z |
| [ | 91 | 5b | 133 | Открывающая квадратная скобка |
| \ | 92 | 5c | 134 | Обратная наклонная черта (обратный слеш) |
| ] | 93 | 5d | 135 | Закрывающая квадратная скобка |
| ^ | 94 | 5e | 136 | Циркумфлекс, возведение в степень, знак вставки |
| _ | 95 | 5f | 137 | Нижнее подчёркивание |
| ` | 96 | 60 | 140 | Открывающая одиночная кавычка, гравис, знак ударения |
| a | 97 | 61 | 141 | Строчная a |
| b | 98 | 62 | 142 | Строчная b |
| c | 99 | 63 | 143 | Строчная c |
| d | 100 | 64 | 144 | Строчная d |
| e | 101 | 65 | 145 | Строчная e |
| f | 102 | 66 | 146 | Строчная f |
| g | 103 | 67 | 147 | Строчная g |
| h | 104 | 68 | 150 | Строчная h |
| i | 105 | 69 | 151 | Строчная i |
| j | 106 | 6a | 152 | Строчная j |
| k | 107 | 6b | 153 | Строчная k |
| l | 108 | 6c | 154 | Строчная l |
| m | 109 | 6d | 155 | Строчная m |
| n | 110 | 6e | 156 | Строчная n |
| o | 111 | 6f | 157 | Строчная o |
| p | 112 | 70 | 160 | Строчная p |
| q | 113 | 71 | 161 | Строчная q |
| r | 114 | 72 | 162 | Строчная r |
| s | 115 | 73 | 163 | Строчная s |
| t | 116 | 74 | 164 | Строчная t |
| u | 117 | 75 | 165 | Строчная u |
| v | 118 | 76 | 166 | Строчная v |
| w | 119 | 77 | 167 | Строчная w |
| x | 120 | 78 | 170 | Строчная x |
| y | 121 | 79 | 171 | Строчная y |
| z | 122 | 7a | 172 | Строчная z |
| < | 123 | 7b | 173 | Открывающая фигурная скобка |
| | | 124 | 7c | 174 | Вертикальная черта |
| > | 125 | 7d | 175 | Закрывающая фигурная скобка |
| 126 | 7e | 176 | Тильда (приблизительно) |
Расширенный набор символов (ANSI) в русской кодировке Win-1251
Статья Как в Excel сделать перенос текста в ячейке
Dragokas
Very kind Developer
Часто требуется внутри одной ячейки Excel сделать перенос текста на новую строку. То есть переместить текст по строкам внутри одной ячейки как указано на картинке. Если после ввода первой части текста просто нажать на клавишу ENTER, то курсор будет перенесен на следующую строку, но другую ячейку, а нам требуется перенос в этой же ячейке.
Как перенести текст на новую строку в Excel с помощью формулы
Иногда требуется сделать перенос строки не разово, а с помощью функций в Excel. Вот как в этом примере на рисунке. Мы вводим имя, фамилию и отчество и оно автоматически собирается в ячейке A6
Для начала нам необходимо сцепить текст в ячейках A1 и B1 (A1&B1), A2 и B2 (A2&B2), A3 и B3 (A3&B3)
После этого объединим все эти пары, но так же нам необходимо между этими парами поставить символ (код) переноса строки. Есть специальная таблица знаков (таблица есть в конце данной статьи), которые можно вывести в Excel с помощью специальной функции СИМВОЛ(число), где число это число от 1 до 255, определяющее определенный знак.
Например, если прописать =СИМВОЛ(169), то мы получим знак копирайта ©
Примечание: В VBA Excel перенос строки вводится с помощью функции Chr и выглядит как Chr(10)
Итак, в ячейке A6 пропишем формулу
В открывшемся окне во вкладке «Выравнивание» необходимо поставить галочку напротив «Переносить по словам» как указано на картинке, иначе перенос строк в Excel не будет корректно отображаться с помощью формул.
Как в Excel заменить знак переноса на другой символ и обратно с помощью формулы
Можно поменять символ перенос на любой другой знак, например на пробел, с помощью текстовой функции ПОДСТАВИТЬ в Excel
Рассмотрим на примере, что на картинке выше. Итак, в ячейке B1 прописываем функцию ПОДСТАВИТЬ:
Напоминаю, чтобы перенос строк правильно отражался, необходимо в свойствах ячеек, в разделе «Выравнивание» указать «Переносить по строкам».
Бывают случаи, когда формулы использовать неудобно и требуется сделать замену быстро. Для этого воспользуемся Поиском и Заменой. Выделяем наш текст и нажимаем CTRL+H, появится следующее окно.
После этого в поле «Заменить на» введите пробел или любой другой символ на который вам необходимо поменять и нажмите «Заменить» или «Заменить все».
Кстати, в Word это реализовано более наглядно.
Если вам необходимо поменять символ переноса строки на пробел, то в поле «Найти» вам необходимо указать специальный код «Разрыва строки», который обозначается как ^l
В поле «Заменить на:» необходимо сделать просто пробел и нажать на «Заменить» или «Заменить все».
Вы можете менять не только перенос строки, но и другие специальные символы, чтобы получить их соответствующий код, необходимо нажать на кнопку «Больше >>», «Специальные» и выбрать необходимый вам код. Напоминаю, что данная функция есть только в Word, в Excel эти символы не будут работать.
Как поменять перенос строки на пробел или наоборот в Excel с помощью VBA
Рассмотрим пример для выделенных ячеек. То есть мы выделяем требуемые ячейки и запускаем макрос
1. Меняем пробелы на переносы в выделенных ячейках с помощью VBA
Что означает символ 160 в моем исходном коде?
Я форматирую числа в строку, используя следующую строку формата «# #. ##», в какой-то момент мне нужно превратить эти строки чисел, например (1 234 567), в нечто вроде 1234567. Я пытаюсь удалить пустые символы, но обнаружили, что
по какой-то причине строка остается 1 234 567. После просмотра строки я обнаружил, что
значение [1] равно 160.
Мне было интересно, что означает значение 160?
8 ответов
Может быть, вы могли бы использовать регулярное выражение для замены этих пустых символов:
код символа 160 будет
Это быстрый (и достаточно читаемый) способ удаления любых символов, классифицированных как пробелы, с использованием Char.IsWhiteSpace :
Если вам нужны числа и десятичные разделители, должно быть что-то вроде этого:
Я бы предложил использовать версию с перегрузкой char :
Решение с расширенными методами:
И его можно использовать с этим кодом:
Не будет ли предпочтительным методом замены всех пустых символов (и именно это хотел сделать спрашивающий) на метод Regex, который Рубенс уже опубликовал?
или что предлагает Expresso:
Разница в том, что \ p
Преимущество использования RegEx состоит в том, что для замены нормальных пробелов необходима только одна команда, а не только неразрывный пробел, как объяснено в некоторых ответах выше.
Таблица символов в эксель
трюки • приёмы • решения
Каждый символ, вводимый вами и отображаемый на экране, имеет свой собственный числовой код. Например, код для прописной буквы А — это 65, а код для амперсанда (&) — 38. Эти коды присутствуют не только для алфавитных символов, вводимых вами с клавиатуры, но и для специальных символов, которые вы можете вывести на экран, используя их коды. Список всех символов и их кодов называется таблицей кодов ASCII.
Например, ASCII-код для символа © равен 169. Для вывода данного символа необходимо ввести на клавиатуре Alt+0169, используя цифровую клавиатуру для ввода цифр. Таблица ASCII содержит коды от 1 до 255, при этом первые 31 цифра используются для непечатаемых символов, таких как возврат каретки и перевод строки.
Функция СИМВОЛ()
Политический символ — это знак, выполняющий коммуникативную функцию между личностью и властью. Если исходить из концепции Т. Парсонса, согласно которой культура — это упорядоченная система символов, то можно сказать, что политическая культура — это организованная система символов, подробнее о символах политических партий смотрите на mmk-international.ru. Для того чтобы символ выполнял коммуникативную функцию, он должен иметь сходное значение для множества индивидов, его смысл должен быть, как минимум, интуитивно понятен определенному кругу людей. Кроме коммуникативной, символ обладает интегративной функцией — он способен сплачивать, объединять людей, группы, обеспечивать чувство единства.
Рис. 4.6. Построение таблицы ASCII кодов
Функция КОДСИМВ()
Отображает знак под номером 65 в наборе знаков компьютера.
Отображает знак под номером 33 в наборе знаков компьютера.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Get expert help now
Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.
В стандартном наборе шрифтов Windows есть много специальных символов, которые иногда весьма приятно и полезно было использовать на листе Excel:
где Код – число от 0 до 255, внутренний код символа Windows.
Заметьте, что в разных шрифтах символы с одним и тем же кодом могут выглядеть по-разному. Поэтому для вывода нужного значка необходимо знать имя шрифта и код символа в нем. Проще всего это определить с помощью небольшой таблицы, которую можно сделать себе за пару минут и использовать потом как справочник:
Для всех ячеек столбца В установлен шрифт Arial, для столбца C – шрифт Webdings и т.д. Так что одна и та же функция СИМВОЛ (CHAR) показывает нам символы с заданными кодами в каждом шрифте.
Обратите внимание, что первые три десятка кодов зарезервированы во всех шрифтах под системные нужды и в виде осмысленных символов не отображаются. А вот с остальными можно поиграться.
Вот так, например, можно визуализировать рост или падение продаж, используя символы с кодами 233 и 234 из шрифта Wingdings и простую проверку функцией ЕСЛИ (IF) :
Цвет стрелок можно задать дополнительно, используя условное форматирование.