Копирование части из ячейки.

Дата: Понедельник, 26.12.2016, 00:51 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 176

Репутация: 0 ±

Замечаний: 40% ±

И снова здраствуйте, Есть небольшой прайслист на 10000 строк. В первой колонке, в ячейках идет название товаров, а в 9ю колонку, по темже строкам, мне нужно выдернуть одну часть текста. Проблема заключаеться в том, что данная часть текста, являеться модельным номером оборудование и не всегда находиться вторым словом, в надписи. Бывает третьим словом, четвертым, пятым и т.д. Я не знаю как выследить закономерность чтоб составить правильную формулу, одну для всех позиций, тем более мне сложнее попросить помочь с макросом.

Например:
Монитор 17' Acer V176LBMD (UM.BV6EE.005)
Asus ZenFone Go (ZB452KG) DS Black
Монитор LCD Asus 18.5' VS197DE D-Sub (90LMF1001T02201C-)
Моноблок ASUS Z240ICGK-GC029X (90PT01E1-M01190)
Неттоп ASUS UN65H-M107M (90MS00S1-M01070)
ПК ASUS UN62-M003M (90MS00A1-M00030)

Жирным шрифтом, я выделил значения которые, каждый раз при помощи формул:
2 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]2[/b]-1)*250+1;250))

3 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]3[/b]-1)*250+1;250))

4 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]4[/b]-1)*250+1;250))

и вставляю в 9ый столбец.

Соответственно, ручками меняя номер куска текста в ячейке, я добиваюсь поставленой задачи. Но это так изматывает. Если кто может помочь с небольшим макросом, Если возможно этот нелегкий процес оптимизировать - буду премного благодарен.
Спасибо. Файл пример прилагаю. В нем цветом -красный, выделил значения которые нужны в 9ом столбце.

P.S. В основном нужные мне значения идут сразу до скобок. Такие вещи, как в этом наименование,т.е. значение в скобках, можно не брать во внимание: Asus ZenFone Go (ZB452KG) DS Black - бывают очень редко, и значения со скобок можно просто НЕ переносить, оставляя ячейку в 9ом столбце пустой.

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]2[/b]-1)*250+1;250))

3 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]3[/b]-1)*250+1;250))

4 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]4[/b]-1)*250+1;250))

К сообщению приложен файл: proba-pera.xlsm (45.3 Kb)

Сообщение отредактировал wwizard - Понедельник, 26.12.2016, 14:05

СообщениеИ снова здраствуйте, Есть небольшой прайслист на 10000 строк. В первой колонке, в ячейках идет название товаров, а в 9ю колонку, по темже строкам, мне нужно выдернуть одну часть текста. Проблема заключаеться в том, что данная часть текста, являеться модельным номером оборудование и не всегда находиться вторым словом, в надписи. Бывает третьим словом, четвертым, пятым и т.д. Я не знаю как выследить закономерность чтоб составить правильную формулу, одну для всех позиций, тем более мне сложнее попросить помочь с макросом.

Например:
Монитор 17' Acer V176LBMD (UM.BV6EE.005)
Asus ZenFone Go (ZB452KG) DS Black
Монитор LCD Asus 18.5' VS197DE D-Sub (90LMF1001T02201C-)
Моноблок ASUS Z240ICGK-GC029X (90PT01E1-M01190)
Неттоп ASUS UN65H-M107M (90MS00S1-M01070)
ПК ASUS UN62-M003M (90MS00A1-M00030)

Жирным шрифтом, я выделил значения которые, каждый раз при помощи формул:
2 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]2[/b]-1)*250+1;250))

3 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]3[/b]-1)*250+1;250))

4 слово

Код

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(RC[-8];" ";ПОВТОР(" ";250));([b]4[/b]-1)*250+1;250))

Автор - wwizard
Дата добавления - 26.12.2016 в 00:51

Дата: Понедельник, 26.12.2016, 02:35 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Наверное опять замечание будет! Формулы надо заключать в теги.

Код работает правильно если только номер вне скобок и если без пробела.
Чем богат. Совершенствуйте.
[vba]

Код

Sub tekst()
  Dim St$, ps&, i&, ns&, n&
  ps = Range("A" & Rows.Count).End(xlUp).Row
  For i = 2 To ps
    St = Cells(i, 1)
    ns = InStr(St, "(")
    If ns > 1 Then
       St = Mid(St, 1, ns - 2)
       n = InStrRev(St, " ")
       Cells(i, 9) = Mid(St, n)
    End If
  Next
End Sub

[/vba]

Сообщение отредактировал Wasilich - Понедельник, 26.12.2016, 02:37

Дата: Понедельник, 26.12.2016, 03:03 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 176

Репутация: 0 ±

Замечаний: 40% ±

Цитата Wasilich, 26.12.2016 в 02:35, в сообщении № 2 ()

Код работает правильно если только номер вне скобок и если без пробела.

