Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

Убрать лишние данные из ячейки

Автор nick79087, 29.05.2012, 18:23

« назад - далее »

nick79087

Добрый день!
существует таблица с такими данными в столбце:
Ноутбук HP ProBook 4530s i5-2450M 15.6 HD AG 4096/ 750/ DVDRW/ HD6490/ BT/ WiFi/ Cam/ Bag/ W7HP
Возможно ли сделать макрос, который игнорировал первое слово, потом брал следующие 3 и копировал их в новый столбец, и игнорировал все остальное. То есть в итоге в новом столбце должно быть:
HP ProBook 4530s
Критерием для отбора должны быть пробелы между словами, я так понимаю.
Заранее благодарен.

Barbarian12

#1
При условии, что в тексте нет символов "!!":
=ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ("!!";ПОДСТАВИТЬ(A1;" ";"!!";3);1)-3)

GWolf

Цитата: nick79087 от 29.05.2012, 18:23
...
Возможно ли сделать макрос, который игнорировал первое слово, потом брал следующие 3 и копировал их в новый столбец, и игнорировал все остальное. ...

Пожалуйста:
Sub razbor()
    Dim nR As Long, nC As Long
    Dim stroka As String
    Dim arrSborka() As String
   
    'УСТАНОВИМ ИСХОДНЫЕ ЗНАЧЕНИЯ:
    nC = 2 '- номер столбца с данными для разбора
    nR = 3 '- стартовый номер строки столбца с данными для разбора
   
    'Поехали!
    With ThisWorkbook.ActiveSheet
        Do
            stroka = .Cells(nR, nC).Text
           
            arrSborka = Split(stroka, " ", -1, vbTextCompare)
           
            .Cells(nR, nC + 1) = arrSborka(1) & " " & arrSborka(2) & " " & arrSborka(3)
           
            Erase arrSborka
           
            nR = nR + 1
        Loop While .Cells(nR, nC).Text <> ""
    End With
End Sub


P.S.
Если у Вас данные расположены в другом столбце, а "по умолчанию" во 2-ом и, начало данных не в 3-ей строке то, поменяйте значения номера столбца и (или) строки вот здесь:     'УСТАНОВИМ ИСХОДНЫЕ ЗНАЧЕНИЯ:
    nC = 2 '- номер столбца с данными для разбора
    nR = 3 '- стартовый номер строки столбца с данными для разбора
в коде макроса. И еще, макрос будет работать с ЛЮБЫМ активным листом в текущей рабочей книге Excel.
Путей к вершине - множество. Этот один из многих!

nick79087

Спасибо огромное! Формула работает тоже, но не совсем так хотелось бы. Просто длина слов в ячейках разная, бывает неверно обрезает. А вот макрос в 90 процентов делает все так, как нужно. И как раз остальные 10% можно допилить при помощи доработанной для конкретного случая формулы. Еще раз - большое спасибо за помощь!

GWolf

Добрый вечер!
Цитата: nick79087 от 30.05.2012, 11:03... И как раз остальные 10% можно допилить при помощи доработанной для конкретного случая формулы. ...
Если это Вас не затруднит, опишите, пожалуйста, что представляют эти 10%. Я полагаю, что Вам будет интересно, если макрос будет доработан и будет выполнять 100% полрученной ему работы.

С уважением, GWolf.
Путей к вершине - множество. Этот один из многих!

wildnew

А возможно ли делать похожую процедуру (желательно как можно проще) вот с такими данными:
Имеем 1 столбец:
БС456,джыо аподжыв ажлд
БС36,юш зяфщ дар жлыо вап
БС563.до ывш апэщ32
БС4 дырашэщп

Требуется:
456
36
563
4

Суть: оставить только первые цифры независимо одна две или три их там, текст перед ними и после удалить независимо от того что в нем.
?

kuklp

Я, как всегда, чертовски адекватен... Email: pilipnikop@yandex.ua WM Z206653985942, R334086032478, U238399322728, E332314026771

Barbarian12

#7
Попробуйте эту формулу (вводить как формулу массива, зажав ctrl+shift+enter)
=ПСТР(A1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(--ПСТР(A1;СТРОКА($1:$7);1));0);СЧЁТ(--ПСТР(A1;СТРОКА($1:$7);1)))

Единственный минус - формула будет выдавать ошибку, если среди первых 7 символов ячейки будут разрозненные цифры (например, БС563х9до ывш апэщ). В таком случае цифру 7 в СТРОКА следует уменьшить до нужного количества.

GWolf

#8
Добрый день!

Цитата: Barbarian12 от 11.07.2012, 17:09
... Единственный минус - формула будет выдавать ошибку, если среди первых 7 символов ячейки будут разрозненные цифры (например, БС563х9до ывш апэщ). В таком случае цифру 7 в СТРОКА следует уменьшить до нужного количества.

Осмелюсь заметить:
Цитироватьоставить только первые цифры независимо одна две или три их там
Если я правильно понял, то "вытащить" нужно ПЕРВУЮ СТОЯЩИХ ПОДРЯД группу цифр?

На скрепке - пример реализации.

Sub otbor()
    Dim nR As Long, nC As Long
    Dim stroka As String, simwol As String, nabor As String
    Dim i As Integer
    Dim flg0 As Boolean, flg1 As Boolean
   
    nR = 4 '- стартовое значение счетчика строк
    nC = 2 '- номер рабочей колонки
    stroka = "" '- переменная для значения обрабатываемой ячейки
   
    With ThisWorkbook '- В этой рабочей книге
        With .Worksheets("Lt1") '- на листе "Lt1"
            'в ходе выполнения цикла Do ... Loop
            Do 'в каждом шаге цикла
                stroka = .Cells(nR, nC).Text 'в переменную stroka передаем значение _
                                              ячейки с координатами nR, nC, как Text
               
                flg0 = False '- значение флага указывающего что первый очередной _
                                символ - число в False
                simwol = "" '- стартовое значение переменной для одного символа строки
                nabor = "" '- стартовое значение переменной для отобранного числа
               
                i = 0 '- стартовое значение счетчика
                'цикл перебора символов строки
                For i = 1 To Len(stroka) 'от первого до последнего, равного _
                                          длинне строки
                    simwol = Mid(stroka, i, 1) 'передаем в переменную simwol, значение _
                                               очередного символа строки
                    flg1 = False '- значение флага указывающего, что очередной _
                                    символ - число
                    If IsNumeric(simwol) = True Then '- если очередной символ - число, то
                        If flg0 = False Then flg0 = True '- если он первый, то flg0 в True
                        flg1 = True '- флаг очередного символа - числа, в True
                    End If
                   
                    If flg0 = True And flg1 = True Then '- если оба флага в True
                        nabor = nabor & simwol '- к отобранному числу добавляем _
                                                  очередной символ
                        simwol = "" '- "чистим" переменную
                    ElseIf flg0 = True And flg1 = False Then 'если flg0 = True и flg1 = False, _
                                                              т.е. пошли после цифр буквы, то
                        Exit For '- закончить выполнение цикла
                    End If
                Next i
               
                'число, получившееся в переменной nabor, при анализе очередной ячейки, записать в _
                 ячейку расположенную в той же строке, что и анализируемая и на 1 колонку правее:
                .Cells(nR, nC + 1) = nabor
                nabor = "" '- "чистим" переменную
               
                nR = nR + 1 '- увеличиваем значение счеетчика строк на единицу.
            Loop While .Cells(nR, nC).Text <> "" '- выполнять цикл Do ... Loop, пока в ячейке _
                                                    есть текст
        End With
    End With
End Sub
Путей к вершине - множество. Этот один из многих!

wildnew

Спасибо огромное, да формула может быть и дает ошибки, но в моем случае работает.
Макрос тоже работает (в принцип еще не вдавался, но как я понимаю он ошибаться не должен).
Для меня проще - лучше.
Ну а вообще есть пара идей где может пригодиться макрос, когда нужно сделать чтонибудь  по требованию. Попробую вникнуть как они работают.
Спасибо еще раз.

GWolf

Что ж, рад был помочь.
Постарался максимально записать код с пояснениями. Но, тем не менее, если возникнут вопросы - буду рад ответить.
Путей к вершине - множество. Этот один из многих!

Танюшка Викторова

Помогите!!!!!!
эксель почти не знаю, как  из ячеек в которой к примеру по 7 цифр мне надо убрать всего 4
Например - 19804569 , 19801236 , 19802587 , 19974567 , 19971478
первый 4 цифры, разом как нибудь можно??

ShAM


zs

#13
Цитата: wildnew от 11.07.2012, 16:32
Суть: оставить только первые цифры независимо одна две или три их там, текст перед ними и после удалить независимо от того что в нем.
?
=ЛЕВСИМВ(ПСТР(E9;ПОИСК("1";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(E9;0;"1");1;"1");2;"1");3;"1");4;"1");5;"1");6;"1");7;"1");8;"1");9;"1"));77777);ДЛСТР(ПСТР(E9;ПОИСК("1";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(E9;0;"1");1;"1");2;"1");3;"1");4;"1");5;"1");6;"1");7;"1");8;"1");9;"1"));77777))-(ДЛСТР(E9)-ПОИСК(СИМВОЛ(КОДСИМВ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(E9;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");ПОИСК("1";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(E9;0;"1");1;"1");2;"1");3;"1");4;"1");5;"1");6;"1");7;"1");8;"1");9;"1"));77777)));E9;1)+1))

vikttur

Цитировать...оставить только первые цифры независимо одна две или три их там, текст перед ними и после удалить независимо от того что в нем
Для любого количества цифр  в первом числе (в формуле ограничено 50-ю), формула массива:
=ПСТР(E1;МИН(ПОИСК({1;2;3;4;5;6;7;8;9;0};E1&1234567890));СЧЁТ(1/-ЛЕВСИМВ(ПСТР(E1;МИН(ПОИСК({1;2;3;4;5;6;7;8;9;0};E1&1234567890));50);СТРОКА($1:$50))))