Як залишити в осередку тільки цифри або тільки текст?

1. Пошук по мітках

От буває так: є у Вас в осередку якийсь текст. Припустимо "Було доставлено шматків мила 763шт.". Вам потрібно з цього тільки 763 - щоб можна було провести з цим якісь математичні дії. Якщо це тільки одна комірка - проблем тут немає, а якщо таких осередків пару тисяч? І до того ж всі різні?

• Було доставлено шматків мила 763шт.
• Всього прийшло 34
• Тюбики - 54 доставлено
• і т.д.

Ніякої зачіпки для вилучення даних. Пару тисяч таких рядків видаляти вручну досить обтяжлива заняття, треба сказати. Та ще й не швидка.
Є кілька варіантів вирішення такого завдання.

1. Формула вводиться в осередок сполучення клавіш Ctrl + Shift + Enter, тому що є формулою масиву. Детальніше про ці формули читайте в статті: Що таке формула масиву
2. в такому вигляді формула працює з текстом, кількість символів в якому не перевищує 99. Щоб розширити необхідно у формулі у всіх місцях замінити СТРОКА ($ 1: $ 99) на СТРОКА ($ 1: $ 200). Тобто замість 99 вказати кількість символів з запасом. Тільки не захоплюйтеся, інакше може вийти, що формула буде працювати дуже довго
3. формула не обробить коректно текст "Було доставлено шматків мила 763шт., а замовляли 780" і йому подібний, де числа розкидані по тексту.

Тепер коротко розберемо формулу на прикладі фрази: Було доставлено шматків мила 763шт.

• в A1 сам текст, з якого необхідно витягти числа: Було доставлено шматків мила 763шт., а замовляли 780
• блок: МІН (ЯКЩО (ЕЧІСЛО (-ПСТР (A1; СТРОКА ($ 1: $ 99); 1)); СТРОКА ($ 1: $ 99)))
  обчислює позицію першої цифри в осередку - 29
• блок: ПЕРЕГЛЯД (2; 1 / ЕЧІСЛО (-ПСТР (A1; СТРОКА ($ 1: $ 99); 1)); СТРОКА ($ 1: $ 99))
  обчислює позицію останньої цифри в осередку - 31
• в результаті виходить: = ПСТР (A1; 29; 31 - 29 +1)
  функція ПСТР витягує з тексту, зазначеного першим аргументом (A1) текст, починаючи з вказаної позиції (29) з кількістю символів, зазначеним третім аргументом (31 - 29 +1)
• І в підсумку:
  = ПСТР (A1; 29; 31 - 29 +1)
  => = ПСТР (A1; 29; 2 + 1)
  => = ПСТР (A1; 29; 3)
  => 763

СПОСІБ 2: використовуємо макроси
Найголовніший недолік методу за допомогою формули, наведеної вище - з тексту "Було доставлено шматків мила 763шт., А замовляли 780" формула поверне не тільки числа, а й текст між першою і останньою цифрою: 763шт., А замовляли 780.
Вирішити ж проблему вилучення цифр навіть з такого тексту за допомогою VBA куди простіше і гнучкіше. Плюс можна не тільки цифри витягувати, але і навпаки - цифри видалити, а витягти тільки текст. Нижче наведено код користувальницької функції , Яка допоможе витягти з рядка тільки числа або тільки текст. Іншими словами, результатом функції буде або тільки текст, або тільки числа.

Function Extract_Number_from_Text (sWord As String, Optional Metod As Integer) 'sWord = посилання на клітинку або безпосередньо текст' Metod = 0 - числа 'Metod = 1 - текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Немає даних!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len (sWord) sSymbol = Mid (sWord, i, 1) If Metod = 1 Then If Not LCase (sSymbol) Like "* [0-9] *" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = "") And i> 1 Then If Mid (sWord, i - 1, 1) Like "* [0 -9] * "And Mid (sWord, i + 1, 1) Like" * [0-9] * "Then sSymbol =" "End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase (sSymbol) Like" * [0-9.,;: -] * "Then If LCase (sSymbol) Like" * [.,] * "And i> 1 Then If Not Mid (sWord, i - 1, 1) Like" * [0 -9] * "Or Not Mid (sWord, i + 1, 1) Like" * [0-9] * "Then sSymbol =" "End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Ex tract_Number_from_Text = sInsertWord End Function

Даний код необхідно помістити в стандартний модуль книги. Після цього в майстра функцій в категорії Певні користувачем (User Defined) буде доступна функція Extract_Number_from_Text, яку можна буде застосовувати як звичайну функцію на аркуші.
Для вилучення тільки чисел
= Extract_Number_from_Text (A1; 0)
або
= Extract_Number_from_Text (A1)
Для вилучення тільки тексту
= Extract_Number_from_Text (A1; 1)

Детальніше про створення призначених для користувача функції і їх застосуванні можна почитати в статті Що таке функція користувача (UDF)?

Крім функції користувача вирішив викласти і варіант з використанням діалогового вікна:

Вибрати клітинку або діапазон з текстом (Лист1! $ A $ 2: $ A $ 10) - тут вказується діапазон з вихідними значеннями, з якого необхідно залишити тільки числа або тільки текст.

Виберіть осередок для виведення даних (Лист1! $ A $ 2) - вказується один осередок, з якою почати виведення перетворених значень. Як цього осередку можна вибрати перший осередок діапазону з текстом (вихідного) якщо необхідно провести зміни відразу в цих же осередках (як на малюнку). Обережніше з цієї вказівки, тому що результат роботи коду може бути не зовсім таким, який ви очікували, а повернути колишні дані вже не вийде - якщо тільки не закрити файл без збереження змін.

Залишити тільки цифри, залишити тільки текст - думаю не треба пояснювати. Тут вибираємо, що залишити в якості результату.

Невелике доповнення до використання коду
У коді є рядок:

If LCase (sSymbol) Like "* [0-9.,;: -] *" Then

Цей рядок відповідає за текстові символи, які можуть зустрічатися всередині чисел і які треба залишити (не видаляти нарівні з іншими чи не числова символами). Отже, якщо якісь із цих символів не потрібні в кінцевому тексті - їх треба просто видалити. Наприклад, щоб залишалися виключно числа (без ком і ін.):

If LCase (sSymbol) Like "* [0-9] *" Then

якщо треба виключити з видалення крім цифр точку (тобто. будуть вилучені цифри і крапка):

If LCase (sSymbol) Like "* [0-9.] *" Then

і т.д.
Завантажити приклад:

Число з тексту і наоборот.xls (99,0 KiB, 12 115 завантажень)

Також див .:
Витяг числа з тексту
Що таке функція користувача (UDF)?
Як отримати адресу гіперпосилання з осередку
Залишити цифри або текст за допомогою PowerQuery

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках