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

Вход

Регистрация

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

 

= Мир MS Excel/Разнести текст по ячейкам формулой - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Разнести текст по ячейкам формулой (Формулы/Formulas)
Разнести текст по ячейкам формулой
Mikez Дата: Четверг, 19.05.2016, 09:11 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Есть текст разделённый запятыми. Нужно разделить на 3 составляющие:
- текст до первой запятой
- текст после первой и до следующей
- текст после второй запятой до конца

Пробел, который после запятой, не нужен.

Бьюсь уже час - никак не могу победить.
Помогите как это грамотно сделать. Через Данные - Текст по столбцам не подходит. Нужно именно формулами.
Спасибо.
К сообщению приложен файл: 8097398.xlsx(9Kb)


Сообщение отредактировал Mikez - Четверг, 19.05.2016, 09:12
 
Ответить
СообщениеЕсть текст разделённый запятыми. Нужно разделить на 3 составляющие:
- текст до первой запятой
- текст после первой и до следующей
- текст после второй запятой до конца

Пробел, который после запятой, не нужен.

Бьюсь уже час - никак не могу победить.
Помогите как это грамотно сделать. Через Данные - Текст по столбцам не подходит. Нужно именно формулами.
Спасибо.

Автор - Mikez
Дата добавления - 19.05.2016 в 09:11
_Boroda_ Дата: Четверг, 19.05.2016, 09:17 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11349
Репутация: 4679 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999))
К сообщению приложен файл: 8097398_1.xlsx(10Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999))

Автор - _Boroda_
Дата добавления - 19.05.2016 в 09:17
Nic70y Дата: Четверг, 19.05.2016, 09:32 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4085
Репутация: 872 ±
Замечаний: 0% ±

Excel 2013
такой вариант
Код
=ЛЕВБ(A2;ПОИСК(",";A2)-1)
Код
=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;B2&", ";);",";ПОВТОР(" ";999));999))
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;B2&", "&C2;);", ";;1)
К сообщению приложен файл: 5235305.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениетакой вариант
Код
=ЛЕВБ(A2;ПОИСК(",";A2)-1)
Код
=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;B2&", ";);",";ПОВТОР(" ";999));999))
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;B2&", "&C2;);", ";;1)

Автор - Nic70y
Дата добавления - 19.05.2016 в 09:32
Mikez Дата: Четверг, 19.05.2016, 09:42 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Второй вариант от Nic70y прямо то, что нужно. Благодарю!
 
Ответить
СообщениеВторой вариант от Nic70y прямо то, что нужно. Благодарю!

Автор - Mikez
Дата добавления - 19.05.2016 в 09:42
_Boroda_ Дата: Четверг, 19.05.2016, 09:44 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11349
Репутация: 4679 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не понял! А мой первый что, не подходит? Почему? Вы файл мой смотрели?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе понял! А мой первый что, не подходит? Почему? Вы файл мой смотрели?

Автор - _Boroda_
Дата добавления - 19.05.2016 в 09:44
Nic70y Дата: Четверг, 19.05.2016, 09:59 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4085
Репутация: 872 ±
Замечаний: 0% ±

Excel 2013
Не понял
3-й столб :(


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Не понял
3-й столб :(

Автор - Nic70y
Дата добавления - 19.05.2016 в 09:59
_Boroda_ Дата: Четверг, 19.05.2016, 10:21 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11349
Репутация: 4679 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
3-й столб

Точно, прохлопал.
Тогда так (опять одинаковая формула для всех столбцов)
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999^(1+(СТОЛБЕЦ(A2)>2))))
К сообщению приложен файл: 8097398_2.xlsx(10Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
3-й столб

Точно, прохлопал.
Тогда так (опять одинаковая формула для всех столбцов)
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999^(1+(СТОЛБЕЦ(A2)>2))))

Автор - _Boroda_
Дата добавления - 19.05.2016 в 10:21
Mikez Дата: Пятница, 20.05.2016, 03:38 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Немного переделал. Теперь не на 3 ячейки разбирает, а на 4.
Но не могу понять как сделать так, чтобы если есть данные для одной или двух ячеек, то в третьей и четвёртой было пусто.
В примере выделил жёлтым те ячейки, которые должны быть пустые. Покажите, пожалуйста, как получить именно такой результат.
Спасибо.
К сообщению приложен файл: ____4_.xlsx(10Kb)
 
