Как удалить числа в Excel слева: это просто!

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

Существует несколько способов удаления чисел в Excel слева от текста. Некоторые методы работают, когда данные имеют последовательную структуру. VBA или более сложные формулы работают с менее структурированными данными.

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

Пойдем!

Оглавление

• Как использовать Flash Fill для удаления чисел в Excel слева
• Как использовать функцию «Текст в столбцы» для удаления чисел
• 5 текстовых функций для удаления чисел в Excel слева
  • 1. СРЕДНЯЯ функция
  • 2. В сочетании с функцией LEN
  • 3. Сочетание функций LEFT и SUBSTITUTE
  • 4. Использование функций RIGHT и LEN
  • 5. Использование функции ЗАМЕНА
• Как использовать Power Query для удаления чисел слева
• 3 продвинутых техники вне зависимости от количества персонажей
  • 1. Объедините вспомогательный столбец с формулой, чтобы удалить символы слева от ячеек.
  • 2. Создайте макрос VBA для удаления чисел
  • 3. Создайте пользовательскую функцию
• Последние мысли

Как использовать Flash Fill для удаления чисел в Excel слева

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

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

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

2. Выберите ячейку с новыми данными (в нашем примере B1) и щелкните вкладку Данные на ленте.

3. Нажмите кнопку «Быстрая заливка» в разделе «Инструменты данных».

4. Excel автоматически применит шаблон для удаления чисел с левой стороны соседних ячеек.

Это расположение кнопки «Быстрая заливка» на вкладке «Данные».

Если вы предпочитаете использовать ярлыки, нажмите CTRL + E в Windows или CMD + E на Mac.

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

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

Как использовать функцию «Текст в столбцы» для удаления чисел

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

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

• 1234-яблоко

• 4321-оранжевый

• 9876-груша

Это до и после, которого мы хотим достичь:

Вот пошаговое руководство с этим примером набора данных:

1. Выберите столбец А.

2. Перейдите на вкладку «Данные» и щелкните мастер «Текст в столбцы».

3. Выберите параметр «С разделителями» и нажмите «Далее».

4. Снимите все разделители (вкладка может быть применена по умолчанию).

5. Установите флажок Другой разделитель и введите «-» в поле ввода.

6. Выберите подходящий разделитель (пробел, запятая и т. д.).

7. Нажмите «Далее» и «Готово».

Теперь данные будут разделены на два столбца. Удаленные символы находятся в первом столбце, а остальная часть текста — во втором столбце. На этом рисунке показано, как установить разделитель:

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

5 текстовых функций для удаления чисел в Excel слева

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

Предположим, ячейка A1 содержит строку « 1234apple ». Первые четыре символа — это числа, которые мы хотим удалить.

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

1. СРЕДНЯЯ функция

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

Функция MID извлекает определенный раздел текстовой строки, учитывая начальную позицию и количество символов для извлечения. Синтаксис функции MID:

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

• text: Текстовая строка, содержащая символы, которые вы хотите извлечь.

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

• num_chars: количество символов для извлечения из текстовой строки.

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

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

2. Введите формулу: MID(A1, 5, 7)

Приведенная выше формула удалит символы из 5-го слота в строке.

2. В сочетании с функцией LEN

Одним из недостатков этого метода является то, что ваша текстовая строка может не всегда иметь одинаковую длину.

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

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

=СРЕДН(A1, 5, ДЛСТР(A1)-4)

Формулу можно скопировать вниз, используя маркер заполнения. На этом рисунке показана приведенная выше формула в использовании:

3. Сочетание функций LEFT и SUBSTITUTE

Функция LEFT возвращает крайние левые символы из текстовой строки. Например, LEFT("1234apple", 4) вернет значение "1234".

Это полезно, когда вы хотите извлечь числа. Но что, если вы хотите удалить их?

Вы можете комбинировать ЛЕВЫЙ с функцией ПОДСТАВИТЬ, чтобы заменить число пустой строкой. Это формат для нашего примера:

=ПОДСТАВИТЬ(A1, ВЛЕВО(A1, 4), "")

4. Использование функций RIGHT и LEN

Функция RIGHT возвращает крайние правые символы из текстовой строки. Синтаксис функции ПРАВИЛЬНО:

ПРАВО(текст, [число_символов])

• text: Текстовая строка, содержащая символы, которые вы хотите извлечь.

• num_chars: (необязательно) количество символов, которое нужно извлечь из правой части текстовой строки. Если опущено, по умолчанию используется 1.

Например, RIGHT("1234apple", 5) вернет значение "apple".

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

Формат для нашего примера: =ПРАВО(A1, ДЛСТР(A1)-4)

