Как удалить текст из ячейки в excel и оставить только цифры
Обновлено: 07.07.2024
Добрый день. Возник вопрос, как можно удалить все текстовые значение (рус и анг) из ячейки, и оставить только цифры.
в ASAP Util есть такая функция, но работает только с английским языком.
Добрый день. Возник вопрос, как можно удалить все текстовые значение (рус и анг) из ячейки, и оставить только цифры.
в ASAP Util есть такая функция, но работает только с английским языком.
в ASAP Util есть такая функция, но работает только с английским языком.
Подскажите пожалуйста. Автор - qpp
Дата добавления - 04.07.2012 в 12:06
Excel 2003 EN, 2013 EN
И ещё Автор - Формуляр
Дата добавления - 04.07.2012 в 12:17
Формула массива (вводить одновременным нажатием Контрл, Шифт, Ентер)
[vba] [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 Для А1
Формула массива (вводить одновременным нажатием Контрл, Шифт, Ентер)
[vba] [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 _Boroda_ [/vba]
Можно блок ДВССЫЛ("A1:A"&ДЛСТР(A1)) заменить на, допустим, D$1:D$1000 или вообще $1:$1000 Автор - _Boroda_
Дата добавления - 04.07.2012 в 12:30
Интересное решение, попробую разобраться на досуге.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
формула не работает.
Интересное решение, попробую разобраться на досуге.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
формула не работает.
Но меня все таки интересует момент, как можно убрать из ячейки все буквы.
формула не работает.
Автор - ГостьДата добавления - 04.07.2012 в 12:43 прошу прощение, оставил запись как гость. не до конца еще освоил форум. прошу прощение, оставил запись как гость. не до конца еще освоил форум. qpp
93937059290058298022158901014112251 - солить будем?
93937059290058298022158901014112251 - солить будем? RAN
Быть или не быть, вот в чем загвоздка!
93937059290058298022158901014112251 - солить будем? Автор - RAN
Дата добавления - 04.07.2012 в 12:55
прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками.
прошу прощения, принял к сведению и буду выкладывать файл впредь для примера.
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. qpp
в итоге я бы хотел убрать все буквы из ячейки, и получить (вместе со знаками табуляции) набор цифр, после из него вывести закономерность,
упаковки - кол-во, они попадаются в нескольких комбинациях,
я вначале привел для примера ASAP Util которая это делает, но только с англ. знаками. Автор - qpp
Дата добавления - 04.07.2012 в 13:00
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 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 "*5*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*8*" And Mid(sWord, i + 1, 1) Like "*6*" 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 "*1*" 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 "*8*" Then
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then
Число из текста и наоборот.xls (99,0 KiB, 16 527 скачиваний)
Довольно часто в Excel нужно убрать из строки символы до определенного знака. Это может быть как один символ (пробел, точка, запятая, двоеточие и т.д.), так и символ из определенного множества (цифра, английская буква).
В зависимости от этих условий, решение может быть простым, а может потребовать использование довольно сложных формул и процедур.
В этой статье я подробно опишу известные мне варианты. Поехали!
Удалить текст до последнего символа
Наиболее просто удалить текст в диапазоне ячеек до последнего вхождения заданного символа или текста. Можно воспользоваться процедурой «Найти и заменить»:
- Выделить диапазон
- Вызвать процедуру поиска и замены со вкладки «Главная» или сочетанием клавиш Ctrl+H
- использовать подстановочный символ * (звездочку) прямо перед искомым в строке поиска
- оставить пустым второе поле и нажать ОК
Процедура заменит все символы до знака на пустоту, иначе говоря, удалит их. Звездочка как раз и обозначает «все символы».
Удалить текст до первого пробела — формула
Данная формула универсальна и позволит удалить не только текст до первого пробела, но и до любого другого первого вхождения символа в строке.
Однако удалить первое слово в ячейке — самая популярная задача, и пробел как раз и является символом, который отгораживает первое слово слева от остального текста.
Формулы для других символов аналогичны:
Убрать текст в ячейке до первой запятой:
А чтобы удалить текст до вхождения последовательности символов, например, слова, нужно использовать в формуле длину этой последовательности, сократив ее на 1:
Здесь из адресов удаляется текст до номера дома. Длина текста в кавычках — 3 символа (д, точка и пробел):
Удалить текст до первой цифры
Пожалуй, вы уже догадались, но я все же объясню, как работают формулы выше:
-
ищет позицию первого вхождения текстового паттерна (он всегда в кавычках). Соответствующий фрагмент выделен красным. принимает эту позицию как аргумент;
- она же заменяет фрагмент от первого символа до этой позиции на пустоту (две кавычки подряд), таким образом, удаляя его.
Следовательно, чтобы удалить текст до первой цифры, нужно вычислить позицию первой цифры в строке. Поскольку цифр не одна, а 10, простая формула уже не подойдет. Но можно сделать это вот такой формулой массива:
А подставив это выражение в исходную формулу, мы сможем решить исходную задачу — получить текст после первой цифры включительно, удалив текст до нее.
Что за числа 48 и 57, можно прочитать подробнее тут:
Обратите внимание на фигурные скобки! Их не нужно вводить, они появятся сами при вводе формулы сочетанием клавиш Ctrl+Shift+Enter.
Формула выдаст ошибку, если цифр в строке нет.
Удаление текста в 1-2 клика с !SEMTools
Каждый из перечисленных способов хорош по-своему, но всем им недостает одного важного фактора — скорости исполнения.
В каждом из случаев нужно вводить много символов и производить много действий.
Для экономии времени я включил быстрые процедуры в меню своей надстройки для Excel — !SEMTools.
Теперь удалить текст до первого или последнего вхождения конкретного символа или подстроки, включая и не включая сам текст, можно в считанные мгновения!
При этом можно как менять данные на месте, так и автоматически выводить в соседний столбец — за это отвечает глобальный чекбокс.
Удаляем символы в тексте до первого или последнего вхождения определенного текста с !SEMToolsХотите так же быстро обращаться с текстовыми данными в Excel и испытать на практике еще более 500 похожих процедур?
Жмите на кнопку ниже, чтобы бесплатно скачать и подключить пробную версию надстройки !SEMTools. Процедура займет пару минут.
Встречаются задачи, в которых необходимо сделать разделение символов, когда одни символы нужно удалить, а другие оставить, например, удалить любые буквы, оставив только цифры или удалить из текста, написанного кириллицей, все латинские буквы.
Простой способ быстро удалить из текста ячеек рабочего листа Excel буквы, цифры и лишние символы - использовать надстройку для Excel. Все максимально просто, пользователю не нужно писать никаких формул, достаточно задать диапазон ячеек, выбрать определенный набор символов и запустить программу.
Как удалить цифры из текста?
Программа осуществляет перебор символов в каждой ячейке и удаляет из значений ячеек только те символы, которые задал пользователь в диалоговом окне надстройки. Так, чтобы удалить из значений ячеек все цифры, нужно выбрать пункт «Только цифры».
Как удалить буквы из текста?
Чтобы удалить буквы из текста, необходимо в диалоговом окне надстройки выбрать пункт «Любые буквы», при этом в тексте ячеек цифры и знаки остаются, а все буквы, независимо от того русские они или английские, удаляются. Предусмотрено удаление из текста только латиницы (букв латинского алфавита) или только кириллицы (букв русского алфавита).
Как удалить лишние символы?
Для удаления лишних символов, необходимо выбрать пункт «Только знаки», при этом все символы кроме цифр и букв удаляются, но имеется возможность задать символы-исключения, например, чтобы все слова не слились в одну строку, можно установить флажок в поле «а также символы:» и вписать пробел. В поле для символов-исключений можно вписать любой другой символ, либо группу символов, друг за другом без пробелов или других разделителей.
Как удалить только заданные символы?
В случае, если необходимо удалить из значений ячеек отдельные символы из разных групп, предусмотрена возможность перечислить все символы, подлежащие удалению через точку с запятой. Для удаления самого символа ";" (точка с запятой) можно использовать опцию удаления по ASCII-кодам. Например, символу ";" соответствует ASCII-код "59". Эта опция будет также полезна при необходимости удаления непечатаемых символов, например символа перевода на новую строку.
Выборочное удаление символов из ячеек Excel
Надстройка позволяет пользователям в выбранном диапазоне ячеек осуществлять выборочное удаление из значений:
2) цифр и любых символов (кроме букв);
3) любых букв, как русского, так и английского алфавита;
4) любых букв и знаков;
5) любых букв и цифр;
6) только знаков (всех, кроме букв и цифр);
7) только букв кириллицы;
8) букв кириллицы и знаков;
9) только букв латиницы;
10) букв латинского алфавита и любых знаков;
11) символов, заданных вручную либо по ASCII-кодам.
При удалении группы символов программа позволяет создавать исключения из группы в поле "кроме символов", то есть задавать символы, которые не будут удаляться.
Удаление символов из выделенного текста документа Word
Надстройка дает возможность удалять группы символов в выделенном тексте документа Word:
1) изменять цвет шрифта у заданных символов;
2) удалять заданные символы;
3) создавать исключения из заданной группы символов;
4) осуществлять выбор одной из групп символов (цифры, знаки, буквы латиницы, буквы кириллицы и сочетания этих групп);
Читайте также: