Как удалить текст до или после определенного символа в excel

Способ 4: Изменение формата ячейки

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

Выделите требуемые ячейки в таблице с зажатой левой кнопкой мыши.

Нажмите по ним ПКМ и в появившемся контекстном меню отыщите пункт «Формат ячеек».

Через панель слева перейдите на формат «Числовой» или тот, который изначально был задан для выбранных ячеек.

Снимите галочку с пункта «Разделитель групп разрядов» и сохраните изменения.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.

Наши специалисты постараются ответить максимально быстро.

Как удалить все пробелы

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

Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем (“”):

Или вы можете просто ввести его (” “) в формуле, например:

Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.

4 ответов

Я боюсь Апостроф ‘ является специальным символом для Excel, когда он появляется как первый символ в ячейке, как вы нашли. Он говорит Excel рассматривать остальную часть строки как текст, так что вы можете ввести что-то вроде ‘34.2 в ячейке, и он будет рассматривать его как строку вместо числа (для форматирования и так далее).

Я предлагаю сделать что-то похожее на то, что вы предложили, за исключением того, что там, где вы помещаете его в Excel, проверьте первый символ и добавьте дополнительный ‘ Если там уже есть один.

кроме того, вы можете добавить Апостроф ко всем значениям — если вы хотите, чтобы все они были текстом. Таким образом, вам не понадобится дополнительная проверка первого символа.

посмотреть PrefixCharacter свойства Range объект, который соответствует этой ячейке

если TransitionNavigKeys свойства False , этот символ префикса будет ‘ для текстовой метки, или пустой. Если TransitionNavigKeys недвижимость True , этот персонаж будет для left-justified label, » for a правая метка, ^ для a центрированная метка, для повторения этикетка или пустая.

на TransitionNavigKeys часть относится к совместимости Lotus 1-2-3, поэтому она, скорее всего, будет False

ответ на основе статьи на сайте:

(предупреждение: может появиться немного раздражающее всплывающее окно)

редактировать: на самом деле это, вероятно, не будет никакой пользы, потому что PrefixCharacter доступно только для чтения

edit2: я был прав первый раз. PrefixCharacter заполняется только в том случае, если значение, добавленное в ячейку, начинается с » so just read back PrefixCharacter плюс Value и сцепления. Пока TransitionNavigKeys и False , что составляет

попробовать targetcell.Value вместо. .Formula — это формула, видимая в строке формул, в то время как .Value — вычисляемое значение ячейки.

Итак, я предполагаю, что вы бы использовать .Formula в исходном коде, а также. Изменение этого должно сработать.

EDIT: хорошо, это не сработало (смущенно).

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

вы можете попробовать предварительно ожидать одну цитату в текстовых полях («»+dbField ) в вашем запросе, чтобы для полей со встроенными одинарными кавычками ваш запрос возвращал:

который при размещении в ячейке Excel преобразуется в:

для символов, которые не были в кавычках, вы получите:

который при размещении в ячейке Excel преобразуется в:

Удаление пробелов только слева или справа

Если нужно произвести удаление пробелов только слева или справа (иначе называются начальные и конечные), нужно выполнить следующие действия:

Убрать пробел слева, в начале строки. Чтобы все сработало нужно ввести в соседнюю ячейку следующую формулу: =ПСТР(A1;НАЙТИ(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1))). Если английская версия, то тогда: =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1)). Где А1 целевое значение, которое нужно обработать.
Убрать пробел справа, в конце строки. В соседнюю ячейку надо ввести следующую формулу: =ЛЕВСИМВ(Ф1;МАКС((ПСТР(A1&ПОВТОР(“ “;99);СТРОКА(А1:А99);1);» «)* СТРОКА(A1:A99))). В англоязычной версии: =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99))). Где А1 целевое значение, которое нужно обработать.

Как быстро удалить первый символ из всех ячеек в Excel

Часто бывает такое, что нужно удалить один или несколько символов из начала каждой ячейки.

Допустим у нас есть первый столбик, в котором лежат значения буква+цифры, нам нужно обрезать их таким образом, чтобы получились только цифры. Как на картинке ниже:

На самом деле, есть несколько вариантов, как можно это сделать.

Итак, давайте начнем!

С помощью функции ЗАМЕНА

Самый распространенный способ — использовать функцию ЗАМЕНА. Она довольно простая и в то же время полезная.

Для указанного выше примера формула функции принимает такой вид:

Просто заменяет первый символ в строке на пустое место.

С помощью неё можно удалить любое количество символов с начала строки, например удалить два символа можно так:

Копируем из ячейки все, кроме первого символа

Можно сделать и так, в этом случае формула будет немного сложней, но результата мы добьемся.

Разница в том, что в первом случае мы удаляли первый символ из строки, а в этом случае — мы просто разделяем строку и переносим нужную её часть в другую ячейку.

Допустим, у нас тот же столбик, который нужно обработать:

В этой ситуации формула принимает такой вид:

Функция ДЛСТР получает длину всей строки, а далее из длины убавляется 1 (потому что 1 символ мы хотим «пропустить») и все это отдается в обработку функции ПРАВСИМВ.

С помощью функции «Текст по столбцам»

Прошлые методы простые, но неудобны тем, что для них нужен отдельный столбец, так сказать, «технический» (для расчетов).

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

Допустим, у нас тот же столбик:

Выделите ячейки и щелкните на «Данные»;

Далее «Текст по столбцам»;

В открывшемся окошке:

В первом шаге используйте вторую опцию (как на картинке);

Во втором шаге установите стрелку на том делении, сколько символов вы хотите «отрезать»;

И в последнем шаге, выберите «пропустить столбец»;

Подтвердите.

Таким образом, первый символ был удален.

Как это работает?

Довольно просто: вы указали функции сколько символов нужно «отрезать» от начала строки, а также вы указали пропустить столбец. Это значит что данные, после обработки, будут записаны в тот же столбик, где они изначально были.

С помощью автозаполнения

Эта функция — одна из самых удобных функций в Excel. Она автоматически пытается угадать значения для следующих ячеек, если вы заполнили первую (одну или две).

Давайте посмотрим как это работает.

Допустим, у нас тот же столбик:

Воспользуемся этой функцией

В следующем столбике, впишите значение, которое должно быть после преобразования (в нашем случае 271);

А также, по аналогии, укажите значение для второй ячейки. На самом деле, функция автозаполнения уже поняла логику и предлагает нам заполнить всю оставшуюся часть;

Теперь выделите обе ячейки с обработанными данными;

Наведите курсор на правую нижнюю часть выделения и потяните её вниз;

Готово!

Просто не правда ли?

Это удобная функция, но с ней нужно быть осторожным. Если она неправильно поймет логику вашего заполнения — могут быть ошибки.

С помощью Visual Basic

Ну и как обычно, напоследок, рассмотрим как можно сделать это с помощью Visual Basic.

Я дам вам код функции для такого рода задачи, вы сможете модифицировать его.

Также, как и в случае использования функции «Текст по столбцам» в этом методе нам не нужен дополнительный столбик для расчетов.

Обратите внимание, что если строк, которые нужно обработать у вас очень много, выполнение этого кода может замедлить работу компьютера на какое-то время. Этот код удаляет первый символ из каждой ячейки:

Этот код удаляет первый символ из каждой ячейки:

Как его использовать?

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

Откройте просмотр кода (правой кнопкой на имя листа -> «Просмотр кода»);

Далее щелкните на «View» -> «Immediate Window» (или CTRL + G);

Поместите в окно наш код;

Обязательно наведите мышь в конец кода;

И просто подтвердите (нажав ENTER);

Готово, первый символ удален.

Что же делает этот код?

Итак, здесь используется цикл, который копирует и перезаписывает каждую ячейку отдельно.

В общем то и все! Мы рассмотрели несколько удобных и практичных методов удаления первого символа из каждой ячейки.

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

Отрезать (удалить) лишний текст слева или справа в ячейке «Excel».

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

Функции, которая отрезает лишнее количество символов в ячейке, найти не удается, но существует система из двух функций, которые можно применить. Это система из функции ЛЕВСИМВ (или ПРАВСИМВ) и функции ДЛСТР (длина строки).

Рассмотрим их применение.

Вначале находим количество символов в тексте при помощи функции ДЛСТР. Для этого запускаем менеджер функций, выбираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество символов.