5. Использование функции ЗАМЕНА

Функция REPLACE может использоваться для замены определенного количества символов слева в ячейке. Это синтаксис:

ЗАМЕНИТЬ(старый_текст, начальный_номер, число_символов, новый_текст)

• old_text: исходная текстовая строка, которую вы хотите изменить.

• start_num: позиция в old_text, с которой вы хотите начать замену символов.

• num_chars: количество символов в old_text, которые вы хотите заменить.

• новый_текст: текстовая строка, которой вы хотите заменить указанные символы.

Следующая формула предназначена для наших выборочных данных:

=ЗАМЕНИТЬ(A1, 1, 4, "")

И это последняя из наших пяти текстовых функций для удаления символов слева в Excel.

В большинстве случаев семи методов, которые мы рассмотрели до сих пор, должно быть достаточно, но если вам нужно больше мощности, Power Query — это ответ, и это то, о чем мы поговорим в следующем разделе!

Как использовать Power Query для удаления чисел слева

Power Query — это мощный инструмент преобразования данных , доступный в Excel. Метод, показанный в этом разделе, предполагает наличие разделителя между числами и текстом в ячейке.

Первым шагом является преобразование целевых данных в таблицу:

1. Перейдите на вкладку Данные.

2. Выберите диапазон ячеек.

3. Выберите «Из таблицы/диапазона» в разделе «Получить данные» на ленте.

Когда откроется редактор Power Query, выполните следующие действия:

1. Выберите целевой столбец (столбцы).

2. Перейдите на вкладку «Преобразование».

3. Нажмите «Извлечь» на ленте и выберите «Текст после разделителя».

4. В диалоговом окне «Текст после разделителя» введите тире (-).

5. Нажмите «ОК», чтобы принять детали.

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

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

Если вы не знакомы с этим инструментом данных, ознакомьтесь с нашим руководством по использованию Power Query в Excel .

Если вы хотите глубже изучить использование таблиц в Power Query, в этом видео показаны более сложные примеры:

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

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

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

1. Объедините вспомогательный столбец с формулой, чтобы удалить символы слева от ячеек.

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

• использовать формулу для создания временной измененной версии исходного текста

• используйте другую формулу для удаления чисел на основе измененного текста.

Первый шаг — использовать следующую формулу для замены всех чисел определенным символом (например, «|»):

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A1, «0», «|»), «1», «|»), «2», «|»), «3», «|»), «4», «|»), «5», «|»), «6», «|»), «7», «|»), «8», « |"), "9", "|")

Второй шаг — использовать следующую формулу в другом вспомогательном столбце (например, столбце C):

=ОТРЕЗАТЬ(ПРАВО(ПОДСТАВИТЬ(B1, "|", ПОВТОР(" ", ДЛСТР(B1))), ДЛСТР(B1)))

Это до и после с формулами в столбцах B и C:

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

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

2. Создайте макрос VBA для удаления чисел

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

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

Для этого выполните следующие действия:

1. Нажмите ALT + F11, чтобы открыть редактор VBA.

2. Щелкните Вставить > Модуль, чтобы создать новый модуль.

3. Скопируйте и вставьте следующий код, чтобы удалить только числа слева от столбца A:

Sub RemoveLeftNumbers()
    Dim ws As Worksheet, cell As Range, i As Integer
    
    ' Set the active worksheet as the target worksheet
    Set ws = ActiveSheet

    ' Loop through all the cells in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Remove numbers from the left of each cell
        For i = 1 To Len(cell.Value)
            If Not IsNumeric(Mid(cell.Value, i, 1)) Then
                Exit For
            End If
        Next i
        cell.Value = Mid(cell.Value, i)
    Next cell
End Sub

Последним шагом является запуск макроса. Щелкните зеленый треугольник или пункт меню «Выполнить».

3. Создайте пользовательскую функцию

Вы также можете создать пользовательскую функцию с помощью VBA, которую можно использовать в своих формулах.

Для этого выполните следующие действия:

1. Нажмите ALT + F11, чтобы открыть редактор VBA.

2. Щелкните Вставить > Модуль, чтобы создать новый модуль.

3. Скопируйте и вставьте следующий код:

Public Function RemoveNumbersFromLeft (text As String) As String
    Dim i As Integer
    For i = 1 To Len(text)
        If Not IsNumeric(Mid(text, i, 1)) Then
            Exit For
        End If
    Next i
    RemoveNumbersFromLeft = Mid(text, i)
End Function

Теперь вернитесь к листу Excel и используйте общедоступную функцию в формуле.

Например, эта формула работает, принимая ячейку A1:

= УдалитьЧислаСлева(A1)

Последние мысли

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

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

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