Как удалить текст из ячейки в excel и оставить только цифры
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
- Было доставлено кусков мыла 763шт.
- Всего пришло 34
- Тюбики - 54 доставлено
- и т.д.
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст " Было доставлено кусков мыла 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 "*6*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*4*" And Mid(sWord, i + 1, 1) Like "*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 "*2*" Or Not Mid(sWord, i + 1, 1) Like "*4*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_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 "*1*" Then
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then
Число из текста и наоборот.xls (99,0 KiB, 17 001 скачиваний)
В ячейках табличного массива Microsoft Office Excel можно прописывать различные значения, символы. Соответственно, их также можно быстро удалить, оставив только важные и нужные элементы. О том, как это сделать, будет рассказано в данной статье.
Как удалить числа из ячеек Excel, оставив только текст
Существует несколько распространённых способов выполнения поставленной задачи, каждый из которых рекомендуется подробно изучить, чтобы понять принципы работы в Excel.
Способ 1. Ручное удаление
Этот метод считается самым простым. Для его реализации пользователю придётся действовать следующим образом:
- Левой клавишей манипулятора выделить ячейку исходной таблицы, где нужно произвести деинсталляцию символов.
- Поставить курсор мышки в строку для ввода формул сверху главного меню программы. В этом поле полностью прописывается информация, содержащаяся в выбранной ячейке.
- Поставить указатель в нужное место текста и удалить символы нажатием на кнопку «Backspace» с клавиатуры компьютера. Таким образом, в ячейке можно оставить только сам текст, удалив при этом числовые значения, величины.
Обратите внимание! После деинсталляции элементов в строке для ввода формул они автоматически удалятся из указанной ячейки табличного массива.
Финальный результат деинсталляции
Способ 2. Использование специальных функций
В Microsoft Office Excel есть встроенные функции, с помощью которых можно удалить часть символов из конкретной ячейки таблички. При реализации метода пользователю потребуется задействовать функции: «ДЛСТР()» и «ПРАВСИМ()» либо ее аналог «ЛЕВСИМ()». В общем виде процесс деинсталляции чисел из ячеек Excel путём ввода соответствующих формул подразделяется на следующие этапы:
- Составить таблицу с исходными данными. Таблица, для которой проводится операция обрезки символов, приведена на изображении ниже.
- Выделить ячейку, где будет выводиться результат и кликнуть ЛКМ по значку функций справа от строки для ввода формул.
- Откроется окно «Вставка функции». Здесь в строку поиска надо вбить с клавиатуры название «ДЛСТР», чтобы посчитать количество символов в нужной ячейке.
- Нажать дважды ЛКМ по найденному значению. После этого должно запуститься окно аргументов функции, а сам оператор появится в выделенном ранее элементе табличного массива.
- Поставить курсор мышки в строку «Текст» и в исходной табличке щёлкнуть по строчке, в которой надо посчитать количество символов.
- Нажать на «ОК» и проверить результат. В выбранном столбце массива выведется соответствующее значение. При необходимости формулу можно продлить на оставшиеся строки таблицы, чтобы не заполнять их вручную.
- Посчитать в уме количество чисел в ячейке, которые надо удалить из нее.
- Теперь из общего числа символов ячейки необходимо вычесть посчитанное значение ненужных элементов по формуле и вывести результат в отдельный столбец. Подробнее на скриншоте ниже.
- Приступить к деинсталляции лишних символов, в данном случае чисел. Для этого во втором столбце таблички необходимо прописать ещё одну функцию.
- Написать функцию «=ПРАВСИМ(С4;F4)». В скобках на первом месте указывается адрес ячейки, где прописан исходный текст, а на втором адрес элемента, в котором подсчитано количество символов, оставшееся после удаления чисел.
Важно! Функции «ПРАВСИМ()» и «ЛЕВСИМ()» равнозначны. Первая используется, когда надо удалить знаки слева, а вторая, если деинсталляция проводится справа от написанной формулы.
При желании прописанное выражение в MS Excel можно продлить на весь диапазон ячеек табличного массива, чтобы не выполнять описанную выше операцию для каждой строки.
Способ 3. Извлечение чисел из текста с помощью опции мгновенного заполнения
Данный способ не предполагает использование стандартных формул. Чтобы удалить числа из ячеек Excel, используя опцию «Мгновенное заполнение», необходимо проделать ряд шагов по алгоритму:
- Создать исходную таблицу. В рассматриваемом примере табличный массив состоит из двух столбцов. В первом прописан полный текст, а втором будет выводиться обработанная информация без чисел.
- Во второй столбик таблички выписать вручную с клавиатуры числа из первого столбца, которые надо деинсталлировать.
- Зажать одновременно кнопки «Ctrl+E» для активации опции мгновенного заполнения. Справа от заполненного второго столбца таблицы появится иконка параметров опции.
- После выполнения предыдущей манипуляции числовые значения из первого столбца табличного массива пропадут. Они будут прописаны во втором столбике.
Дополнительная информация! Отменить мгновенное заполнение можно с помощью сочетания клавиш «Ctrl+Z» с клавиатуры компьютера в любой версии программы Microsoft Office Excel.
К сожалению, в Эксель нет специальной функции, удаляющей только числа из текста в ячейке либо какие-то определённые символы. Однако реализовать такую возможность можно более длинным путём, описанном во втором способе выше.
Заключение
Таким образом, удаление чисел из ячеек подчиняется ряду правил, описанных выше. С представленными рекомендациями необходимо внимательно ознакомиться, чтобы хорошо разбираться в данной теме.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Часто перед пользователями табличного редактора Эксель встает такая задача, когда необходимо удалить всю текстовую информацию в ячейках, а числовые данные оставить. Для осуществления этой процедуры существует множество способов. В статье мы детально разберем каждый метод, позволяющий оставить числа и убрать текст в ячейках.
Способы удаления текста и сохранения числовой информации
Представим, что у нас есть такая информация, располагающаяся в ячейке: «Было доставлено кусков мыла 763шт». Нам необходимо сделать так, чтобы осталось только значение 763 для проведения разнообразных математических операций. Хорошо, если нужно избавиться от текстовых данных только в одной ячейке, тогда можно реализовать удаление ручным способом, но этот вариант не подходит, когда в табличке находится слишком много разной информации. Здесь необходимо применять различные специальные функции табличного редактора.
Первый метод: использование специальной формулы
Для реализации этой процедуры можно применять специальную массивную формулу. Примерная формула выглядит так:
Разберем основные моменты:
- Специальную формулу необходимо вбивать в поле при помощи комбинации кнопок «Ctrl+Shift+Enter».
- Стоит заметить, что в таком виде массивная формула может использоваться только с текстовой информацией, в которой число знаков не больше 99. Для увеличения диапазона нужно, к примеру, заменить параметр «СТРОКА($1:$99)» на «СТРОКА($1:$200)». Иными словами, мы вместо показателя 99 вводим число знаков с запасом. Если ввести слишком большой диапазон, то обработка формулы может занять длительное время.
- Если в текстовых данных числовые значения разбросаны по всему тексту, то формула не сможет правильно обработать информацию.
Детально рассмотрим специальную массивную формулу на таком примере: «Было доставлено кусков мыла 763шт., а заказывали 780»
- В поле А1 располагается сама текстовая информация, из которой мы будем извлекать числовые данные.
- Фрагмент: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))) позволяет определить позицию 1-го значения в поле. Получаем значение 29.
- Фрагмент: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)) позволяет определить позицию последнего значения в поле. Получаем значение 31.
- Мы получаем такую формулу: =ПСТР(А1;29;31-29+1). Оператор ПСТР позволяет извлечь из текстовой информации, указанной 1-м аргументом, начиная с заданной позиции (29) с числом знаков, заданным 3-м аргументом.
- В результате мы получаем:
=ПСТР(А1;29;31-29+1)- =ПСТР(А1;29;2+1)
- =ПСТР(А1;29;3)
- 763
Бывают ситуации обратные, когда нужно реализовать операцию извлечения односоставных текстовых данных, исключив числа.
Нам необходимо сохранить только текстовую информацию.
Специальная формула применяется по аналогичному алгоритму, что и вышерассмотренная. Она выглядит так: =ПСТР(А1;ПОИСК(«-«;А1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР(А1;ПОИСК(«-«;А1)+1;999);СТРОКА($1:$99);1));0)-1)
Здесь мы, благодаря фрагменту ПОИСК(«-«;А1) отыскали локацию тире, а затем при помощи оператора ПОИСКПОЗ нашли в извлеченных текстовых данных позицию 1-го числа. Передали эти показатели в оператор ПСТР, который реализовал все дальнейшие преобразования.
Второй метод: использование специального макроса
Эту процедуру можно реализовать при помощи специального макроса, созданного в табличном редакторе Эксель. К примеру, у нас есть табличка, в которой существует колонка с текстовой информацией и числовыми данными. Нам нужно оставить только числовые данные, убрав при этом текст.
В табличном редакторе отсутствует интегрированная функция, поэтому нам необходимо создать такой пользовательский макрос:
Public Function GetNumbers(TargetCell As Range) As String
Dim LenStr As Long
For LenStr = 1 To Len(TargetCell)
Select Case Asc(Mid(TargetCell, LenStr, 1))
Case 48 To 57
GetNumbers = GetNumbers & Mid(TargetCell, LenStr, 1)
End Select
Next
End Function
Подробная инструкция по созданию пользовательского макроса выглядит так:
- Используя специальную комбинацию клавиш «Alt+F11», производим открытие редактора VBA. Альтернативный вариант – нажать ПКМ по рабочему листу и выбрать элемент «Исходный текст».
- Реализуем создание нового модуля. Для осуществления этой процедуры жмем левой клавишей мышки на элемент, имеющий наименование «Insert», а затем выбираем объект «Module».
- Производим копирование кода, который располагается выше, и вставляем его в созданный модуль. Копирование реализуем при помощи сочетания клавиш «Ctrl+C», а вставку – «Ctrl+V».
- Теперь в необходимой ячейке, в которой мы планируем вывести только числовую информацию, вбиваем такую формулу: =GetNumbers(А1).
- Нам нужно растянуть формулу вниз на все ячейки колонки. Для этого наводим указатель на нижний правый уголок ячейки. Курсор принял форму небольшого плюсика темного цвета. Зажимаем левую клавишу мышки и протягиваем формулу вниз до окончания таблички.
- Готово! Мы реализовали извлечение числовой информации при помощи специального макроса.
Заключение и выводы о процедуре извлечение
Мы выяснили, что существует несколько способов, позволяющих реализовать извлечение числовой информации. Осуществить эту операцию можно при помощи создания специальных макросов в редакторе VBA или же используя массивную формулу. Каждый пользователь может самостоятельно выбрать для себя наиболее удобный способ, который позволит ему убрать текстовую информацию из ячейки и оставить при этом числовые данные.
Табличный процессор Эксель имеет огромное количество функций, позволяющих эффективно работать с большими объёмами информации и разнообразными вычислениями. Часто бывает так, что пользователю нужно удалить формулу, с помощью которой был рассчитан результат, а итоговый показатель оставить в ячейке. В статье будет рассмотрено несколько методов удаления формул из ячеек табличного процессора Excel.
Удаление формул
В табличном процессоре отсутствует интегрированный инструмент, предназначенный для удаления формул. Это действие возможно реализовать другими методами. Разберем каждый более подробно.
Метод 1: копируем значения с помощью параметров вставки
Первый вариант является самым быстрым и легким. Способ позволяет скопировать наполнение сектора и перенести его в альтернативное место, только уже без формул. Подробная инструкция выглядит следующим образом:
- Производим выделение ячейки или диапазона ячеек, которые будем в дальнейшем копировать.
- Жмем ПКМ по произвольному элементу выделенной области. Появляется небольшое контекстное меню, где следует выбрать элемент «Копировать». Альтернативный вариант копирования – использование комбинации клавиш «Ctrl+C». Третий вариант копирования значений – использование кнопки «Копировать», располагающейся на ленте инструментов раздела «Главная».
- Выбираем ту ячейку, в которую желаем вставить ранее скопированную информацию, кликаем по ней ПКМ. Открывается знакомое нам контекстное меню. Находим блок «Параметры вставки» и нажимаем на элемент «Значения», который имеет вид иконки с изображением последовательности цифр «123».
- Готово! Скопированная информация без формул перенеслась на новую выбранную область.
Метод 2: используем специальную вставку
Существует «Специальная вставка», которая помогает произвести копирование информации и вставить ее в ячейки с сохранением изначального форматирования. Вставленная информация не будет содержать формулы. Подробная инструкция выглядит следующим образом:
Метод 3: удаляем формулы в исходной таблице
Далее поговорим о том, как удалить формулы в первоначальной таблице. Подробная инструкция выглядит следующим образом:
- Производим копирование диапазона ячеек любым имеющимся способом. К примеру, используя комбинацию клавиш «Ctrl+C».
- Как и в ранее рассмотренном способе, производим вставку с сохранением первоначального форматирования в другой сектор рабочего листа. Не убирая выделение, снова производим копирование данных.
- Перемещаемся в сектор, находящийся в левом верхнем углу, нажимаем ПКМ. Появляется знакомое нам контекстное меню, в котором следует выбрать элемент «Значений».
- Наполнение ячеек без формул скопировалось в изначальное место. Теперь можно произвести удаление остальных таблиц, которые были нам необходимы для процедуры копирования. Выделяем при помощи ЛКМ дубликаты таблицы и кликаем по области выделения ПКМ. Появляется контекстное меню, в котором следует нажать на элемент «Удалить».
- На экране отобразилось небольшое окошко «Удаление ячеек». В нем можно выбрать, что именно удалить. Ставим пункт около надписи «Строку» и кликаем «ОК». В нашем примере в правой части от выделенной области нет ячеек с данными, поэтому вариант «Ячейки, со сдвигом влево» тоже подходит.
- Дубликаты таблиц полностью удалены с рабочего листа. Мы реализовали замену формул на конкретные показатели в исходной таблице.
Метод 4: удаляем формулы без копирования в другое место
Некоторых пользователей табличного процессора Эксель может не устроить предыдущий метод, так как он подразумевает большое количество манипуляций, в которых можно запутаться. Существует еще одна вариация удаления формул из исходной таблицы, но она требует внимательности со стороны пользователя, так как все действия будут проводиться в самой табличке. Важно делать все аккуратно, чтобы случайно не произвести удаление нужных значений или не «поломать» структуру данных. Подробная инструкция выглядит следующим образом:
Метод 5: применяем макрос
Следующий способ подразумевает применение макросов. Перед тем, как приступить к удалению формул из таблицы и замене их конкретными значениями, нужно произвести включение «Режима разработчика». Изначально в табличном процессоре этот режим находится в выключенном состоянии. Подробная инструкция включения «Режима разработчика»:
Подробная инструкция по применению макроса:
- Перемещаемся во вкладку «Разработчик», которая находится в верхней части интерфейса табличного процессора. Далее находим группу параметров «Код» и выбираем элемент “Visual Basic”.
- Выбираем нужный лист документа, а затем кликаем на элемент “View Code”. Проделать ту же самую операцию можно двойным кликом ЛКМ по нужному листу. После проведения этого действия на экране появляется редактор макросов. В поле редактора вставляем следующий код:
Sub Удаление_формул()
Selection.Value = Selection.Value
End Sub
Метод 6: удаляем формулу вместе с результатом вычислений
Случается, что пользователю табличного процессора Эксель необходимо не только реализовать удаление формул, но и удалить результаты расчётов. Подробная инструкция выглядит следующим образом:
- Как и во всех предыдущих способах, начинаем свою работу с выделения диапазона, в котором располагаются формулы. Затем кликаем ПКМ по области выделения. На экране появилось контекстное меню. Находим элемент «Очистить содержимое» и нажимаем на него. Альтернативный вариант удаления – нажатие на клавишу «Delete».
- В результате проведенных манипуляций удалились все данные в выделенных ячейках.
Удаление формулы с сохранением результатов
Рассмотрим подробно, как удалить формулу с сохранением результата. Этот способ предполагает использование свойства «Вставить значения». Подробная инструкция выглядит следующим образом:
- Выделяем ячейку или же диапазон, где располагается необходимая нам формула. Если она является формулой массива, то первоначально следует произвести выделение всех ячеек в диапазоне, в которых содержится формула массива.
- Нажимаем на ячейку в формуле массива.
- Переходим в раздел «Главная» и находим блок инструментов «Редактирование». Здесь кликаем на элемент «Найти и выделить», а затем на кнопку «Перейти».
- В следующем окошке нажимаем на «Дополнительный», а затем на элемент «Текущий массив».
- Возвращаемся в раздел «Главная», находим элемент «Копировать» и щелкаем на него.
- После проведения процесса копирования нажимаем на стрелочку, которая находится под кнопкой «Вставить». На последнем этапе жмем «Вставить значения».
Удаление формулы массива
Чтобы произвести процедуру удаления формулы массива, первоначально нужно убедиться, что выделены все ячейки в диапазоне, содержащие нужную формулу. Подробная инструкция выглядит следующим образом:
- Выбираем нужный сектор в формуле массива.
- Перемещаемся в раздел «Главная». Находим блок инструментов «Редактирование» и жмем на элемент «Найти и выделить».
- Далее нажимаем «Перейти», а после на элемент «Дополнительный».
- Щёлкаем на «Текущий массив».
- В завершении процедуры нажимаем «Delete».
Заключение
Подводя итог, можно сказать, что в удалении формул из ячеек табличного процессора нет ничего сложного. Существует огромное количество методов удаления, благодаря чему каждый сможет подобрать для себя наиболее удобный.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
- Было доставлено кусков мыла 763шт.
- Всего пришло 34
- Тюбики - 54 доставлено
- и т.д.
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст " Было доставлено кусков мыла 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 "*7*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*1*" And Mid(sWord, i + 1, 1) Like "*1*" 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 "*3*" Or Not Mid(sWord, i + 1, 1) Like "*4*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_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 "*2*" Then
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then
Число из текста и наоборот.xls (99,0 KiB, 17 001 скачиваний)
Читайте также: