Excel удалить символы справа

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

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

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

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

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

С помощью каких формул это можно сделать?

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

Чтобы получить содержимое ячейки, содержащей текст ячейки с удаленным первым или последним символом используют функцию выделения подстроки:ПСТР(текст;начальный символ;количество символов)

для определения количества символов используют функцию длины строки ДЛСТР(текст)

если текст “абвгдежзиклмн” находится в ячейке A1 то формулы будут такими:

  • строка с удаленным первым символом ячейки =ПСТР(A1;2;ДЛСТР(A1)-1) (результат бвгдежзиклмн)
  • строка с удаленным последним символом ячейки =ПСТР(A1;1;ДЛСТР(A1)-1) (результат абвгдежзиклм)
  • строка с удаленными первым и последним символами ячейки =ПСТР(A1;2;ДЛСТР(A1)-2) (результат бвгдежзиклм)
  • часть строки в 5 символов , начиная с 3-го символа =ПСТР(A1;3;5) (результат вгдеж)

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

например: вырезаем 3 символа, начиная с символа 3 и 4 символа, начиная с символа 7:

=ПСТР(A1;3;3)&ПСТР(A1;7;4) (результат вгджзик)

если нужно в результат включить строку, то она тоже добавляется символом &

=ПСТР(A1;3;3)&”. “&ПСТР(A1;7;4) (результат вгд. жзик)

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

Рассмотрим различные ситуации.

Удаление первого символа в ячейке

Для этого будем использовать формулу, состоящую из двух функций:

ПРАВСИМВ – возвращает заданное число символов справа.

ДЛСТР – длина строки.

ПРАВСИМВ имеет 2 аргумента:

1) Ссылка на ячейку, из которой нужно вернуть символы. Например, B3.

2) Количество символов. В нашем случае это будет вся строка за исключением первого символа. Поэтому пишем ДЛСТР(B3)-1.

Формула выглядит так:

ПРАВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).

Удаление последнего символа в ячейке

Если в Excel нужно удалить не первый, а последний символ в ячейке, то нужно использовать функцию ЛЕВСИМВ.

Эта функция работает также, как и ПРАВСИМВ – только возвращает не правые символы, а левые.

Формула для удаления последнего символа будет такой:

ЛЕВСИМВ(Ссылка на ячейку;ДЛСТР(Ссылка на ячейку)-1).

Удаление первого и последнего символа в ячейке

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

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

Также существует функция ПСТР, возвращающая заданное число символов в ячейке. Аргументы:

1) Ссылка на ячейку.

2) Начальная позиция. В нашем случае она равна 2, так как первый символ нас не интересует.

3) Количество знаков, которые нужно извлечь. Пишем ДЛСТР(B3)-2, так как нужно вернуть все символы из ячейки, кроме первого и последнего.

Формула будет такой:

Удаление заданного числа символов в ячейке справа или слева

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

В этом случае можно использовать всё те же функции, только в аргументах ЛЕВСИМВ и ПРАВСИМВ пишем не ДЛСТР(B3)-1, а ДЛСТР(B3)-n.

n – число символов, которые нужно удалить.

1) ПРАВСИМВ(B3;ДЛСТР(B3)-3). Удаляем 3 первых символа.

2) ЛЕВСИМВ(B3;ДЛСТР(B3)-5). Удаляем 5 символов с конца.

Удаление символов в середине ячейки

Для этого в Excel существует функция ЗАМЕНИТЬ. Она имеет 4 аргумента:

1) Ссылка на ячейку.

2) Начальная позиция. Например, если нам нужно удалять данные с 3 символа, то пишем “3”.

3) Число знаков. Сюда пишем число удаляемых символов. Например, “3”.

4) Новый текст. Так как нам нужно именно убрать данные символы, то пишем “” (две кавычки без пробела).

Таким образом, формула будет выглядеть так:

Она удаляет с 3 по 5 символ в ячейке.

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

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

Пусть у нас будет вот такая таблица:

Мы видим, что во втором столбце у нас уже выполнено задание – удален первый символ. Формула использована вот такая: ПРАВСИМВ(B3;ДЛСТР(B3­ )-1). То есть что мы с вами делаем: мы длину строки делаем меньше на один символ, при этом количество символов оставшееся программа считает справа, то есть лишним у нас оказывается левый, то есть первый символ, он и удаляется программой.

