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

Вход

Регистрация

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

 

= Мир MS Excel/Удалить лишние данные из ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Удалить лишние данные из ячейки (Формулы/Formulas)
Удалить лишние данные из ячейки
uqrop Дата: Вторник, 17.02.2015, 13:55 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день
есть таблица с ячейками вида: 18973 от 19,07,14, Телефон Nokia 108 DS Black, 357270059638989
из данной ячейки нужны только цифры 357270059638989, цифры абсолютно разные единственно что их объединяет это кол-во их всегда 15.
Каким то образом можно все остальные ненужны данные, оставить только 15 цифр
Заранее спасибо
 
Ответить
СообщениеДобрый день
есть таблица с ячейками вида: 18973 от 19,07,14, Телефон Nokia 108 DS Black, 357270059638989
из данной ячейки нужны только цифры 357270059638989, цифры абсолютно разные единственно что их объединяет это кол-во их всегда 15.
Каким то образом можно все остальные ненужны данные, оставить только 15 цифр
Заранее спасибо
Автор - uqrop
Дата добавления - 17.02.2015 в 13:55
VEKTORVSFREEMAN Дата: Вторник, 17.02.2015, 14:00 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
uqrop, здравствуйте!
Используйте функцию =ПРАВСИМВ, примерно так
Код
=ПРАВСИМВ(A1;15)

точнее только с вашим файлом. Правила форума.

"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
Сообщениеuqrop, здравствуйте!
Используйте функцию =ПРАВСИМВ, примерно так
Код
=ПРАВСИМВ(A1;15)

точнее только с вашим файлом. Правила форума.
Автор - VEKTORVSFREEMAN
Дата добавления - 17.02.2015 в 14:00
uqrop Дата: Вторник, 17.02.2015, 14:13 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
VEKTORVSFREEMAN, спасибо
забыл уточнить, что данные расположены в произвольном порядке т.е.
может быть
8973 от 19,07,14, Телефон Nokia 108 DS Black, 357270059638989
8973 от 19,07,14, 357270059638989, Телефон Nokia 108 DS Black,
357270059638989, 8973 от 19,07,14, Телефон Nokia 108 DS Black
и т.д.
пример вложил
К сообщению приложен файл: 8107661.xls (24.5 Kb)
 
Ответить
СообщениеVEKTORVSFREEMAN, спасибо
забыл уточнить, что данные расположены в произвольном порядке т.е.
может быть
8973 от 19,07,14, Телефон Nokia 108 DS Black, 357270059638989
8973 от 19,07,14, 357270059638989, Телефон Nokia 108 DS Black,
357270059638989, 8973 от 19,07,14, Телефон Nokia 108 DS Black
и т.д.
пример вложил
Автор - uqrop
Дата добавления - 17.02.2015 в 14:13
Pelena Дата: Вторник, 17.02.2015, 14:40 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
uqrop, посмотрите похожую тему

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеuqrop, посмотрите похожую тему
Автор - Pelena
Дата добавления - 17.02.2015 в 14:40
_Boroda_ Дата: Вторник, 17.02.2015, 14:49 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16895
Репутация: 6613 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Обычная формула
Код
=ПРОСМОТР(;-1/(-ПСТР(" "&A1;СТРОКА($1:$999);16)=-ПСТР(A1&"z";СТРОКА($1:$999);15));ПСТР(A1;СТРОКА($1:$999);15))
К сообщению приложен файл: 8107661_1.xls (29.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеОбычная формула
Код
=ПРОСМОТР(;-1/(-ПСТР(" "&A1;СТРОКА($1:$999);16)=-ПСТР(A1&"z";СТРОКА($1:$999);15));ПСТР(A1;СТРОКА($1:$999);15))
Автор - _Boroda_
Дата добавления - 17.02.2015 в 14:49
uqrop Дата: Вторник, 17.02.2015, 15:11 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_,
большое спасибо, отлично работает
 
Ответить
Сообщение_Boroda_,
большое спасибо, отлично работает
Автор - uqrop
Дата добавления - 17.02.2015 в 15:11
vikttur Дата: Вторник, 17.02.2015, 17:23 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Для разнообразия, тоже массивная:
Код
=МАКС(ЕСЛИ(ЕЧИСЛО(--ПСТР(A1;СТРОКА($1:$200);15));--ПСТР(A1;СТРОКА($1:$200);15)))&""


Сообщение отредактировал vikttur - Вторник, 17.02.2015, 23:47
 
Ответить
СообщениеДля разнообразия, тоже массивная:
Код
=МАКС(ЕСЛИ(ЕЧИСЛО(--ПСТР(A1;СТРОКА($1:$200);15));--ПСТР(A1;СТРОКА($1:$200);15)))&""
Автор - vikttur
Дата добавления - 17.02.2015 в 17:23
vikttur Дата: Вторник, 17.02.2015, 23:45 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

То же, для более поздних версий:
Код
=МАКС(ЕСЛИОШИБКА(--ПСТР(A1;СТРОКА($1:$200);15);))&""
 
Ответить
СообщениеТо же, для более поздних версий:
Код
=МАКС(ЕСЛИОШИБКА(--ПСТР(A1;СТРОКА($1:$200);15);))&""
Автор - vikttur
Дата добавления - 17.02.2015 в 23:45
VEKTORVSFREEMAN Дата: Среда, 18.02.2015, 07:28 | Сообщение № 9
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
Уважаемые Господа и гуру Excel'я!
Хоть ответ уже ТС получил, тем не менее мне очень хочется решить сею задачу самостоятельно. Пытаюсь решить её данной формулой
Код
=ПОИСК("/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(" "&A1&" ");" ";"/");1)

Но часть формулы (искомый текст) "/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/" не перебирает всех значений, а останавливается на первом из десяти "/0??????????????/".
Как можно заставить и можно ли, перебрать все десять значений
и найти единственно верное для поиска в тексте ячейки?

[p.s.]Немного поясню, этой формулой я пытаюсь найти порядковое число первого символа птнадцатизначного числа в тексте ячейки, и далее применить функцию =ПСТР(А1;"эта формула";15)[/p.s.]
К сообщению приложен файл: 4604006.xls (25.5 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"


Сообщение отредактировал VEKTORVSFREEMAN - Среда, 18.02.2015, 09:22
 
Ответить
СообщениеУважаемые Господа и гуру Excel'я!
Хоть ответ уже ТС получил, тем не менее мне очень хочется решить сею задачу самостоятельно. Пытаюсь решить её данной формулой
Код
=ПОИСК("/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(" "&A1&" ");" ";"/");1)

Но часть формулы (искомый текст) "/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/" не перебирает всех значений, а останавливается на первом из десяти "/0??????????????/".
Как можно заставить и можно ли, перебрать все десять значений
и найти единственно верное для поиска в тексте ячейки?

[p.s.]Немного поясню, этой формулой я пытаюсь найти порядковое число первого символа птнадцатизначного числа в тексте ячейки, и далее применить функцию =ПСТР(А1;"эта формула";15)[/p.s.]
Автор - VEKTORVSFREEMAN
Дата добавления - 18.02.2015 в 07:28
Pelena Дата: Среда, 18.02.2015, 09:21 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Виктор, если обсуждать именно формулу, то надо из текста убирать запятые, а также пробелы до и после текста прицеплять уже после функции СЖПРОБЕЛЫ, т.е. будет так
Код
ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&СЖПРОБЕЛЫ(A1)&" ";" ";"/");",";"")

после этого функция ПОИСК() вернёт массив из ошибок и числА - номер позиции найденного фрагмента. Чтобы получить это число можно использовать ПРОСМОТР()
Код
=ПРОСМОТР(9^9;ПОИСК("/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/";ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&СЖПРОБЕЛЫ(A1)&" ";" ";"/");",";"");1))

Ну, а дальше уже ПСТР()

Но в этом алгоритме есть большой минус, на мой взгляд. Знак ? заменяет любой символ, а не только цифру, то есть вариант 1абв-2/деж123456 тоже прокатит

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВиктор, если обсуждать именно формулу, то надо из текста убирать запятые, а также пробелы до и после текста прицеплять уже после функции СЖПРОБЕЛЫ, т.е. будет так
Код
ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&СЖПРОБЕЛЫ(A1)&" ";" ";"/");",";"")

после этого функция ПОИСК() вернёт массив из ошибок и числА - номер позиции найденного фрагмента. Чтобы получить это число можно использовать ПРОСМОТР()
Код
=ПРОСМОТР(9^9;ПОИСК("/"&{0:1:2:3:4:5:6:7:8:9}&"??????????????/";ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&СЖПРОБЕЛЫ(A1)&" ";" ";"/");",";"");1))

Ну, а дальше уже ПСТР()

