Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Извлечение опредленных 5 цифр из содержимого ячейки
 
#1
21.06.2013 15:01:01
Помогите ! бух выгружает карточку счета и там етсь 5 цифр(код операции).
 Как показала практика они могут стоять где угодно - в начале, конце , в середине текста, на новой строке текста, а так же их может и не быть вовсе (если бух забыл ее поставвить).
  Необходимо вытащить только этот пятизначный код а если его нет то вывести ошибку.
 Смотрел на форуме пятался модернизировать имеющиеся решения но закопался и сдался.
Жду вашей помощи заранее спасибо, пример во вложении.
 
 
 
#2
21.06.2013 15:03:10
регуряные выражения. Сейчас попробую что родить
 
 
 
#3
21.06.2013 15:10:03
проверяйте, можно будет паттерном поиграть
 
 
 
#4
21.06.2013 15:18:05
Приверил, чуток не так работает. Если в ячейке отсутствует КОд, а есть еще "счет" с набором цифр, то берет первые пять цифр счета. с этим и вся заморочка что помимо даты кода там етсь еще счет в основном он 10 значный ...
 
 
 
#5
21.06.2013 15:18:37
все равно спасибо чот так быстро отклинулись
 
 
 
#6
21.06.2013 15:36:45
=ЕСЛИ(ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ");ПОИСК(" ????? ";ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ");СИМВОЛ(10);" "))+1;5));ПСТР(ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ");ПОИСК(" ????? ";ПОДСТАВИТЬ(A2;СИМВОЛ(10);" "))+1;5);ПСТР(A2;ПОИСК(" ????? ";ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ");ПОИСК(" ????? ";A2)+1)+1;5))

Ошибки, правда, всё равно будут.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#7
21.06.2013 15:39:08
Код
=ПЕЧСИМВ(A2)   =СЖПРОБЕЛЫ(B2)   =ПОИСК(" ????? ";C2)   =ПСТР(C2;D2+1;5)   =ПОИСК(" ????? ";C2;D2+1)            =ЗНАЧЕН(E2)
=ПЕЧСИМВ(A3)   =СЖПРОБЕЛЫ(B3)   =ПОИСК(" ????? ";C3)   =ПСТР(C3;D3+1;5)   =ПОИСК(" ????? ";C3;D3+1)            =ЗНАЧЕН(E3)
=ПЕЧСИМВ(A4)   =СЖПРОБЕЛЫ(B4)   =ПОИСК(" ????? ";C4)   =ПСТР(C4;D4+1;5)   =ПОИСК(" ????? ";C4;D4+1)            =ЗНАЧЕН(E4)
=ПЕЧСИМВ(A5)   =СЖПРОБЕЛЫ(B5)   =ПОИСК(" ????? ";C5)   =ПСТР(C5;D5+1;5)   =ПОИСК(" ????? ";C5;D5+1)            =ЗНАЧЕН(E5)
=ПЕЧСИМВ(A6)   =СЖПРОБЕЛЫ(B6)   =ПОИСК(" ????? ";C6)   =ПСТР(C6;D6+1;5)   =ПОИСК(" ????? ";C6;D6+1)   =ПСТР(C6;F6+1;5)         =ЗНАЧЕН(G6)
=ПЕЧСИМВ(A7)   =СЖПРОБЕЛЫ(B7)   =ПОИСК(" ????? ";C7)   =ПСТР(C7;D7+1;5)   =ПОИСК(" ????? ";C7;D7+1)            =ЗНАЧЕН(E7)
=ПЕЧСИМВ(A8)   =СЖПРОБЕЛЫ(B8)   =ПОИСК(" ????? ";C8)   =ПСТР(C8;D8+1;5)   =ПОИСК(" ????? ";C8;D8+1)            =ЗНАЧЕН(E8)
=ПЕЧСИМВ(A9)   =СЖПРОБЕЛЫ(B9)   =ПОИСК(" ????? ";C9)   =ПСТР(C9;D9+1;5)   =ПОИСК(" ????? ";C9;D9+1)            =ЗНАЧЕН(E9)
=ПЕЧСИМВ(A10)   =СЖПРОБЕЛЫ(B10)   =ПОИСК(" ????? ";C10)   =ПСТР(C10;D10+1;5)   =ПОИСК(" ????? ";C10;D10+1)            =ЗНАЧЕН(E10)
=ПЕЧСИМВ(A11)   =СЖПРОБЕЛЫ(B11)   =ПОИСК(" ????? ";C11)   =ПСТР(C11;D11+1;5)   =ПОИСК(" ????? ";C11;D11+1)            =ЗНАЧЕН(E11)
=ПЕЧСИМВ(A12)   =СЖПРОБЕЛЫ(B12)   =ПОИСК(" ????? ";C12)   =ПСТР(C12;D12+1;5)   =ПОИСК(" ????? ";C12;D12+1)            =ЗНАЧЕН(E12)
=ПЕЧСИМВ(A13)   =СЖПРОБЕЛЫ(B13)   =ПОИСК(" ????? ";C13)   =ПСТР(C13;D13+1;5)   =ПОИСК(" ????? ";C13;D13+1)            =ЗНАЧЕН(E13)
=ПЕЧСИМВ(A14)   =СЖПРОБЕЛЫ(B14)   =ПОИСК(" ????? ";C14)   =ПСТР(C14;D14+1;5)   =ПОИСК(" ????? ";C14;D14+1)   =ПСТР(C14;F14+1;5)   =ПОИСК(" ????? ";C14;F14+1)      =ЗНАЧЕН(G14)

