Як прибрати числа в Excel зліва: це просто!

Бувають випадки, коли ви працюєте з даними, відформатованими не так, як вам потрібно. Наприклад, у вас може бути суміш цифр і тексту в клітинці, і вам потрібно очистити ці дані.

Існує кілька способів видалення чисел в Excel зліва від тексту. Деякі методи працюють, коли дані мають послідовний шаблон. VBA або більш складні формули працюють з менш структурованими даними.

У цій статті показано сім методів видалення чисел ліворуч від текстових даних. Наприкінці ви матимете повне розуміння різних технік і того, коли їх використовувати.

Ходімо!

Зміст

• Як використовувати Flash Fill для видалення чисел у Excel зліва
• Як використовувати функцію «Текст у стовпці» для видалення чисел
• 5 текстових функцій для видалення чисел у Excel зліва
  • 1. Функція MID
  • 2. Поєднайте з функцією LEN
  • 3. Поєднання функцій LEFT і SUBSTITUTE
  • 4. Використання функцій RIGHT і LEN
  • 5. Використання функції REPLACE
• Як за допомогою Power Query видалити числа зліва
• 3 передові прийоми незалежно від кількості персонажів
  • 1. Поєднайте допоміжний стовпець із формулою, щоб видалити символи зліва від клітинок
  • 2. Створіть макрос VBA для видалення чисел
  • 3. Створіть функцію, визначену користувачем
• Заключні думки

Як використовувати Flash Fill для видалення чисел у Excel зліва

Flash Fill — це функція Excel, яка автоматично заповнює шаблони. Це швидкий і простий спосіб видалити числа з лівого боку комірок.

Цей метод може не завжди працювати ідеально, але він може бути корисним у простих випадках. Виконайте такі дії, щоб використовувати Flash Fill:

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

2. Виберіть клітинку з новими даними (B1 у нашому прикладі) і натисніть вкладку «Дані» на стрічці.

3. Натисніть кнопку Flash Fill у розділі Data Tools.

4. Excel автоматично застосує шаблон для видалення чисел з лівого боку суміжних клітинок.

Це розташування кнопки Flash Fill на вкладці «Дані».

Якщо ви віддаєте перевагу використанню ярликів, натисніть CTRL + E на Windows або CMD + E на Mac.

Flash Fill є зручною функцією, але, як і у більшості речей у Excel, існує кілька способів виконати те саме завдання.

У наступному розділі ми розглянемо, як ви можете зробити те саме за допомогою функції «Текст у стовпці».

Як використовувати функцію «Текст у стовпці» для видалення чисел

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

Давайте попрацюємо з набором даних, який використовує дефіс для відокремлення чисел від літер таким чином:

• 1234-яблуко

• 4321-помаранчевий

• 9876-груша

Ми хочемо досягти цього до і після:

Ось покроковий посібник із цим зразком набору даних:

1. Виберіть стовпець A.

2. Перейдіть на вкладку «Дані» та натисніть майстер «Текст у стовпці».

3. Виберіть опцію «З роздільниками» та натисніть «Далі».

4. Зніміть прапорці з усіх роздільників (табуляція може бути застосована за замовчуванням).

5. Перевірте роздільник «Інше» та введіть «-» у поле введення.

6. Виберіть відповідний роздільник (пробіл, кома тощо).

7. Натисніть Далі та Готово.

Тепер дані будуть розділені на два стовпці. Видалені символи знаходяться в першому стовпці, а решта тексту – у другому стовпці. На цьому малюнку показано, як встановити роздільник:

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

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

Якщо ви знаєте, скільки символів складають цифри, ви можете скористатися текстовою функцією, щоб видалити їх.

Припустимо, клітинка A1 містить рядок « 1234apple ». Перші чотири символи - це числа, які ми хочемо видалити.

Є кілька функцій, які можна використовувати самостійно або комбінувати для видалення символів, які є числовими значеннями.

1. Функція MID

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

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

MID(текст, початковий номер, кількість_символів)

• текст: текстовий рядок, що містить символи, які потрібно витягти.

• start_num: позиція першого символу, який ви хочете витягти.

• num_chars: кількість символів, які потрібно отримати з текстового рядка.

Щоб видалити цифри зліва, виконайте такі дії:

1. виберіть клітинку B2, щоб містити функцію та результати.

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

Наведена вище формула видалить символи з 5-го слота в рядку.

2. Поєднайте з функцією LEN

Одним із недоліків цього методу є те, що ваш текстовий рядок може не завжди мати однакову довжину.

Ви можете використовувати функцію LEN для динамічного обчислення довжини. Ця функція повертає довжину текстового рядка.

Ви можете змінити формулу, щоб замінити третій параметр функцією LEN таким чином:

=MID(A1; 5; LEN(A1)-4)

Ви можете скопіювати формулу за допомогою ручки заповнення. На цьому малюнку показано використання наведеної вище формули:

3. Поєднання функцій LEFT і SUBSTITUTE

Функція LEFT повертає крайні ліві символи з текстового рядка. Наприклад, LEFT(“1234apple”, 4) поверне значення “1234”.

Це корисно, коли ви хочете отримати числа. Але що, якщо ви хочете їх видалити?

Ви можете поєднати LEFT із функцією SUBSTITUTE, щоб замінити число порожнім рядком. Це формат для нашого прикладу:

=ПІДСТАВИТИ(A1; ВЛІВО(A1; 4); “”)

4. Використання функцій RIGHT і LEN

Функція RIGHT повертає крайні праві символи з текстового рядка. Синтаксис функції RIGHT такий:

ПРАВО(текст; [кількість_символів])

• текст: текстовий рядок, що містить символи, які потрібно витягти.

• num_chars: (необов’язково) кількість символів, які потрібно витягти з правого боку текстового рядка. Якщо опущено, за умовчанням дорівнює 1.

Наприклад, RIGHT(“1234apple”, 5) поверне значення “apple”.

Ви також можете комбінувати функції RIGHT і LEN, щоб уникнути жорсткого кодування кількості буквених символів. Це припускається, що ви знаєте кількість цифрових символів.

Формат для нашого прикладу: =RIGHT(A1, LEN(A1)-4)

5. Використання функції REPLACE

Функцію REPLACE можна використовувати для заміни певної кількості символів зліва в клітинці. Ось синтаксис:

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, у цьому відео показано кілька складніших прикладів:

3 передові прийоми незалежно від кількості персонажів

Основний недолік методів на даний момент полягає в тому, що числа в усіх клітинках повинні відповідати одному шаблону або складатися з певної кількості символів.

Що робити, якщо ви хочете видалити символи без чіткого шаблону у вихідних даних? Техніки в цьому розділі призначені для користувачів Excel середнього рівня.

1. Поєднайте допоміжний стовпець із формулою, щоб видалити символи зліва від клітинок

Ви можете комбінувати функції з допоміжними стовпцями, якщо не знаєте загальної довжини чисел. Цей метод вимагає двоетапного процесу:

• використовувати формулу для створення тимчасової зміненої версії оригінального тексту

• використовуйте іншу формулу, щоб видалити числа на основі зміненого тексту.

Першим кроком є ​​використання наведеної нижче формули для заміни всіх чисел певним символом (наприклад, «|»):

=ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(ПІДСТАВИТИ(A1, “0”, “|”), “1”, “|”), “2”, “|”), «3», «|»), «4», «|»), «5», «|»), «6», «|»), «7», «|»), «8», « |”), “9”, “|”)

Другим кроком є ​​використання наступної формули в іншому допоміжному стовпці (наприклад, стовпці C):

=TRIM(RIGHT(SUBSTITUTE(B1, “|”, REPT(” “, LEN(B1))), LEN(B1)))

Це до і після з формулами в стовпцях B і C:

Коли ви починаєте комбінувати такі функції, ви можете зіткнутися з проблемами з вибором неправильних параметрів у своїх формулах. Наша стаття допоможе вам усунути поширену помилку.

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

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:

=RemoveNumbersFromLeft(A1)

Заключні думки

У цій статті ми дослідили кілька методів видалення чисел з лівого боку текстових рядків у Excel. Кожна техніка має свої переваги та обмеження.

Жоден метод не є ідеальним для кожної ситуації, але обов’язково знайдеться підхід, який найкраще відповідає вашим потребам. Попрактикувавшись, ви навчитеся застосовувати ці техніки для вирішення будь-яких завдань форматування даних, які постануть на вашому шляху.

Ось і все, тонкощі видалення непотрібних цифр зліва в Excel. Спочатку це може здатися дещо складним, але за допомогою правильних прийомів ви швидко впораєтеся! Щасливого числа!