Удалить все символы кроме цифр Excel

Обновлено: 21.11.2024

Знаете ли вы, как удалить нечисловые символы из ячейки в Excel? Здесь мы придумываем инструкции по простому удалению буквенно-цифровых символов на листе Excel. Мы проиллюстрировали два примера с использованием формулы и функции VBA. Давайте рассмотрим их один за другим в этой статье.

Перейти к:

Удалить нечисловые символы с помощью формулы:

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

  • Функция TEXTJOIN — функция Excel TEXTJOIN объединяет или объединяет значения с заданным разделителем, разделяющим каждое значение. Эта функция эффективно объединяет диапазоны, если разделителем является пустая текстовая строка.
  • Функция ИСТИНА. Возвращает значение ИСТИНА, если заданные условия будут ИСТИНА, или наоборот.
  • Функция ЕСЛИОШИБКА. Функция Excel ЕСЛИОШИБКА возвращает пользовательский результат, когда формула выдает ошибку, и стандартный результат, если ошибки не обнаружено.
  • Функция MID. Извлекает число (начиная с левой стороны) или символы из заданной строки.
  • Функция ROW. Функция ROW возвращает номер строки для ссылки.
  • Функция ДВССЫЛ. Функция ДВССЫЛ в Excel возвращает действительную ссылку из заданной текстовой строки.
  • Функция ДЛСТР. Используйте функцию ДЛСТР, чтобы найти длину текстовой строки.

Пример 1:

Удалить нечисловые символы с помощью VBA:

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

Пример 2:

Sub RemoveNonNumber()
Set myRange = Application.Selection
Set myRange = Application.InputBox("выберите диапазон, из которого вы хотите удалить нечисловые символы", "RemoveNonNumber", myRange.Address , Тип:=8)
Для каждой ячейки myCell в моем диапазоне
LastString = ""
Для i = 1 To Len(myCell.Value)
mT = Mid(myCell.Value, i, 1)
If mT Like “5” Then
tString = mT
Else
tString = “”
End If
LastString = LastString & tString
Следующая i
myCell.Value = LastString
Следующая
End Sub

Вывод:

В приведенной выше статье мы объяснили, как простым способом удалить нечисловые символы из ячейки в Excel Office 365. Пожалуйста, не стесняйтесь поделиться своим вопросом или отзывом в разделе комментариев ниже. Большое спасибо, что прочитали!! Чтобы узнать больше, посетите наш веб-сайт Geek Excel!!

Удалить нечисловые символы из текстовых строк

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

Удалите все нечисловые символы из текстовых строк с формулами

В Excel 2019 и Office 365 новая функция СОЕДИНЕНИЕ ТЕКСТ в сочетании с функциями ЕСЛИОШИБКА, СРЕДН, СТРОКА и ДВССЫЛ может помочь вам извлечь из текстовой строки только числа. Общий синтаксис:

  • текст: текстовая строка или значение ячейки, из которой вы хотите удалить все нечисловые символы.
<р>1. Скопируйте или введите приведенную ниже формулу в пустую ячейку, где вы хотите вывести результат:

<р>2. А затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первый результат, см. скриншот:

<р>3. Затем выберите ячейку формулы, а затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, были извлечены только числа, а все другие нечисловые символы были удалены, см. снимок экрана:

Пояснение к формуле:

СТРОКА(ДВССЫЛ("1:100")：Число 1:100 в формуле ДВССЫЛ означает, что функция СРЕДН оценивает 100 символов текстовой строки. Этот массив будет содержать 100 чисел, например: .
Примечание: если ваша текстовая строка намного длиннее, вы можете изменить число 100 на большее число, если вам нужно.

MID(A2,ROW(INDIRECT("1:100")),1: эта функция MID используется для извлечения текста в ячейке A2 для получения одного символа, и это будет такой массив:
< /p>

ЕСЛИОШИБКА(MID(A2,СТРОКА(ДВССЫЛ("1:100")),1)+0: эта функция ЕСЛИОШИБКА используется для замены всех значений ошибки пустой строкой, например:
< /p>

TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,"")): наконец, эта функция TEXTJION объединит все непустые значения в массиве, который возвращается функцией IFFERROR и возвращает результат.

Примечания:
<р>1. С помощью приведенной выше формулы числа будут возвращены в текстовом формате. Если вам нужно реальное числовое значение, примените эту формулу, не забудьте одновременно нажать клавиши Ctrl + Shift + Enter, чтобы получить правильный результат.

<р>2. В ранних версиях Excel эта формула не будет работать, в этом случае вам может помочь следующая формула, скопируйте или введите эту формулу в пустую ячейку:

=СУММПРОИЗВ(СРЕДН(0&A2, БОЛЬШОЙ(ИНДЕКС(ЧИСЛО(--СРЕДН(A2, СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))), 1)) * СТРОКА(ДВССЫЛ("1:"&ДЛСТР (A2))), 0), СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2))))+1, 1) * 10^СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)))/10)< /p>

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

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