Если не трудно, можете пояснить алгоритм, как он выбирает в каком месте находиться код товара?

Дата: Понедельник, 26.12.2016, 11:19 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата wwizard, 26.12.2016 в 03:03, в сообщении № 3 ()

как он выбирает в каком месте находиться код

А отладчиком пройтись?
Находим первую скобку "(" в строке и вырезаем текст до конца.
Находим первый пробел справа налево и вырезаем всё до пробела, остается код.
Если код с пробелом, то будет не полный, здесь проблема.
И да, если в строке, где код в скобках, в конце строки нет скобки ")", то код в скобках можно исключить из выбора так
[vba]

Код

If ns > 1 And Right(St, 1) = ")" Then

[/vba]
И желательно
[vba]

Код

St = Cells(i, 1)

[/vba]заменить на
[vba]

Код

St = Trim(Cells(i, 1))

[/vba]

Сообщение отредактировал Wasilich - Понедельник, 26.12.2016, 11:25

Дата: Понедельник, 26.12.2016, 14:00 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 176

Репутация: 0 ±

Замечаний: 40% ±

Не совсем, к сожалению весь прайс не пройти и ручками лишнее не поудалять, получиться та же работа, что я формулу вставляю. Вот что получилось:
http://prntscr.com/dnz2o5

Или может можно добавить проверку, например такую: брать данный код только в том случае, если в строке встречаются следующие слова: Ноутбук Asus, Ноутбук ACER, Проектор Acer, Монитор Asus, Монитор Acer. Для других товаров это не требуеться.
И плюс: исключить список слов, скажем представленных на листе 2 в 4ом столбце. (я же могу туда указать, список Всех слов, которые надо исключать, очищая ячейку)

Сообщение отредактировал wwizard - Понедельник, 26.12.2016, 14:01

Pelena

Дата: Понедельник, 26.12.2016, 14:03 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 19187

Репутация: 4421 ±

Замечаний: ±

Excel 365 & Mac Excel

wwizard, оформите формулы в первом посте тегами (кнопка fx)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Понедельник, 26.12.2016, 14:13 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата wwizard, 26.12.2016 в 14:00, в сообщении № 5 ()

Вот что получилось:
http://prntscr.com/dnz2o5

Вы предлагаете работать с картинкой? Ложите файл.

Дата: Понедельник, 26.12.2016, 14:49 | Сообщение № 8

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 176

Репутация: 0 ±

Замечаний: 40% ±

Файл приложил. Красным в нем пометил, те артикли которые нужны.

Можно ли добавить проверку, например такую: брать данный код только в том случае, если в строке встречаются следующие слова: Ноутбук Asus, Ноутбук ACER, Проектор Acer, Монитор Asus, Монитор Acer. Для других товаров это не требуется.
И плюс: исключить список слов, скажем представленных на листе 2 в 4ом столбце. (я же могу туда указать, список Всех слов, которые надо исключать, очищая ячейку)

К сообщению приложен файл: proba-pera2.xlsm (42.1 Kb)

Дата: Понедельник, 26.12.2016, 16:33 | Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 1232

Репутация: 326 ±

Замечаний: 0% ±

2003

Цитата wwizard, 26.12.2016 в 14:49, в сообщении № 8 ()

если в строке встречаются следующие слова:Ноутбук Asus, Ноутбук ACER, Проектор Acer, Монитор Asus, Монитор Acer

Поместите их на лист 2 в колонку "Е" с 1-й строки.
Будет проверка наличия их в тексте. Можно добавлять/удалять. Дальше не знаю, нет конкретной привязки. Может кто лучше сообразит.

[vba]

Код

Sub tekst()
Dim St$, i&, j&, s&, ns&, n&, X$
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
  St = Trim(Cells(i, "A"))
  With Sheets("Лист2")
    For j = 1 To .Range("E" & Rows.Count).End(xlUp).Row
       ns = InStr(St, .Cells(j, "E"))
       If ns > 0 Then
          St = Replace(St, "Black ", "")
          ns = InStr(St, "(")
          If ns > 1 Then
             St = Mid(St, 1, ns - 2)
             n = InStrRev(St, " ")
             Cells(i, 9) = Mid(St, n)
          End If
          Exit For
       End If
    Next
  End With
Next
End Sub

[/vba]

Дата: Понедельник, 26.12.2016, 18:49 | Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 176

Репутация: 0 ±

Замечаний: 40% ±

Цитата Wasilich, 26.12.2016 в 16:33, в сообщении № 9 ()

Будет проверка наличия их в тексте. Можно добавлять/удалять. Дальше не знаю, нет конкретной привязки. Может кто лучше сообразит.

Супер работает. Осталось только прописать условие - Удалить из результата в листе 1 в колонке 9, если найдено сопоставления с Лист2 колонка 4.
Чтоб такие вещи удалять: http://prntscr.com/do33l3