Вот часть того, что я придумал. Я на работе, поэтому не могу файл приложить. Думаю можно доработать еще. Хотел бы отметить что нужно работать с фильтрами и предоставлять систематические данные, то есть где 5 цифр отделяются справа и слева пробелом. Если цифры стоят строго в конце или в начале, то здесь опять же можно фильтровать.
Изменено: Все_просто - 21.06.2013 15:40:21
С уважением,
Федор/Все_просто
 
 
 
#8
21.06.2013 16:01:14
Код
Sub F()

    Dim re As Object, mc As Object, i As Long
    
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "\s\d{5}\s"
    
    For i = 1 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
         Set mc = re.Execute(Cells(i, 1))
         If mc.Count > 0 Then
            Cells(i, 2) = mc(0)
        End If
    Next

End Sub
There is no knowledge that is not power
 
 
 
#9
21.06.2013 16:20:27
Владимир - спасибо но действительно проскакивают ошибки, а это бух отчетность.. от меня требуют без них
Все_просто  - не много не понял как все там работает
Johny -  макрос не работает почему то или я не догоняю к концу дня, можно сделать функ ???
 
 
 
#10
21.06.2013 16:26:41
вариант UDF:
Код
Function Num_5$(ByVal s$)
    Dim i&
    s = " " & s & " "
    For i = 1 To Len(s) - 6
        If Mid$(s, i, 7) Like "[!0-9]#####[!0-9]" Then Num_5 = Mid$(s, i + 1, 5): Exit For
    Next i
End Function
 
 
 
#11
21.06.2013 16:29:49
Func
There is no knowledge that is not power
 
 
 
#12
21.06.2013 16:33:40
Цитата
ambruxa пишет: Необходимо вытащить только этот пятизначный код а если его нет то вывести ошибку.
... спасибо но действительно проскакивают ошибки, а это бух отчетность.. от меня требуют без них
Это как?
There is no knowledge that is not power
 
 
 
#13
21.06.2013 16:38:01
каким то образом в результате появлетс текст
 
 
 
#14
21.06.2013 16:43:11
Так я и спрашиваю - вы просите, чтобы выводилась ошибка в случае ненахождения, а теперь жалуетесь, что ошибка появляется. Однако!  :D
There is no knowledge that is not power
 
 
 
#15
21.06.2013 16:50:30
формульный вариант:
Код
=ПРОСМОТР(2;1/НЕ(МУМНОЖ(ABS(ЕЧИСЛО(-ПСТР(" "&A1&" ";СТРОКА($1:$199)+{0;1;2;3;4;5;6};1))-{0;1;1;1;1;1;0});{1:1:1:1:1:1:1}));ПСТР(A1;СТРОКА($1:$199);5))
 
 
 
#16
21.06.2013 17:02:37
Цитата
Johny пишет:
Так я и спрашиваю - вы просите, чтобы выводилась ошибка в случае ненахождения, а
теперь жалуетесь, что ошибка появляется.
Отвечу за ambruxa
Формула Владимира не корректно работает:
Для ячейки A11 - не находит код 10101
Для ячеек A72, A91, A92, A109 выдает текст: "учет)", "Д. ИП", "Д. ИП", "0004С" - соответственно
Хотя должна выдавать значение ошибки или пустую строку, т.к. кода в этих ячейках нет
Изменено: MCH - 21.06.2013 17:03:29
 
 
 
#17
21.06.2013 17:10:21
Я в ВБА не силен, поэтому предложу алгоритм действий. Используем оператор like. Там можно использовать регулярные выражения, то есть выражение " ##### " как раз и выделяет нужный нам ряд. Делаем два массива: один - для искомых значений, а второй - для результирующих значений. Для получения значений второго массива выискиваем с помощью if... then... нужные значения.

ПС предварительно нужно искомые значения привести к форме, позволяющей потом можно было использовать вышеприведенный алгоритм.
Изменено: Все_просто - 21.06.2013 17:12:33
С уважением,
Федор/Все_просто
 
 
 
#18
21.06.2013 17:15:45
Я извиняюсь - моя формула работает? :D
There is no knowledge that is not power
 
 
 
#19
21.06.2013 17:17:12
Цитата
Johny пишет:
Func
Johny, Ваша функция возвращяет текст длиной 7 знаков (5 цифр с символами слева и справа), при этом не находя код в ячейке A11

Цитата
Все_просто пишет:
Я в ВБА не силен, поэтому предложу алгоритм действий. Используем оператор like.
Там можно использовать регулярные выражения, то есть выражение " ##### " как раз
и выделяет нужный нам ряд
А в сообщение #10 что написано?
 
 
 
#20
21.06.2013 17:30:14
Цитата
MCH пишет:
Johny, Ваша функция возвращяет текст длиной 7 знаков (5 цифр с символами слева и справа), при этом не находя код в ячейке A11
ТС не уточнил, в каком виде могут появляться эти 5 цифр. В большинстве случаев слева и справа пробелы. В 11-ой строке справа нет пробела. Тогда поменять так:
Код
re.Pattern = "\d{5}"
Изменено: Johny - 21.06.2013 17:30:49
There is no knowledge that is not power
 
 
 
#21
21.06.2013 17:39:56
Цитата
Johny пишет: ТС не уточнил, в каком виде могут появляться эти 5 цифр. В большинстве случаев
слева и справа пробелы.
Как раз уточнил:
Цитата
ambruxa пишет: Как показала практика они могут стоять где угодно - в начале, конце , в середине
текста, на новой строке текста, а так же их может и не быть вовсе
Т.е. совершенно по любому, и разделены от текста не обязательно пробелом
Цитата
Тогда поменять так:
re.Pattern = "\d{5}"
Не вижу принципиальной разницы от варианта pharmaprofi, который возвращает первые цифры счета, а не кода, если в тексте имеется счет, а кода нет, либо счет стоит раньше кода
Изменено: MCH - 21.06.2013 19:14:50
 
 
 
#22
21.06.2013 19:02:20
Цитата
MCH пишет:
и в ячейке отсутствует КОд, а есть еще "счет" с набором цифр, то берет первые пять цифр счета. с этим и вся заморочка что помимо да

re.Pattern = "\W\d{5}\W"

\W  -символ не буква.
потом от результата орезеть первый и последний символ

Может как то так поробовать... сейчас проверить негде
Изменено: pharmaprofi - 21.06.2013 19:04:21
 
 
 
#23
21.06.2013 19:02:52
проверяйте
Код
Function CheckCode$(s As String)
  With CreateObject("vbscript.regexp")
    .Global = True
    .MultiLine = True
    .Pattern = "(^|[^\d])(\d{5,5})([^\d]|$)"
    Set x = .Execute(s)
    If x.Count = 1 Then CheckCode = x.Item(0).submatches(1)
  End With
End Function
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#24
21.06.2013 19:35:57
ikki , с моей формулой совпадает
Решил проверить на скорость, регулярные выражения и Mid+Like, результат не в пользу регулярных по текущим исходным данным
 
 
 
#25
21.06.2013 20:05:28
регулярками просто иногда проще пользоваться.
а скорость у них не ахти, это да.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#26
21.06.2013 21:36:25
=ПСТР(A2;ПОИСК("\\\\\";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;0;"\");1;"\");2;"\");3;"\");4;"\");5;"\");6;"\");7;"\");8;"\");9;"\"));5)
 
 
 
#27
22.06.2013 03:26:17
Скорость регулярных выражений зависит от простоты шаблона и оптимальности кода.
Вот так будет корректно и достаточно быстро:
Код
Function Account(Txt As String) As String
  Static RegEx As Object
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = "\D(\d{5})\D"
  End If
  With RegEx.Execute(" " & Txt & " ")
    If .Count > 0 Then Account = .Item(0).SubMatches(0)
  End With
End Function


Если сравнивать с Mid, то при коротких строках Mid немного быстрее (см. ниже), а при длинных строках и дальнем совпадении Mid может быть в разы медленнее. С регулярными обычно удобнее, т.к. код практически не меняется, нужно лишь пошаманить с шаблоном (Pattern)
Тест: у меня на данных Автора темы: Mid - 1.437 мсек, RegEx - 1.625 мсек
Изменено: ZVI - 22.06.2013 05:53:41
 
 
 
#28
22.06.2013 04:49:41
Цитата
ZVI пишет:
RegEx.Execute(" " & Txt & " ")
о! удобный и эффективный приём "обхода" проблемы с началом и концом строки.
спасибо, Владимир.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
Страницы: 1
Читают тему
Наверх