Но в этом алгоритме есть большой минус, на мой взгляд. Знак ? заменяет любой символ, а не только цифру, то есть вариант 1абв-2/деж123456 тоже прокатит
Автор - Pelena
Дата добавления - 18.02.2015 в 09:21
vikttur Дата: Среда, 18.02.2015, 09:25 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Формула перебирает массив констант и создает массив значений
={#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:15:#ЗНАЧ!}
Это можно увидеть, выдели в формулу и нажав F9.
Но заменить разом все 10 цифр никак не получится. Только если число равняется заменяемому.


Сообщение отредактировал vikttur - Среда, 18.02.2015, 09:26
 
Ответить
СообщениеФормула перебирает массив констант и создает массив значений
={#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:15:#ЗНАЧ!}
Это можно увидеть, выдели в формулу и нажав F9.
Но заменить разом все 10 цифр никак не получится. Только если число равняется заменяемому.
Автор - vikttur
Дата добавления - 18.02.2015 в 09:25
VEKTORVSFREEMAN Дата: Среда, 18.02.2015, 09:37 | Сообщение № 12
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
Елена, огромное спасибо за подсказку! Да, вижу огромный минус сей формулы (думал, что возможны сбои, но редко. Ан нет получается, что она совсем некорректна как минимум с приведённым в этом топике примером, правильный результат получился только во второй строке)
И отдельное спасибо за науку, теперь мне понятен и известен приём через функцию =ПРОСМОТР.

Виктор, спасибо за пояснения :) Ранее как то узнал про клавишу F9 и понял, что это могучий ключ к пониманию сложных формул.
Этот алгоритм ={#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:15:#ЗНАЧ!} я и пытался узнать (т.е. как его добиться)

"Опыт - это то, что получаешь, не получив того, что хотел"


Сообщение отредактировал VEKTORVSFREEMAN - Среда, 18.02.2015, 09:40
 
Ответить
СообщениеЕлена, огромное спасибо за подсказку! Да, вижу огромный минус сей формулы (думал, что возможны сбои, но редко. Ан нет получается, что она совсем некорректна как минимум с приведённым в этом топике примером, правильный результат получился только во второй строке)
И отдельное спасибо за науку, теперь мне понятен и известен приём через функцию =ПРОСМОТР.

Виктор, спасибо за пояснения :) Ранее как то узнал про клавишу F9 и понял, что это могучий ключ к пониманию сложных формул.
Этот алгоритм ={#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:15:#ЗНАЧ!} я и пытался узнать (т.е. как его добиться)
Автор - VEKTORVSFREEMAN
Дата добавления - 18.02.2015 в 09:37
Pelena Дата: Среда, 18.02.2015, 09:40 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
Да нет, как раз с этим примером работает
К сообщению приложен файл: 1111238.xls (26.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДа нет, как раз с этим примером работает
Автор - Pelena
Дата добавления - 18.02.2015 в 09:40
VEKTORVSFREEMAN Дата: Среда, 18.02.2015, 09:45 | Сообщение № 14
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
Елена, мне еще учится и учится :(
вот где моя ошибка была (в файле)
Сейчас сяду и буду разбираться ))
Спасибо!

Ошибку понял. А почему необходимо помимо сжатия пробелов еще и запятые убирать?
К сообщению приложен файл: 6060384.xls (28.0 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"


Сообщение отредактировал VEKTORVSFREEMAN - Среда, 18.02.2015, 09:54
 
Ответить
СообщениеЕлена, мне еще учится и учится :(
вот где моя ошибка была (в файле)
Сейчас сяду и буду разбираться ))
Спасибо!

Ошибку понял. А почему необходимо помимо сжатия пробелов еще и запятые убирать?
Автор - VEKTORVSFREEMAN
Дата добавления - 18.02.2015 в 09:45
Pelena Дата: Среда, 18.02.2015, 09:59 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 19517
Репутация: 4632 ±
Замечаний: ±

Excel 365 & Mac Excel
потому что это 16-й символ, прилепленный к 15 цифрам :)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениепотому что это 16-й символ, прилепленный к 15 цифрам :)
Автор - Pelena
Дата добавления - 18.02.2015 в 09:59
VEKTORVSFREEMAN Дата: Среда, 18.02.2015, 10:05 | Сообщение № 16
Группа: Друзья
Ранг: Ветеран
Сообщений: 772
Репутация: 130 ±
Замечаний: 0% ±

MS Office Excel 2010
Елена, спасибо! :) Стало всё понятно yes

"Опыт - это то, что получаешь, не получив того, что хотел"
 
Ответить
СообщениеЕлена, спасибо! :) Стало всё понятно yes
Автор - VEKTORVSFREEMAN
Дата добавления - 18.02.2015 в 10:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Удалить лишние данные из ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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