Как убрать запятую в excel в конце ячейки

Обновлено: 07.07.2024

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

лишние пробелы перед, после или между словами (для красоты!)
ненужные символы ("г." перед названием города)
невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

Давайте рассмотрим способы избавления от такого "мусора".

Замена

"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:

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

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

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

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

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем

С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):

Удаление апострофов в начале ячеек

Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)

Иногда при импорте данных из других файлов в приложение для работы с электронными таблицами Excel вы можете обнаружить, что в конце строк появляются посторонние символы, например запятые. В этом руководстве объясняется, как использовать комбинацию функций IF, RIGHT, LEFT и LEN для удаления конечной запятой в ячейке в Excel.

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

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

Общая формула

аргументы

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

Как пользоваться этой формулой?

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

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

=IF(RIGHT(B3,1)=",",LEFT(B3,LEN(B3)-1),B3)

Тогда вы увидите, что все запятые в конце строк немедленно удаляются.

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

Как работает эта формула?

=IF(RIGHT(B3,1)=",",LEFT(B3,LEN(B3)-1),B3)

1. LEFT(B3,LEN(B3)-1)

Функция LEN вычисляет общую длину текстовой строки «Excel, handy, addin» и возвращает результат как 20;
Затем функция LEFT извлекает 19 (20-1 = 19) символов из левой части текстовой строки «Excel, handy, addin» и получает результат «Excel, handy, addin».

2. IF(RIGHT("Excel, handy, addin,",1)=",","Excel, handy, addin","Excel, handy, addin,")

Здесь определяется, является ли крайнее правое значение в текстовой строке «Excel, handy, addin» запятой. Если в конце строки обнаружена запятая, возвращается «Excel, handy, addin», в противном случае - исходная текстовая строка «Excel, handy, addin,».

Связанные функции

Функция ЕСЛИ в Excel
Функция Excel ЕСЛИ выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.

Функция Excel ВПРАВО
Функция Excel RIGHT извлекает определенное количество символов из правой части текстовой строки.

Функция ВЛЕВО в Excel
Функция Excel LEFT извлекает заданное количество символов из левой части предоставленной строки.

Функция ДЛСТР в Excel
Функция Excel LEN возвращает количество символов в текстовой строке.

Родственные формулы

Удалить первое или последнее слово из строки
В этом руководстве объясняется, как удалить только первое или последнее слово из текстовой строки в ячейке с формулами в Excel.

Удалить первые N символов из ячейки
В этом руководстве представлены две формулы, которые помогут вам легко удалить первые n символов из ячейки в Excel.

Удаление разрывов строк из ячеек в Excel
В этом руководстве представлены три формулы, которые помогут вам удалить разрывы строк (которые возникают при нажатии клавиш Alt + Enter в ячейке) из определенных ячеек в Excel.

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

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

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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

Удалите все запятые с помощью функции поиска и замены

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

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

2. Затем нажмите Найти и выбрать > Заменять под Главная , чтобы открыть Найти и заменить диалоговое окно. Смотрите скриншот:

Внимание: Вы также можете нажать Ctrl + H клавиши одновременно, чтобы открыть Найти и заменить диалоговое окно.

3. в Найти и заменить диалогового окна, нажмите Заменять вкладки введите запятую ",”В Найти то, что коробка, держи Заменить пустое поле и, наконец, нажмите Заменить все кнопка. Смотрите скриншот:

Затем все запятые в выбранном диапазоне немедленно удаляются.

Удалите все запятые с помощью Kutools for Excel

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

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выделите ячейки, в которых нужно удалить все запятые. Затем нажмите Kutools > Текст > Удалить символы.

2. в Удалить символы диалоговое окно, только отметьте На заказ поле, введите запятую в текстовое поле, а затем щелкните значок OK кнопка. Смотрите скриншот:

Теперь все запятые удалены.

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

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

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

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

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

Удалить запятую из чисел

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

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

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

Ниже приведены шаги для этого:

Выберите набор данных (тот, что с цифрами)
Щелкните вкладку Главная на ленте
В группе номеров нажмите кнопку запуска диалогового окна (значок маленькой наклонной стрелки в правом нижнем углу группы)
Убедитесь, что в диалоговом окне «Формат ячеек» выбрана вкладка «Число».
В списке «Категория» выберите «Номер» (если он еще не выбран).
Снимите флажок «Использовать разделитель 1000 (,)» и укажите, сколько десятичных цифр вы хотите.
Нажмите ОК.

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

Совет: Вы также можете использовать сочетание клавиш Control + 1, чтобы открыть диалоговое окно форматирования ячеек (после выбора ячеек). Удерживая клавишу Control, нажмите клавишу 1.

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

Использование функции ЧИСЛО ЗНАЧЕНИЕ ( NumberValue )

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

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

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

Ниже приведена формула, которая преобразует этот текст в числа.

Скопируйте эту формулу для всех ячеек в столбце, и у вас будут числа.

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

Удалить запятую из текстовой строки

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

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

Позвольте мне показать вам несколько способов сделать это, когда у вас есть текстовые данные.

Использование поиска и замены

С помощью функции «Найти и заменить» вы можете легко найти все запятые в ячейке и заменить их чем-то другим (пустым или другим символом).

Обратите внимание, что этот метод работает только с текстовыми данными. Если у вас есть числа, в которых запятая стоит из-за форматирования, этот метод не удалит запятую.

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

Ниже приведены шаги по удалению запятой из этого набора данных имен:

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

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

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

Использование формулы ЗАМЕНА ( SUBSTITUTE )

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

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

Ниже приведена формула, которая сделает это:

Приведенная выше формула принимает три аргумента:

Текст (или ссылка на ячейку, содержащую текст), из которого вы хотите удалить запятую.
Символ, который вы хотите заменить (запятая в этом примере)
Символ, с помощью которого вы хотите удалить запятую (в этом примере пусто)

Но что, если вы хотите удалить только первую запятую, а не вторую.

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

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

Ниже приведена формула, которая сделает это:

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

Но что, если вы не хотите удалять первую запятую, а вместо этого замените вторую запятую тире.

Вы можете сделать это, используя следующую формулу:

Приведенная выше формула выполняет замену только второго экземпляра запятой и заменяет его тире.

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

Читайте также: