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

Вход

Регистрация

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

 

= Мир MS Excel/Разделить буквы и цифры в ячейки - Мир MS Excel

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

Добрый день

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

То есть есть:

артикул а должно быть так, как удалить буквы, если кол-во цифр в ячейки может быть разным
777GT 777
8989897TY 898989

Сейчас пользуюсь фильтром и текст по столбцам удаляю по одному символу (букве), но это долго и неудобно. Нужна формула
К сообщению приложен файл: 2593914.xlsx (9.4 Kb)
 
Ответить
СообщениеДобрый день

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

То есть есть:

артикул а должно быть так, как удалить буквы, если кол-во цифр в ячейки может быть разным
777GT 777
8989897TY 898989

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

Автор - Keles
Дата добавления - 01.02.2016 в 16:29
Pelena Дата: Понедельник, 01.02.2016, 16:48 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19165
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Так, например
Код
=ПРОСМОТР(9^9;--ПСТР(A2;1;СТРОКА($1:$9)))
К сообщению приложен файл: 2330492.xlsx (9.8 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Так, например
Код
=ПРОСМОТР(9^9;--ПСТР(A2;1;СТРОКА($1:$9)))

Автор - Pelena
Дата добавления - 01.02.2016 в 16:48
YouGreed Дата: Понедельник, 01.02.2016, 16:55 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 589
Репутация: 123 ±
Замечаний: 0% ±

Excel 2010
Keles, Можно еще так.
Код
=ЛЕВБ($A2;МАКС(ЕСЛИ(ЕЧИСЛО(-ПСТР(A2;СТРОКА($1:$99);1));СТРОКА($1:$99))))
 
Ответить
СообщениеKeles, Можно еще так.
Код
=ЛЕВБ($A2;МАКС(ЕСЛИ(ЕЧИСЛО(-ПСТР(A2;СТРОКА($1:$99);1));СТРОКА($1:$99))))

Автор - YouGreed
Дата добавления - 01.02.2016 в 16:55
_Boroda_ Дата: Понедельник, 01.02.2016, 16:56 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант. Правда, формула массива - вводится одновременным нажатием Контрл шифт Ентер
Код
=ЛЕВБ(A2;СЧЁТ(-ЛЕВБ(A2;СТРОКА($1:$9))))

И чуть поиграюсь с формулой Лены
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))
К сообщению приложен файл: 2593914_1.xlsx (11.1 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант. Правда, формула массива - вводится одновременным нажатием Контрл шифт Ентер
Код
=ЛЕВБ(A2;СЧЁТ(-ЛЕВБ(A2;СТРОКА($1:$9))))

И чуть поиграюсь с формулой Лены
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))

Автор - _Boroda_
Дата добавления - 01.02.2016 в 16:56
DExplorer Дата: Среда, 01.01.2020, 21:55 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте, всех с прошедшими и наступающими!

Нашёл здесь решение своего вопроса, но не могу разобрать, как именно работает формула
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))

Я так понимаю, что запись
Код
=-ПСТР(A2;1;СТРОКА($1:$9))
превращает запись в ячейке в массив элементов, которые можно увидеть, если нажать F9. Но как тогда ПРОСМОТР вытаскивает оттуда макс значение? И при этом он работает только когда перед ПСТР есть знак "-".
Получается, что в формуле =-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9))) ПРОСМОТР ищет пустое значение в указанном массиве элементов, и не найдя его, возвращает наименьшее (которое меньше искомого)в этом массиве (для этого знак "-" перед ПСТР), т.е. получается всю числовую часть ячейки. И затем знак "минус" перед самим ПРОСМОТРом схлопывается с минусом ПСТР, и получается искомый результат.
Тогда понятен "-" перед ПСТР: поскольку ПРОСМОТР, когда не находит искомое (здесь - пустое ) значение, возвращает наименьшее в указанном массиве, нужно перед ПСТР добавить минус, т.к. без него наименьшего значения в массиве получается что нет, и формула =-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9))) выдаёт Н/Д. Т.е. пустое значение для Эксель - это то же самое, что и ноль.

Это так? мне кажется в моём рассуждении где-то кроются ошибки.
 
Ответить
СообщениеЗдравствуйте, всех с прошедшими и наступающими!

Нашёл здесь решение своего вопроса, но не могу разобрать, как именно работает формула
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))