Ответить
СообщениеНемного переделал. Теперь не на 3 ячейки разбирает, а на 4.
Но не могу понять как сделать так, чтобы если есть данные для одной или двух ячеек, то в третьей и четвёртой было пусто.
В примере выделил жёлтым те ячейки, которые должны быть пустые. Покажите, пожалуйста, как получить именно такой результат.
Спасибо.

Автор - Mikez
Дата добавления - 20.05.2016 в 03:38
Michael_S Дата: Пятница, 20.05.2016, 05:01 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1831
Репутация: 343 ±
Замечаний: 0% ±

Excel2016
_Boroda_, А почему 999? что, между запятыми очень много знаков? или много запятых?
Mikez, см. файл
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A2;",";ПОВТОР(" ";ДЛСТР($A2)));(СТОЛБЕЦ(A2)-1)*ДЛСТР($A2)+1;ДЛСТР($A2)))
К сообщению приложен файл: Mikez_4_.xlsx(10Kb)


ЯД: 41001136675053
WM: R389613894253


Сообщение отредактировал Michael_S - Пятница, 20.05.2016, 05:53
 
Ответить
Сообщение_Boroda_, А почему 999? что, между запятыми очень много знаков? или много запятых?
Mikez, см. файл
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A2;",";ПОВТОР(" ";ДЛСТР($A2)));(СТОЛБЕЦ(A2)-1)*ДЛСТР($A2)+1;ДЛСТР($A2)))

Автор - Michael_S
Дата добавления - 20.05.2016 в 05:01
Nic70y Дата: Пятница, 20.05.2016, 07:42 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4085
Репутация: 872 ±
Замечаний: 0% ±

