Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Обрезка текста по словам без учета знаков препинания - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Обрезка текста по словам без учета знаков препинания (Формулы/Formulas)
Обрезка текста по словам без учета знаков препинания
mult Дата: Воскресенье, 10.01.2021, 13:35 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Приветствую ребят, запарился с проблемой, не могу сформировать формулу, мож кто поможет
Есть формула:
Код
=ЛЕВБ(AD2;ПРОСМОТР(81;ПОИСК(" ";AD2&" ";СТРОКА($1:$81)))-1)

Нужно чтобы текст обрезался по словам с знаком препинания до обрезаемого слова и после, обязательное условие: при обрезании текста должно учитываться только количество букв (т. е. количество знаков препинания не должно учитываться во всей строке)

ПРИМЕР:
Исходник 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 1111111111, 90 ОШИБКА!
Проблема 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 69 ОК
Нужный результат 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111 79 ОК ОБРЕЗАНО: (, 1111111111,)
Подсчет количества символов произведен без учета запятых, а обрезка происходи с учетом запятых, а нужно без учета запятых
К сообщению приложен файл: 3849008.xlsx(9.1 Kb)
 
Ответить
СообщениеПриветствую ребят, запарился с проблемой, не могу сформировать формулу, мож кто поможет
Есть формула:
Код
=ЛЕВБ(AD2;ПРОСМОТР(81;ПОИСК(" ";AD2&" ";СТРОКА($1:$81)))-1)

Нужно чтобы текст обрезался по словам с знаком препинания до обрезаемого слова и после, обязательное условие: при обрезании текста должно учитываться только количество букв (т. е. количество знаков препинания не должно учитываться во всей строке)

ПРИМЕР:
Исходник 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 1111111111, 90 ОШИБКА!
Проблема 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 69 ОК
Нужный результат 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111, 111111111 79 ОК ОБРЕЗАНО: (, 1111111111,)
Подсчет количества символов произведен без учета запятых, а обрезка происходи с учетом запятых, а нужно без учета запятых

Автор - mult
Дата добавления - 10.01.2021 в 13:35
bmv98rus Дата: Воскресенье, 10.01.2021, 14:27 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 594 ±
Замечаний: 0% ±

Excel 2013/2016
Код
=SUBSTITUTE(LEFTB(SUBSTITUTE(B1;",";);LOOKUP(81;SEARCH(" ";SUBSTITUTE(B1;",";)&" ";ROW($1:$81)))-1);" ";", ")

или задачу формулируйте более корректно.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Воскресенье, 10.01.2021, 14:28
 
Ответить
Сообщение
Код
=SUBSTITUTE(LEFTB(SUBSTITUTE(B1;",";);LOOKUP(81;SEARCH(" ";SUBSTITUTE(B1;",";)&" ";ROW($1:$81)))-1);" ";", ")

или задачу формулируйте более корректно.

Автор - bmv98rus
Дата добавления - 10.01.2021 в 14:27
mult Дата: Воскресенье, 10.01.2021, 17:03 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Не то получается, по сути удалилась только последняя запятая, это легко можно сделать в моей формуле, а кроме всего, после каждого слова появилась запятая, где ее не было )

Мне требуется, чтобы обрезание происходило исходя из количества символов (букв или цифр), но при этом, в предложении есть знаки препинания, которые не должны учитываться при расчете символов при обрезании по словам
Кроме этого, нужно, чтобы обрезаемое слово (-ва), обрезалось с запятой до слова и после его, т.е. в конце предложения не висела запятая

ОБРЕЗАНО: , 1111111111, в примере указал, что должно быть обрезано от исходника

Алгоритм должен понимать, что 1111111111, тут не 11 символов, а 10, без учета запятой


Сообщение отредактировал mult - Воскресенье, 10.01.2021, 17:52
 
Ответить
СообщениеНе то получается, по сути удалилась только последняя запятая, это легко можно сделать в моей формуле, а кроме всего, после каждого слова появилась запятая, где ее не было )

Мне требуется, чтобы обрезание происходило исходя из количества символов (букв или цифр), но при этом, в предложении есть знаки препинания, которые не должны учитываться при расчете символов при обрезании по словам
Кроме этого, нужно, чтобы обрезаемое слово (-ва), обрезалось с запятой до слова и после его, т.е. в конце предложения не висела запятая

ОБРЕЗАНО: , 1111111111, в примере указал, что должно быть обрезано от исходника

Алгоритм должен понимать, что 1111111111, тут не 11 символов, а 10, без учета запятой