Зная длину лишнего текста — 6 знаков в индексе, вычитаем их из общего числа символов посчитанных функцией ДЛСТР и получаем то количество символов, которое должно остаться в тексте, когда удалим индекс.

Полученное значение – количество оставляемых в тексте знаков, вписываем в функцию ЛЕВСИМВ или ПРАВСИМВ. Если нужно удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Нажимаем «Enter». Функция ЛЕВСИМВ или ПРАВСИМВ присваивает ячейке нужное нам количество символов из текста в новую ячейку, исключая ненужный нам индекс.

Для уменьшения количества ячеек с расчетами эти функции можно записать в систему следующего вида:

ПРАВСИМВ(…*¹;(ДЛСТР(…*¹)-6)).

Где …*¹- адрес ячейки, из которой берем текст.

Как отрезать (удалить) лишний текст слева или справа в ячейке «Эксель».: 24 комментария

Отличная статья. Помогла отнять лишние знаки из диапазона значений с различной длинной текста. Например: у меня были значения «100.00» «95.00» 15.00″ «5.00» и т.д. Решил вопрос формулой: =ЛЕВСИМВ(A1;(ДЛСТР(A1)-3))

готовая формула только подставить строки и кол-во символов) спасибо!

Нужна помощь. Как удалить в каждой строчке все символы ПОСЛЕ указанного знака/знаков?

Добрый день. По аналогии с удалением слов описанном в статье «Копировать, убрать/удалить первое слово или несколько первых слов в ячейке Excel » только вместо пробелов подставить знак(и), о котором вы писали. ссылка — http://ruexcel.ru/slova/

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

Здравствуйте! Есть колонка в Екселе с текстовыми значениями типа 00001, 00001А, 00624/1, 00682А, 01082А, 00240-1 и т.п. Требуется во всех значениях убрать впереди все нули. Но только впереди. Не подскажете, как это сделать? Спасибо.

Добрый день! Большая таблица, в одном из столбцов указаны даты: 1995, 2006г., 1915, 1934г., 1946г., 2017, 2015 и т.д. Как убрать после цифр года символ «г.» во всех ячейках столбца, чтобы остались одни цифры года автоматически? Благодарю заранее.

Добрый день. Выделите ячейки, в которых нужно убрать лишние символы. Нажмите ctrl+H В поле «Найти:» введите «г.», в поле «Заменить…» не вводите ничего. Нажмите «заменить все»

большое спасибо. очень помогли! Просто и быстро)

Здравствуйте! Есть колонка в Excel с текстовыми значениями типа 38/18, 35/35, 32/25 и т.п. Требуется во всех значениях убрать впереди цифры и дробь. Точно также, только убрать сзади. Не подскажете, как это сделать? Спасибо.

Добрый день. Если текст в ячейке H3, то: =ЛЕВСИМВ(H3; (ПОИСК(«/»;H3)-1)) или =ПРАВСИМВ(H3;(ДЛСТР(H3)-ПОИСК(«/»;H3))) зависит от того, какую часть текста (левую или правую) нужно оставить в ячейке.

Здравствуйте. Подскажите, как из строки формата: Вмк. У240кв. 20-70. 30м3. #39720. 15.40 поместить в отдельную ячейку цифру после символа #.