Excel 2013
вариант:
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1)));ЕСЛИ(СТОЛБЕЦ(A1)=4;ДЛСТР($A2);ПОИСК("/";ПОДСТАВИТЬ($A2&",";",";"/";СТОЛБЕЦ(A1)))-ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1))))));"")
ЕСЛИ(СТОЛБЕЦ(A1)=4;ДЛСТР($A2);
или лучше так:
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1)));ЕСЛИ(K2="";ДЛСТР($A2);ПОИСК("/";ПОДСТАВИТЬ($A2&",";",";"/";СТОЛБЕЦ(A1)))-ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1))))));"")
ЕСЛИ(K2="";ДЛСТР($A2);
насколько растянете, на столько и будет (файл 18-45)
К сообщению приложен файл: _4_.xlsx(10Kb) · 18-45.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Пятница, 20.05.2016, 07:51
 
Ответить
Сообщениевариант:
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1)));ЕСЛИ(СТОЛБЕЦ(A1)=4;ДЛСТР($A2);ПОИСК("/";ПОДСТАВИТЬ($A2&",";",";"/";СТОЛБЕЦ(A1)))-ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1))))));"")
ЕСЛИ(СТОЛБЕЦ(A1)=4;ДЛСТР($A2);
или лучше так:
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1)));ЕСЛИ(K2="";ДЛСТР($A2);ПОИСК("/";ПОДСТАВИТЬ($A2&",";",";"/";СТОЛБЕЦ(A1)))-ПОИСК("\";ПОДСТАВИТЬ(","&$A2;",";"\";СТОЛБЕЦ(A1))))));"")
ЕСЛИ(K2="";ДЛСТР($A2);
насколько растянете, на столько и будет (файл 18-45)

Автор - Nic70y
Дата добавления - 20.05.2016 в 07:42
_Boroda_ Дата: Пятница, 20.05.2016, 07:54 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11349
Репутация: 4679 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Немного переделал. Теперь не на 3 ячейки разбирает, а на 4.

Так в формуле тогда просто измените 2 на 3 (я еще степень заменил на умножить, суть от этого не поменялась)
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999*(1+(СТОЛБЕЦ(A2)>3))))

_Boroda_, А почему 999?

Прежде всего потому, что не нужно грузить компьютер, 3 раза считая ДЛСТР. Это на одной формуле ничего страшного, а если их много? Не нравится 999, можно написать 99 или 237. Только 238 не пишите :D
К сообщению приложен файл: _4_1.xlsx(11Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Немного переделал. Теперь не на 3 ячейки разбирает, а на 4.

Так в формуле тогда просто измените 2 на 3 (я еще степень заменил на умножить, суть от этого не поменялась)
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";999));СТОЛБЕЦ(A2)*999;999*(1+(СТОЛБЕЦ(A2)>3))))

_Boroda_, А почему 999?

Прежде всего потому, что не нужно грузить компьютер, 3 раза считая ДЛСТР. Это на одной формуле ничего страшного, а если их много? Не нравится 999, можно написать 99 или 237. Только 238 не пишите :D

Автор - _Boroda_
Дата добавления - 20.05.2016 в 07:54
Nic70y Дата: Пятница, 20.05.2016, 08:21 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4085
Репутация: 872 ±
Замечаний: 0% ±

Excel 2013
Еще
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(", "&$A2;",";"\";СТОЛБЕЦ(A1)));999);",";ПОВТОР(" ";(K2<>"")*998+1));999));"")
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(", "&$A2;",";"\";СТОЛБЕЦ(A1)));999);",";ЕСЛИ(K2="";",";ПОВТОР(" ";999)));999));"")
К сообщению приложен файл: 18-48.xlsx(10Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Пятница, 20.05.2016, 08:25
 
Ответить
СообщениеЕще
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(", "&$A2;",";"\";СТОЛБЕЦ(A1)));999);",";ПОВТОР(" ";(K2<>"")*998+1));999));"")
Код
=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР($A2;ПОИСК("\";ПОДСТАВИТЬ(", "&$A2;",";"\";СТОЛБЕЦ(A1)));999);",";ЕСЛИ(K2="";",";ПОВТОР(" ";999)));999));"")

Автор - Nic70y
Дата добавления - 20.05.2016 в 08:21
Michael_S Дата: Пятница, 20.05.2016, 12:08 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1831
Репутация: 343 ±
Замечаний: 0% ±

Excel2016
Прежде всего потому, что не нужно грузить компьютер, 3 раза считая ДЛСТР.
ДЛСТР -одна из самых легких функций.


ЯД: 41001136675053
WM: R389613894253
 
Ответить
Сообщение
Прежде всего потому, что не нужно грузить компьютер, 3 раза считая ДЛСТР.
ДЛСТР -одна из самых легких функций.

Автор - Michael_S
Дата добавления - 20.05.2016 в 12:08
_Boroda_ Дата: Пятница, 20.05.2016, 12:22 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11349
Репутация: 4679 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
И что, будем теперь всовывать ее везде?
Собственно, я ответил на вопрос про 999, если не нравится 999, никто не мешает использовать любой другой вариант - у нас демократия


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИ что, будем теперь всовывать ее везде?
Собственно, я ответил на вопрос про 999, если не нравится 999, никто не мешает использовать любой другой вариант - у нас демократия

Автор - _Boroda_
Дата добавления - 20.05.2016 в 12:22
Mikez Дата: Пятница, 20.05.2016, 16:41 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 19
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Благодарю всех за помощь! Работает на ура!
 
Ответить
СообщениеБлагодарю всех за помощь! Работает на ура!

Автор - Mikez
Дата добавления - 20.05.2016 в 16:41
Michael_S Дата: Пятница, 20.05.2016, 16:47 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1831
Репутация: 343 ±
Замечаний: 0% ±

Excel2016
Собственно, я ответил на вопрос про 999
Не, просто я обычно использую 100 в таких случаях, а 999 кажется много, пугает.
А ДЛСТР в данном случае - "проба пера".


ЯД: 41001136675053
WM: R389613894253
 
Ответить
Сообщение
Собственно, я ответил на вопрос про 999
Не, просто я обычно использую 100 в таких случаях, а 999 кажется много, пугает.
А ДЛСТР в данном случае - "проба пера".

Автор - Michael_S
Дата добавления - 20.05.2016 в 16:47
Nic70y Дата: Пятница, 20.05.2016, 17:21 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4085
Репутация: 872 ±
Замечаний: 0% ±

Excel 2013
[offtop]
пугает.
не, конечно 237 мне больше нравится, но 999 красивее.[/offtop]


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение[offtop]
пугает.
не, конечно 237 мне больше нравится, но 999 красивее.[/offtop]

Автор - Nic70y
Дата добавления - 20.05.2016 в 17:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Разнести текст по ячейкам формулой (Формулы/Formulas)
Страница 1 из 11
Поиск:

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