Используемые относительные функции:

  • ТЕКСТСОЕДИНЕНИЕ:
  • Функция TEXTJOIN объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.
  • СРЕДНЯЯ:
  • Функция MID используется для поиска и возврата определенного количества символов из середины заданной текстовой строки.
  • СТРОКА:
  • Функция Excel ROW возвращает номер строки ссылки.
  • КОСВЕННОЕ:
  • Функция Excel ДВССЫЛ преобразует текстовую строку в допустимую ссылку.
  • ЕСЛИ ОШИБКА:
  • Функция ЕСЛИОШИБКА используется для возврата пользовательского результата, когда формула вычисляет ошибку, и для возврата обычного результата, если ошибки нет.

Другие статьи:

  • Удалить ненужные символы из ячейки в Excel
  • Вы можете использовать функцию ПОДСТАВИТЬ, чтобы удалить любые ненужные символы из определенной ячейки в Excel.
  • Удалить разрывы строк из ячеек в Excel
  • В этом руководстве представлены три формулы, которые помогут вам удалить разрывы строк (которые появляются при нажатии клавиш Alt + Enter в ячейке) из определенных ячеек в Excel.
  • Удалить текст на основе позиции переменной в Excel
  • В этом руководстве объясняется, как удалить текст или символы из ячейки, если она находится в переменной позиции.
  • Удалить числовые символы из текстовых строк
  • Если вы хотите удалить все числа только из списка текстовых строк, но оставить другие нечисловые символы, возможно, есть некоторые формулы в Excel, которые могут вам помочь.

Лучшие инструменты для повышения производительности в офисе

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

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

Excel позволяет удалять нечисловые символы из строки с помощью нескольких функций Excel: СОЕДИНЕНИЕ ТЕКСТ, ЕСЛИОШИБКА, СРЕДН, СТРОКА и ДВССЫЛ. Это пошаговое руководство поможет пользователям Excel любого уровня извлечь числовые символы из текста.

Рисунок 1. Удаление нечисловых символов из текста

Синтаксис формулы TEXTJOIN

=TEXTJOIN(разделитель, ignore_empty, text1, [text2], . )

  • разделитель – строка, которая будет использоваться в качестве разделителя между текстовыми элементами.
  • ignore_empty — значение TRUE для игнорирования пустых ячеек.
  • текст1, [текст2] — текстовые строки, которые будут объединены

Синтаксис формулы ЕСЛИОШИБКА

=ЕСЛИОШИБКА(значение, значение_если_ошибка)

  • значение — оцениваемая ячейка
  • value_if_error — значение, которое будет вставлено, если в ячейке существует ошибка

Синтаксис формулы MID

=MID(текст, start_num, num_chars)

  • текст — строка, из которой мы хотим извлечь символы
  • start_num — позиция первого символа, который мы хотим извлечь из строки
  • num_chars — количество символов, которое мы хотим извлечь из первого символа

СТРОКА и ДВССЫЛ являются вспомогательными функциями в синтаксисе массива формул MID, и объяснение этой части формулы будет дано в примере.

Настройка наших данных для удаления нечисловых символов

Наша таблица состоит из 2 столбцов: «Текст» (столбец B) и «Число из текста» (столбец C). Идея состоит в том, чтобы убрать из строки нечисловые символы и поместить числовые символы в столбец C.

Рисунок 2. Структура таблицы с ячейками, содержащими текст и числа

Получить числовые символы из строки

Мы хотим извлечь только числовые символы из строки в столбце «Текст». Это можно сделать с помощью комбинации нескольких функций: TEXTJOIN, IFERROR, MID, ROW и INDIRECT.

Формула выглядит следующим образом:

Для правильной работы формула должна быть функцией массива. Чтобы создать функцию массива, мы должны одновременно нажать ctr, Shift и Enter на формуле.

Чтобы применить формулу, нам нужно выполнить следующие шаги:

  • Выберите ячейку C3 и нажмите на нее.
  • Вставьте формулу: =ТЕКСТОВОЕСОЕДИНЕНИЕ("",ИСТИНА,ЕСЛИОШИБКА(СРЕДН(B3,СТРОКА(ДВССЫЛ("1:30")),1)+0,""))
  • Чтобы создать массив, одновременно нажмите ctrl, shift и enter.
  • Перетащите формулу в другие ячейки столбца, нажав и перетащив маленький значок "+" в правом нижнем углу ячейки.

Рисунок 3. Формула удаления нечисловых символов из строки

Формула MID извлекает каждый символ строки с помощью части формулы: СТРОКА(ДВССЫЛ("1:30")). Число «1:30» в формуле ДВССЫЛ означает, что функция MID оценивает 30 символов строки. Мы можем ввести большее число, если строка содержит больше символов.

Функция MID возвращает массив из 30 символов в строке:

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

Функция ЕСЛИОШИБКА заменяет значения ошибки пустой строкой:

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

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

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Есть ли какая-либо формула / или какая-либо команда в Excel для преобразования буквенных символов в числовое значение. например: от одной тысячи восьмисот пятидесяти шести до 1856 года.

Здравствуйте! Я использую анализ данных для создания гистограммы, и когда я ввожу всю свою информацию и нажимаю «ОК», я получаю: «гистограмма - диапазон ячеек не может иметь нечисловых данных», какая помощь?

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

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

Скачать рабочую тетрадь

Загрузите эту практическую тетрадь

2 способа удалить нечисловые символы из ячеек в Excel

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

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

1. Использование формул для удаления нечисловых символов из ячеек в Excel

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

а. Использование функций TEXTJOIN и INDIRECT

Общая формула, которую мы собираемся использовать:

📌 Шаги:

<р>1. Сначала введите следующую формулу в ячейку C5:

<р>2. Затем нажмите Enter.

<р>3. Затем перетащите значок ручки заполнения в диапазон ячеек C6:C9.

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

🔎 Разбивка формулы

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

➤ СТРОКА(ДВССЫЛ("1:100"))

Эта функция возвращает массив

➤ СРЕДН(B5,СТРОКА(ДВССЫЛ("1:100")),1)+0

Эта функция возвращает

➤ ЕСЛИОШИБКА(СРЕДН(B5,СТРОКА(ДВССЫЛ("1:100″)),1)+0,"")

Эта функция возвращает

➤ TEXTJOIN("",ИСТИНА,ЕСЛИОШИБКА(СРЕДН(B5,СТРОКА(ДВССЫЛ("1:100″)),1)+0,""))

Наконец, функция TEXTJOIN объединит их и вернет в виде текста. Мы используем TRUE для работы с оставшимися значениями.

б. Использование функций TEXTJOIN и SEQUENCE для удаления нечисловых символов из ячеек в Excel

Общая формула, которую мы собираемся использовать:

📌 Шаги:

<р>1. Сначала введите следующую формулу в ячейку C5:

<р>2. Затем нажмите Enter.

<р>3. Затем перетащите значок ручки заполнения в диапазон ячеек C6:C9.

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

🔎 Разбивка формулы

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

➤ ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(B5))

Эта функция возвращает нам массив

➤ СРЕДНИЙ(B5, ПОСЛЕДОВАТЕЛЬ(ДЛСТР(B5)), 1)

Эта функция возвращает массив

➤ СРЕДН(B5, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(B5)), 1) *1

Эта функция возвращает

➤ ЕСЛИОШИБКА(СРЕДН(B5, ПОСЛЕДОВАТЕЛЬНОСТЬ(ДЛСТР(B5)), 1) *1, "")

После этого мы получим этот массив

➤ TEXTJOIN("", ИСТИНА, ЕСЛИОШИБКА(MID(B5, SEQUENCE(LEN(B5)), 1) *1, ""))

В конце концов, функция TEXTJOIN объединит непустые значения в массиве и проигнорирует пустые значения, поскольку аргумент ignore_empty имеет значение TRUE, используя пустую строку ("") в качестве разделителя.

2. Использование кодов VBA для удаления нечисловых символов из ячеек

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

📌 Шаги:

<р>1. Сначала нажмите Alt+F11 на клавиатуре. После этого откроется редактор Visual Basic.

<р>2. Затем нажмите «Вставить» > «Модуль».

<р>3. После этого введите в редакторе следующий код:

<р>4. Теперь введите следующую формулу в ячейку C5:

<р>5. Затем нажмите Enter.

<р>6. Наконец, перетащите маркер заполнения в диапазон ячеек C6:C9.

Как видите, мы успешно удалили нечисловые символы с помощью кодов VBA.

Заключение

Статьи по теме

    • Как удалить символы слева в Excel (6 способов)
    • Использование функции TEXTJOIN в Excel (3 примера)
    • Как удалить специальные символы в Excel (4 метода)
    • Удалить символы в Excel (6 способов)
    • Как удалить пустые символы в Excel (5 способов)
    • Как удалить тире в Excel (3 метода)

    Шанто

    Здравствуйте! Я Шанто. Разработчик контента Excel и VBA. Моя цель — предоставить нашим читателям отличные руководства по различным проблемам, связанным с Excel. Я надеюсь, что наши простые, но эффективные уроки обогатят ваши знания. Я получил степень бакалавра компьютерных наук и инженерии в Международном университете Даффодил. Работа с данными всегда была моей страстью. Любите работать с данными, анализировать их и находить закономерности. Кроме того, любите исследовать. Всегда ищите вызовы, которые помогут мне расти.

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