=ПРАВСИМВ(B2073;(ДЛСТР(B2073)-ПОИСК(«#»;B2073))), где B2073 — ячейка содержащая «Вмк. У240кв. 20-70. 30м3. #39720. 15.40»

Добрый день! Подскажите как текст в одной ячейке: Иванов Иван Иванович разбить на три ячейки: 1. Иванов 2. Иван 3. Иванович

Добрый день, подскажите, пожалуйста, как сократить количество текста в ячейках столбца на 15% с сохранением логического смысла?

Здравствуйте! У меня в столбике, в каждой строке, к примеру, по 15 символов (к примеру, 20.01.2020 17.45) а нужно сократить до 10 (чтобы осталась только дата) , но не в соседний столбец перенеся, как подразумевает функция ЛЕВСИМВ, а просто сократить в том же, не подскажете, как это сделать?

Здравствуйте! Есть столбец в Екселе со значениями типа 000010531, 000401325, 002000045, 000104573, т.п. Количество нулей разное. Требуется во всех значениях убрать спереди все нули, но только впереди. Не подскажете, как это сделать? Спасибо.

Форматирование ячеек.

Порой причиной ненужных пробелов в числах иногда является форматирование. Оно может быть уже установлено ранее или же перенесено вместе с данными из внешнего источника. Чтобы все исправить, нужно по правой кнопке мыши выбрать вкладку «Формат ячеек». Появится список настроек, нужно выбрать вкладку «Число», где и настроить наличие разделителей групп разрядов.

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

3 способа быстро убрать перенос строки в ячейках Excel–Как удалить пробелы в ячейках Excel–Функция СЖПРОБЕЛЫ — как пользоваться и примеры–Функция ПРАВСИМВ в Excel — примеры и советы.–Функция ЛЕВСИМВ в Excel. Примеры использования и советы.–Как использовать функцию ПСТР в Excel?–5 примеров с функцией ДЛСТР в Excel.–Как быстро сосчитать количество знаков в ячейке Excel–Как в Excel разделить текст из одной ячейки в несколько–

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

Как отобразить скрытые символы в Excel?

Как увидеть скрытые символы в Экселе?

Самый простой способ сделать это — просто изменить шрифт на шрифт, который имеет встроенный видимый глиф для пробела (или любой другой символ, который вам может понадобиться идентифицировать) …
CTRL+H заменяет все пробелы символом

Как убрать скрытые символы в Excel?

Удаление непечатаемых символов

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN).

Какая функция удаляет все непечатаемые знаки из текста?

Удаляет все непечатаемые знаки из текста. Функция ПЕЧСИМВ используется в том случае, когда текст, импортированный из другого приложения, содержит знаки, печать которых не возможна в данной операционной системе.

Как проверить непечатные символы?

Чтобы отобразить специальные непечатаемые символы, откройте вкладку Файл (File). В меню слева кликните по Параметры (Options). В левой части диалогового окна Параметры Word (Word Options) нажмите кнопку Экран (Display).

Как узнать код символа в Excel?

Функция КОДСИМВ() в EXCEL

Соответствие кодов символам можно посмотреть через меню Вставка/ Текст/ Символ (код знака указан в нижнем правом углу). Формула =КОДСИМВ(«А») вернет значение 192 (для русской буквы А), для английской буквы — КОДСИМВ() вернет 65.

Как увидеть пробелы в Excel?

Выполните команду Главная –> Условное форматирование –> Создать правило, чтобы открыть диалоговое окно Создание правила форматирования (рис. 3). В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек. В области Измените описание правила введите формулу: =D2СЖПРОБЕЛЫ(D2).

Как в Excel убрать текст и оставить только значения?

В нужном файле Excel выделить столбец, в котором нужно удалить числа.
Открыть вкладку Ёxcel → «Ячейки»→ «Редактировать» → «Оставить в ячейке только текст».

Как в Excel сделать замену символов?

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на главная > Найти & Выберите > Заменить.

В поле Найти введите текст или числа, которые нужно найти.
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Нажмите кнопку Заменитьили Заменить все.

Как избавиться от скрытых символов в тексте?

Вызываем окно поиска текста с помощью горячего сочетания «Ctrl+H», выбираем больше параметров, потом команда «Формат / шрифт» и устанавливаем значок поиска скрытого текста. В поле «Заменить на» ничего не вводим и просто нажимаем «Заменить все». В документе скрытый текст будет удален.

Как убрать из текста символ?

Самый простой способ удалить какой-либо знак из текста документа Word – перейти на вкладку «Главная», в группе кнопок «Редактирование» кликнуть по кнопке «Заменить», в появившемся диалоговом окне написать удаляемый символ в поле «Найти», поле «Заменить на» при этом оставить пустым и нажать кнопку «Заменить» либо « …

Как удалить все непечатаемые символы в Word?

Чтобы отключить их отображение, выполните следующие действия:

Откройте меню “Файл” и выберите пункт “Параметры”. …
Перейдите в раздел “Экран” и найдите там пункт “Всегда показывать эти знаки форматирования на экране”.
Уберите все галочки кроме “Привязка объектов”.

Что такое служебные символы форматирования как отобразить в тексте Непечатаемые символы?

Непечатаемые символы помогают редактировать документ. Они обозначают присутствие разных типов форматирования, но не выводятся на печать при печати документа, даже если отображаются на экране. на вкладке Главная верхней панели инструментов. Вы также можете использовать сочетание клавиш Ctrl+Shift+Num8.

Какие символы называются Непечатаемыми и для чего они используются?

Самый простой непечатаемый символ – это пробел между словами. Пробел между словами получаем, когда нажимаем самую длинную клавишу на клавиатуре, чтобы отделить одно слово от другого. Ещё один непечатаемый символ – это символ абзаца, правильнее – символ конца абзаца «¶». Этот символ получается при нажатии клавиши Enter.

Что относится к свойствам символов?

К основным свойствам символов относятся:

рисунок,
кегль,
начертание,
ширина,
трекинг,
кернинг,
цвет символа,
цвет подложки символа.

Как удалить пробелы в Excel

В этом уроке я покажу вам два способа удаления пробелов в Excel.

Использование функции TRIM.
Использование поиска и замены.

Использование функции TRIM

Функция Excel TRIM удаляет начальные и конечные пробелы, а также двойные пробелы между текстовыми строками.

Например, в приведенном выше примере, чтобы удалить пробелы из всего списка для имен (в A2: A7), используйте следующую формулу в ячейке C1 и перетащите ее вниз для всех имен:

= ОБРЕЗАТЬ (A2)

Функция Excel TRIM мгновенно удалит все начальные и конечные пробелы в ячейке.

Когда у вас есть очищенные данные, скопируйте их и вставьте как значения вместо исходных данных.

Эта функция также полезна, если у вас есть более одного символа пробела между словами. Он удалит лишние пробелы, так что в результате между словами всегда будет один пробел.

Функция Excel TRIM хорошо справляется с удалением пробелов в Excel, однако она не работает, если в вашем наборе данных есть непечатаемые символы (например, разрывы строк). Чтобы удалить непечатаемые символы, вы можете использовать комбинацию функций ОБРЕЗАТЬ и ОЧИСТИТЬ.

Если у вас есть текст в ячейке A1, из которого вы хотите удалить пробелы, используйте следующую формулу:

= ОБРЕЗАТЬ (ЧИСТЫЙ (A1))

Непечатаемые символы также могут быть результатом = CHAR (160), который не может быть удален с помощью формулы CLEAN. Итак, если вы хотите быть абсолютно уверены, что у вас есть все лишние пробелы и непечатаемые символы, используйте следующую формулу:

= ОБРЕЗАТЬ (ОЧИСТИТЬ (ПОДСТАВИТЬ (A1; СИМВОЛ (160); "")))

Удалите лишние пробелы в Excel с помощью НАЙТИ и ЗАМЕНИТЬ

Вы можете удалить пробелы в Excel с помощью функции «Найти и заменить».

Это более быстрый метод, который может быть полезен в следующих ситуациях:

Когда вы хотите удалить двойные пробелы.
Если вы хотите удалить все символы пробела.

Удаление двойных пробелов

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

Вот как это сделать:

Выделите ячейки, из которых вы хотите удалить двойные пробелы.
Перейдите на главную -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш — Control + H).
В диалоговом окне «Найти и заменить» введите:
- Найдите что: Двойное пространство.
- Заменить на: Single Space.
Нажмите «Заменить все».

Это заменит все двойные пробелы одним пробелом.

Обратите внимание, что это удалит только двойные пробелы. Если у вас есть три пробела между двумя словами, это приведет к двум пробелам (один будет удален)

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

Удаление одиночных пространств

Чтобы удалить все пробелы в наборе данных, выполните следующие действия:

Выделите ячейки, из которых вы хотите удалить пробел.
Перейдите на главную -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш — Control + H).
В диалоговом окне «Найти и заменить» введите:
- Что: единое пространство.
- Заменить на: Оставьте это поле пустым.
Нажмите «Заменить все».

Это удалит все пробелы в выбранном наборе данных.

Обратите внимание, что в этом случае, даже если между двумя текстовыми строками или числами имеется более одного символа пробела, все они будут удалены

Удалить разрывы строк

Вы также можете использовать «Найти и заменить», чтобы быстро удалить разрывы строк.