Несложно догадаться, что для удаления последнего символа нужно использовать немного другую формулу: ЛЕВСИМВ(B3;ДЛСТР(B3)-1) (применительно к этой же таблице. Для удаления первого и последнего символа

Читайте также:  Как в excel убрать слово страница

Таким же образом можно удалить несколько, а не один символ, справа или слева, просто пишем в формуле не (B3)-1, а, например, (B3)-3, и тогда удалится 3 символа справа или слева в соответствии с полной формулой.

Чтобы удалить символы в середине ячейки, используем другую формулу, применительно все к той же таблице, если мы хотим удалить три центральных символа, эта формула будет выглядеть так: ЗАМЕНИТЬ(B3;3;3;””), где В3, это ячейка с первоначальным текстом с полным набором символов, первая 3 – первый символ, который надо удалить, вторая 3 – количество символов, которые надо удалить, затем идут кавычки, так как это формула замены, то в них можно было бы указать символы, которые мы хотим поставить вместо удаленных, но мы хотим просто удалить три символа, поэтому в кавычках у нас ничего нет. Вот что получилось в итоге:

Источник: www.bolshoyvopros.ru

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

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

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

Как оставить заданное количество символов в начале строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ЛЕВСИМВ», которая возвращает заданное количество знаков с начала строки.

Как удалить заданное количество знаков в начале строки, остальные оставить?

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

Как оставить заданное количество символов в конце строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ПРАВСИМВ», которая возвращает заданное количество знаков с конца строки.

Как удалить заданное количество знаков в конце строки, остальные оставить?

Ввести в ячейку комбинацию стандартных функций Excel «ЛЕВСИМВ» и «ДЛСТР»

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

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

Надстройка для быстрой обрезки текста

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

1. обрезать заданное количество символов слева;

2. обрезать заданное количество символов справа;

3. обрезать значения ячеек до первого попавшегося заданного символа (сочетания символов);

4. обрезать значения ячеек до последнего попавшегося символа (сочетания символов).

В надстройку зашиты функции, аналогичные тем, которые описаны выше, что сокращает время на:

– выделение диапазона ячеек.

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

Разбиение текста по столбцам

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

Шаг1. Выбор формата исходных данных.

Шаг2. Установка нужной ширины полей.

Количество полей при этом не ограничено.

Шаг 3. Просмотр и получение результатов.

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

Видео по быстрой обрезке символов в Excel

Источник: macros-vba.ru

Excel удалить символы справа

убрать все символы справа до знака “/”

Подскажите как сделать чтоб строка
Ссылки удалены. Нарушение Правил форума

Спасибо за ответ.

убрать все символы справа до знака “/”

Подскажите как сделать чтоб строка
Ссылки удалены. Нарушение Правил форума

Спасибо за ответ. duttyology

Сообщение убрать все символы справа до знака “/”

Подскажите как сделать чтоб строка
Ссылки удалены. Нарушение Правил форума

Спасибо за ответ. Автор – duttyology
Дата добавления – 17.02.2017 в 13:50

AlexM Дата: Пятница, 17.02.2017, 14:11 | Сообщение № 2


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

duttyology Дата: Пятница, 17.02.2017, 14:58 | Сообщение № 3

ссылки придуманные были – не реальные
на форуме не смог найти как решить вопрос(
Прошу помощи

нужно получить
текст1/текст2/текст3/текст4/
текст1/текст22/текст33/текст44/
текст1/текст23/текст34/текст34/

ссылки придуманные были – не реальные
на форуме не смог найти как решить вопрос(
Прошу помощи

нужно получить
текст1/текст2/текст3/текст4/
текст1/текст22/текст33/текст44/
текст1/текст23/текст34/текст34/ duttyology

Сообщение ссылки придуманные были – не реальные
на форуме не смог найти как решить вопрос(
Прошу помощи

нужно получить
текст1/текст2/текст3/текст4/
текст1/текст22/текст33/текст44/
текст1/текст23/текст34/текст34/ Автор – duttyology
Дата добавления – 17.02.2017 в 14:58

Источник: www.excelworld.ru

Удаление текста до определенного знака

Ребят, подскажите пожалуйста:

есть много текста в ячейках, в каждом тексте есть разные фразы и разной длины, но всегда в каждом тексте есть символ “>” :
Зарубежный рэп, Lossless > 1234556
Зарубежный рэп, > 12321
Зарубежный > 212222323

1) как сделать так. чтобы удалялся весь текст, который идет до символа “>”, а лучше если до сочетания символов “>-”
2) и как удалить пробел в начале текста, в каждой ячейки?

20.05.2013, 00:48

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

Удаление символов в записи после определенного знака
Здравствуйте! Подскажите пожалуйста, мне необходимо убрать в записях одного из полей таблицы все.

Удаление до определенного знака из всей строки в textbox1.Text?
Удаление до определенного знака из всей строки в textbox1.Text ? Добавлено через 2 минуты К.

Вывод текста из документа на форму до определенного знака
Здравствуйте! Нужно написать цикл для вывода содержимого из текстового документа в textbox.

20.05.2013, 00:51 2

1. Данные – Текст по столбцам с разделителем > И тут отметить Пропустить первый столбец.
можно отметить разделителем –

20.05.2013, 01:08 [ТС] 3

1. Данные – Текст по столбцам с разделителем > И тут отметить Пропустить первый столбец.
можно отметить разделителем –

В идеале конечно, если кто знает формулу конкретно удаления пробела в начале текста, ну или просто удаление первого символа во всех ячейках?

20.05.2013, 01:14 4
20.05.2013, 22:19 [ТС] 5

эта формула удаляет все пробелы в тексте.

до : Bored Sti> ff – Ghetto Research (320 kbps)
после: BoredSti>ff-GhettoResearch(320kbps)

21.05.2013, 08:52 6
21.05.2013, 09:53 7
21.05.2013, 09:53
21.05.2013, 09:53

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Удаление всех символов в строке до или после определённого знака
Добрый день! Подскажите пожалуйста как из строки можно удалить (или получить) все символы до или.

Удаление определённого текста из txt с вводом текста через $text
Здравствуйте, у меня возник вопрос. Как удалить определённый текст из txt с вводом текста через.

Удаление текста до определенного слова
Здравствуйте. Помогите,пожалуйста написать следующий макрос: Имеется текст вида: Статья i. .

Удаление текста после определённого слова
Всем привет! У меня возник вопрос. Как удалить текст после определённого слова, к примеру есть.

Источник: www.cyberforum.ru

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

Получить слово до первого пробела достаточно просто:
=ПСТР( A1 ;1;НАЙТИ(” “; A1 )-1)
Но куда чаще сложности возникают с получением слова(символа), находящегося на определенной позиции между пробелом. Я беру в качестве примера пробел, но на самом деле это может быть абсолютно любой символ. Например, для получения второго слова(т.е. слова между первым пробелом и третьим), можно составить такую формулу:
=ПСТР( A1 ;НАЙТИ(” “; A1 )+1;НАЙТИ(” “; A1 ;НАЙТИ(” “; A1 )+1)-НАЙТИ(” “; A1 )-1)
На мой взгляд, выглядит несколько закручено, хотя все предельно просто:

  • НАЙТИ(” “; A1 )+1 – ищем позицию первого пробела;
  • НАЙТИ(” “;A1;НАЙТИ(” “; A1 )+1) – ищем позицию второго пробела и затем из этой позиции вычитаем позицию первого пробела( -НАЙТИ(” “; A1 ) ).

Но есть проблема – если второго пробела нет, то формула выдаст ошибку #ЗНАЧ!. Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(” “&ПОДСТАВИТЬ( A1 ;” “;ПОВТОР(” “;999));1;999*2);999);” “;””)
На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
она не нуждается в проверке на отсутствие пробелов;
изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.
Разберем самое главное: чтобы получить первое слово от начала строки, нужно в блоке 999*2 заменить 2 на 1:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(” “&ПОДСТАВИТЬ( A1 ;” “;ПОВТОР(” “;999));1;999*1);999);” “;””)
Чтобы получить 5-е – на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(” “&ПОДСТАВИТЬ( A1 ;” “;ПОВТОР(” “;999));1;999*5);999);” “;””)
И ТО, К ЧЕМУ ШЛИ – СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг пробелов будет меньше, чем указанное число – то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать *999 – в большинстве случаев получим как раз последнее слово.
Как это работает: при помощи функции ПОДСТАВИТЬ мы заменяем все пробелы в тексте на 999 пробелов(число может быть меньше 999, но не должно быть меньше длины исходной строки). Далее при помощи функции ПСТР мы выдергиваем первые 999 символов, помноженные на число, обозначающее необходимое нам слово(999*1 – первое). По сути только то количество слов, которое указано(в данном случае одно – 999*1). Затем функция ПРАВСИМВ возвращает нам только последнее слово – т.е. нужное нам. А далее та же ПОДСТАВИТЬ убирает лишние теперь пробелы, заменяя их все на пустую строку – “” .
Вроде бы достигли того, что нам нужно было. Но вдруг необходимо получить второе слово с конца строки? Как тогда быть? Считать пробелы? А если у нас их штук 50 хотя бы? Можно использовать некую модификацию приведенной выше формулы, но которая как раз возвращает слово с конца строки:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(” “&ПОДСТАВИТЬ( A1 ;” “;ПОВТОР(” “;999));999*1);1;999);” “;””)
Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова по пробелам, то лучше дополнить еще одной функцией – СЖПРОБЕЛЫ:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(” “&ПОДСТАВИТЬ(СЖПРОБЕЛЫ( A1 );” “;ПОВТОР(” “;999));1;999*1);999);” “;””)
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(” “&ПОДСТАВИТЬ(СЖПРОБЕЛЫ( A1 );” “;ПОВТОР(” “;999));999*1);1;999);” “;””)

Так же можно “вытянуть” определенное количество слов:
=СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(” “&ПОДСТАВИТЬ(A1;” “;ПОВТОР(” “;999));999*3);1;999*2))
3 — третье слово с конца строки.
2 — количество слов.

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

Тогда для получения второй строки( ТЦ Таганка и ТЦ Опус ) можно применить такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(СИМВОЛ(10)&ПОДСТАВИТЬ( C2 ;СИМВОЛ(10);ПОВТОР(СИМВОЛ(10);999));1;999*2);999);СИМВОЛ(10);””)
СИМВОЛ(10) здесь означает перенос строки. Обычно эти переносы делаются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужное место строки и нажимаем Alt+Enter.
я для получения месяцев( Август 2015 г. и Сентябрь 2015 г. ) – такую:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(“/”&ПОДСТАВИТЬ( C2 ;”/”;ПОВТОР(“/”;999));999*1);1;999);”/”;””)
Если лень прописывать внутри формулы один символ несколько раз, его можно записать в ячейку(скажем, G1 ) и в формуле указать ссылку на эту ячейку:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ( G1 &ПОДСТАВИТЬ( C2 ; G1 ;ПОВТОР( G1 ;999));999*1);1;999); G1 ;””)
Теперь для изменения символа надо будет изменить его один раз в ячейке и формула “вытащит” нужное слово/строку, опираясь именно на этот символ.