Я так понимаю, что запись
Код
=-ПСТР(A2;1;СТРОКА($1:$9))
превращает запись в ячейке в массив элементов, которые можно увидеть, если нажать F9. Но как тогда ПРОСМОТР вытаскивает оттуда макс значение? И при этом он работает только когда перед ПСТР есть знак "-".
Получается, что в формуле =-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9))) ПРОСМОТР ищет пустое значение в указанном массиве элементов, и не найдя его, возвращает наименьшее (которое меньше искомого)в этом массиве (для этого знак "-" перед ПСТР), т.е. получается всю числовую часть ячейки. И затем знак "минус" перед самим ПРОСМОТРом схлопывается с минусом ПСТР, и получается искомый результат.
Тогда понятен "-" перед ПСТР: поскольку ПРОСМОТР, когда не находит искомое (здесь - пустое ) значение, возвращает наименьшее в указанном массиве, нужно перед ПСТР добавить минус, т.к. без него наименьшего значения в массиве получается что нет, и формула =-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9))) выдаёт Н/Д. Т.е. пустое значение для Эксель - это то же самое, что и ноль.

Это так? мне кажется в моём рассуждении где-то кроются ошибки.

Автор - DExplorer
Дата добавления - 01.01.2020 в 21:55
RAN Дата: Четверг, 02.01.2020, 20:35 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
В кои то веки заглянул в эту ветку.
Возник вопрос: 0<>-0? Или, почему формула
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))

пропускает 0?


Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеВ кои то веки заглянул в эту ветку.
Возник вопрос: 0<>-0? Или, почему формула
Код
=-ПРОСМОТР(;-ПСТР(A2;1;СТРОКА($1:$9)))

пропускает 0?

Автор - RAN
Дата добавления - 02.01.2020 в 20:35
bmv98rus Дата: Четверг, 02.01.2020, 21:37 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
RAN, Андрей, она не 0 пропускает, а в случае неупорядоченных значений дойдет до последнего удовлетворяющего значению. ощем 0 среди отрицательных или ошибок из 9 последнее отрицательное меньше 0 , его и найдем, после инверсируем.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеRAN, Андрей, она не 0 пропускает, а в случае неупорядоченных значений дойдет до последнего удовлетворяющего значению. ощем 0 среди отрицательных или ошибок из 9 последнее отрицательное меньше 0 , его и найдем, после инверсируем.

Автор - bmv98rus
Дата добавления - 02.01.2020 в 21:37
RAN Дата: Четверг, 02.01.2020, 22:15 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Миш, как не перечитывал справку, не обнаружил
случае неупорядоченных значений дойдет до последнего удовлетворяющего значению

Обидно-с, что такой существенной "мелочи" там нет.


Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМиш, как не перечитывал справку, не обнаружил
случае неупорядоченных значений дойдет до последнего удовлетворяющего значению

Обидно-с, что такой существенной "мелочи" там нет.

Автор - RAN
Дата добавления - 02.01.2020 в 22:15
bmv98rus Дата: Четверг, 02.01.2020, 22:37 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
ну в данном случае используется
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
правда не очень очевидно что используется как раз эта ошибка.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениену в данном случае используется
If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
правда не очень очевидно что используется как раз эта ошибка.

Автор - bmv98rus
Дата добавления - 02.01.2020 в 22:37
RAN Дата: Четверг, 02.01.2020, 22:45 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Во встроенной справке написано лучше.
Цитата
Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Читал. Но 0 присутствует, и расположен раньше буквы
50J >> 50
И, как я понимаю, 0>-J


Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Четверг, 02.01.2020, 22:50
 
Ответить
СообщениеВо встроенной справке написано лучше.
Цитата
Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Читал. Но 0 присутствует, и расположен раньше буквы
50J >> 50
И, как я понимаю, 0>-J

Автор - RAN
Дата добавления - 02.01.2020 в 22:45
bmv98rus Дата: Четверг, 02.01.2020, 22:54 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Но 0 присутствует