Автор - mult
Дата добавления - 10.01.2021 в 17:03
mult Дата: Воскресенье, 10.01.2021, 17:31 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Приведу еще пример
111111111,,,,,_222222222,,,,,,,,_3333333333, (_ - это я отметил пробел)
Алгоритм должен посчитать выделенное красным, пробелы учитываются как символ
Обрезка должна произойти до 19 символов 111111111_222222222
Итог должен быть 111111111,,,,,_222222222 - т.е. знаки препинания в алгоритме не учитываются
 
Ответить
СообщениеПриведу еще пример
111111111,,,,,_222222222,,,,,,,,_3333333333, (_ - это я отметил пробел)
Алгоритм должен посчитать выделенное красным, пробелы учитываются как символ
Обрезка должна произойти до 19 символов 111111111_222222222
Итог должен быть 111111111,,,,,_222222222 - т.е. знаки препинания в алгоритме не учитываются

Автор - mult
Дата добавления - 10.01.2021 в 17:31
bmv98rus Дата: Воскресенье, 10.01.2021, 18:04 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 594 ±
Замечаний: 0% ±

Excel 2013/2016
или задачу формулируйте более корректно.

это как раз относилось к тому, что нужен нормальный пример и задача.
Что нужно? Знак препинания - это только запятая?


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Воскресенье, 10.01.2021, 18:42
 
Ответить
Сообщение
или задачу формулируйте более корректно.

это как раз относилось к тому, что нужен нормальный пример и задача.
Что нужно? Знак препинания - это только запятая?

Автор - bmv98rus
Дата добавления - 10.01.2021 в 18:04
Gustav Дата: Воскресенье, 10.01.2021, 23:01 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1878
Репутация: 761 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Похоже, что пользовательская функция (UDF) здесь лучший выход, без лишней головной боли:
[vba]
Код
Function NOPREPINAKI(str, maxLen)
    Dim ar, i, sm, smp, smz, smzp, result
    ar = Split(str, " ")
    For i = LBound(ar) To UBound(ar)
        sm = sm + Len(Replace(Replace(Replace(Replace(Replace(Replace(ar(i), _
                      ":", ""), ";", ""), ",", ""), ".", ""), "!", ""), "?", "")) _
                + IIf(i > LBound(ar), 1, 0)
        smz = smz + Len(ar(i)) + IIf(i > LBound(ar), 1, 0)
        If sm > maxLen Then Exit For
        smp = sm: smzp = smz
    Next i
    result = StrReverse(Left(str, smzp))
    For i = 1 To Len(result)
        If InStr(":;,.!?", Mid(result, i, 1)) = 0 Then
            result = StrReverse(Mid(result, i))
            Exit For
        End If
    Next i
    NOPREPINAKI = result
End Function
[/vba]
К сообщению приложен файл: Prepinaki.xlsm(16.4 Kb)


Мой tip box - яд 41001663842605
 
Ответить
СообщениеПохоже, что пользовательская функция (UDF) здесь лучший выход, без лишней головной боли:
[vba]
Код
Function NOPREPINAKI(str, maxLen)
    Dim ar, i, sm, smp, smz, smzp, result
    ar = Split(str, " ")
    For i = LBound(ar) To UBound(ar)
        sm = sm + Len(Replace(Replace(Replace(Replace(Replace(Replace(ar(i), _
                      ":", ""), ";", ""), ",", ""), ".", ""), "!", ""), "?", "")) _
                + IIf(i > LBound(ar), 1, 0)
        smz = smz + Len(ar(i)) + IIf(i > LBound(ar), 1, 0)
        If sm > maxLen Then Exit For
        smp = sm: smzp = smz
    Next i
    result = StrReverse(Left(str, smzp))
    For i = 1 To Len(result)
        If InStr(":;,.!?", Mid(result, i, 1)) = 0 Then
            result = StrReverse(Mid(result, i))
            Exit For
        End If
    Next i
    NOPREPINAKI = result
End Function
[/vba]

Автор - Gustav
Дата добавления - 10.01.2021 в 23:01
mult Дата: Понедельник, 11.01.2021, 00:17 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
или задачу формулируйте более корректно.

это как раз относилось к тому, что нужен нормальный пример и задача.
Что нужно? Знак препинания - это только запятая?

Знак препинания может быть любым, но меня интересует запятая

Еще как-то подробнее объяснить, что мне нужно?


Сообщение отредактировал mult - Понедельник, 11.01.2021, 00:20
 
Ответить
Сообщениеили задачу формулируйте более корректно.

это как раз относилось к тому, что нужен нормальный пример и задача.
Что нужно? Знак препинания - это только запятая?

Знак препинания может быть любым, но меня интересует запятая

Еще как-то подробнее объяснить, что мне нужно?

Автор - mult
Дата добавления - 11.01.2021 в 00:17
mult Дата: Понедельник, 11.01.2021, 00:24 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

За ранее благодарен, но как ее применить? в приложенном файле
К сообщению приложен файл: 8704916.jpg(14.9 Kb)


Сообщение отредактировал китин - Понедельник, 11.01.2021, 11:11
 
Ответить
СообщениеИзлишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

За ранее благодарен, но как ее применить? в приложенном файле

Автор - mult
Дата добавления - 11.01.2021 в 00:24
Gustav Дата: Понедельник, 11.01.2021, 02:41 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1878
Репутация: 761 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
как ее применить? в приложенном файле

В моем приложенном файле в сообщении № 6 всё же настроено и показано...

Либо вы его сохранили как XLSX и макрос исчез при этом (у меня-то см. - XLSM расширение у файла), либо у вас макросы вообще отключены... Надо включить через Параметры \ Центр управления безопасностью (если возникнут вопросы с включением - погуглите самостоятельно).


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
как ее применить? в приложенном файле

В моем приложенном файле в сообщении № 6 всё же настроено и показано...

Либо вы его сохранили как XLSX и макрос исчез при этом (у меня-то см. - XLSM расширение у файла), либо у вас макросы вообще отключены... Надо включить через Параметры \ Центр управления безопасностью (если возникнут вопросы с включением - погуглите самостоятельно).

Автор - Gustav
Дата добавления - 11.01.2021 в 02:41
mult Дата: Понедельник, 11.01.2021, 11:05 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Огромное спасибо, вроде все работает как надо, макросы не включил сразу


Сообщение отредактировал китин - Понедельник, 11.01.2021, 11:12
 
Ответить
СообщениеИзлишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

Огромное спасибо, вроде все работает как надо, макросы не включил сразу

Автор - mult
Дата добавления - 11.01.2021 в 11:05
bmv98rus Дата: Понедельник, 11.01.2021, 16:05 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3413
Репутация: 594 ±
Замечаний: 0% ±

Excel 2013/2016
Пока массивно вот так
Код
=LEFT(A10;LOOKUP(2;1/IFERROR((1/(1/MMULT(--(ROW($A$1:INDEX(A:A;LEN(A10)+1))>=TRANSPOSE(ROW($A$1:INDEX(A:A;LEN(A10)+1))));--ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:")))*ISNUMBER(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1))+1;1);" .,?!;:"))*ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:"))))<C10;1=0);ROW($A$1:INDEX(A:A;LEN(A10)+1))))


Чуть упростил
Код
=LEFT(A10;LOOKUP(C10;IFERROR(1/(1/
MMULT(--(ROW($A$1:INDEX(A:A;LEN(A10)+1))>=TRANSPOSE(ROW($A$1:INDEX(A:A;LEN(A10)+1))));--ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:")))
*ISNUMBER(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1))+1;1);" .,?!;:"))*ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:"))
);1=0);ROW($A$1:INDEX(A:A;LEN(A10)+1))))
К сообщению приложен файл: example2055.xlsx(9.5 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 11.01.2021, 19:42
 
Ответить
СообщениеПока массивно вот так
Код
=LEFT(A10;LOOKUP(2;1/IFERROR((1/(1/MMULT(--(ROW($A$1:INDEX(A:A;LEN(A10)+1))>=TRANSPOSE(ROW($A$1:INDEX(A:A;LEN(A10)+1))));--ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:")))*ISNUMBER(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1))+1;1);" .,?!;:"))*ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:"))))<C10;1=0);ROW($A$1:INDEX(A:A;LEN(A10)+1))))


Чуть упростил
Код
=LEFT(A10;LOOKUP(C10;IFERROR(1/(1/
MMULT(--(ROW($A$1:INDEX(A:A;LEN(A10)+1))>=TRANSPOSE(ROW($A$1:INDEX(A:A;LEN(A10)+1))));--ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:")))
*ISNUMBER(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1))+1;1);" .,?!;:"))*ISERROR(FIND(MID(A10&"  ";ROW($A$1:INDEX(A:A;LEN(A10)+1));1);" .,?!;:"))
);1=0);ROW($A$1:INDEX(A:A;LEN(A10)+1))))

Автор - bmv98rus
Дата добавления - 11.01.2021 в 16:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Обрезка текста по словам без учета знаков препинания (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!