Tips_All_WordFromEndString.xls (29,5 KiB, 2 579 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru

VBA Excel. Функции Left, Mid, Right (вырезать часть строки)

Извлечение (вырезание) части строки с помощью кода VBA Excel из значения ячейки или переменной. Функции Left, Mid и Right, их синтаксис и аргументы. Пример.

Функция Left

Эта функция извлекает левую часть строки с заданным количеством символов.

Синтаксис функции Left:

Left(строка, длина)

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается левая часть;
  • длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.

Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.

Функция Mid

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

Синтаксис функции Mid:

Mid(строка, начало, [длина])

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается часть строки;
  • начало — обязательный аргумент: числовое выражение, указывающее положение символа в строке, с которого начинается извлекаемая часть;
  • длина — необязательный аргумент: числовое выражение, указывающее количество вырезаемых символов.

Если аргумент «начало» больше, чем количество символов в строке, функция Mid возвращает пустую строку. Если аргумент «длина» опущен или его значение превышает количество символов в строке, начиная с начального, возвращаются все символы от начальной позиции до конца строки.

Функция Right

Эта функция извлекает правую часть строки с заданным количеством символов.

Синтаксис функции Right:

Right(строка, длина)

  • строка — обязательный аргумент: строковое выражение, из значения которого вырезается правая часть;
  • длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.

Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.

Пример

В этом примере будем использовать все три представленные выше функции для извлечения из ФИО его составных частей. Для этого запишем в ячейку «A1» строку «Иванов Сидор Петрович», из которой вырежем отдельные компоненты и запишем их в ячейки «A2:A4».

Источник: vremya-ne-zhdet.ru

Добавить комментарий

Adblock
detector