даже если он есть, хотя его нет ибо массив будет {-8;-89;-898;-8989;-89890;-898909;-8989097;#VALUE!;#VALUE!}, то из-за отсутствия сортировки будет пропущен в пользу последнего без ошибки


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Но 0 присутствует

даже если он есть, хотя его нет ибо массив будет {-8;-89;-898;-8989;-89890;-898909;-8989097;#VALUE!;#VALUE!}, то из-за отсутствия сортировки будет пропущен в пользу последнего без ошибки

Автор - bmv98rus
Дата добавления - 02.01.2020 в 22:54
RAN Дата: Четверг, 02.01.2020, 23:09 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Ура! Удалось добраться до
[vba]
Код
=-ПРОСМОТР(;{-5:-50:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!})
[/vba]
До этого утыкался (в т.ч. в пошаговом выполнении) на
Код
=-ПРОСМОТР(;-{"5":"50":"50J":"50J":"50J":"50J":"50J":"50J":"50J"})


Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Четверг, 02.01.2020, 23:12
 
Ответить
СообщениеУра! Удалось добраться до
[vba]
Код
=-ПРОСМОТР(;{-5:-50:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!})
[/vba]
До этого утыкался (в т.ч. в пошаговом выполнении) на
Код
=-ПРОСМОТР(;-{"5":"50":"50J":"50J":"50J":"50J":"50J":"50J":"50J"})

Автор - RAN
Дата добавления - 02.01.2020 в 23:09
DExplorer Дата: Понедельник, 06.01.2020, 13:45 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
СПасибо, что не оставляете нас один на один с непоятными формулами, но...
я так и не понял её логики.

=-ПСТР(A2;1;СТРОКА($1:$9)) - эта часть понятна, превращает строку в ячейке в набор элементов - {-3:-34:-341:-3416:-34165:-341651:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}

Вопрос в том, как ПРОСМОТР вытаскивает наименьший элемент из этого массива? По справке, как указал Михаил, If the LOOKUP function can't find the lookup_value, the function matches the LARGEST value in lookup_vector that is less than or equal to lookup_value. Наше lookup_value = 0, и ПРОСМОТР (судя по справке) должен вроде как вытаскивать -3, а не -341651. Что за..?
И вот эта часть ещё непонятна
Цитата
Андрей, она не 0 пропускает, а в случае неупорядоченных значений дойдет до последнего удовлетворяющего значению

Почему неупорядоченные значения? От большего к меньшему {-3:-34:-341:-3416:-34165:-341651:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}
 
Ответить
СообщениеСПасибо, что не оставляете нас один на один с непоятными формулами, но...
я так и не понял её логики.

=-ПСТР(A2;1;СТРОКА($1:$9)) - эта часть понятна, превращает строку в ячейке в набор элементов - {-3:-34:-341:-3416:-34165:-341651:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}

Вопрос в том, как ПРОСМОТР вытаскивает наименьший элемент из этого массива? По справке, как указал Михаил, If the LOOKUP function can't find the lookup_value, the function matches the LARGEST value in lookup_vector that is less than or equal to lookup_value. Наше lookup_value = 0, и ПРОСМОТР (судя по справке) должен вроде как вытаскивать -3, а не -341651. Что за..?
И вот эта часть ещё непонятна
Цитата
Андрей, она не 0 пропускает, а в случае неупорядоченных значений дойдет до последнего удовлетворяющего значению

Почему неупорядоченные значения? От большего к меньшему {-3:-34:-341:-3416:-34165:-341651:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}

Автор - DExplorer
Дата добавления - 06.01.2020 в 13:45
bmv98rus Дата: Понедельник, 06.01.2020, 14:58 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Почему неупорядоченные значения? От большего к меньшему
по тому что есть еще одна строка
Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value И это говорит что обязательно от меньшего к большему.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Почему неупорядоченные значения? От большего к меньшему
по тому что есть еще одна строка
Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value И это говорит что обязательно от меньшего к большему.

Автор - bmv98rus
Дата добавления - 06.01.2020 в 14:58
DExplorer Дата: Вторник, 07.01.2020, 16:22 | Сообщение № 15
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, спасибо за наводку. Теперь вроде немного прояснилось.

Если значения в просматриваемом массиве - в нисходящем порядке, то ПРОСМОТР вытаскивает не наибольшее значение, а наоборот, наименьшее.
Ха, но тогда создатели справки не совсем корректно сделали описание функции ПРОСМОТР -
Цитата
The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.

правильнее наверно в таком случае будет так - "The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP matches the SMALLEST value in lookup_vector.".

В этой формуле сознательно (!) допускается ошибка в синтаксе ПРОСМОТР, чтобы получить необходимый результат. Обожаю такую небанальную красоту.


Сообщение отредактировал DExplorer - Вторник, 07.01.2020, 16:30
 
Ответить
Сообщениеbmv98rus, спасибо за наводку. Теперь вроде немного прояснилось.

Если значения в просматриваемом массиве - в нисходящем порядке, то ПРОСМОТР вытаскивает не наибольшее значение, а наоборот, наименьшее.
Ха, но тогда создатели справки не совсем корректно сделали описание функции ПРОСМОТР -
Цитата
The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.

правильнее наверно в таком случае будет так - "The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP matches the SMALLEST value in lookup_vector.".

В этой формуле сознательно (!) допускается ошибка в синтаксе ПРОСМОТР, чтобы получить необходимый результат. Обожаю такую небанальную красоту.

Автор - DExplorer
Дата добавления - 07.01.2020 в 16:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Разделить буквы и цифры в ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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