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

Вход

Регистрация

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

 

= Мир MS Excel/Разделение текста и цифр - Мир MS Excel

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

Excel 2010
Приветствую знатоки и любители Экселя!

Помогите мне пожалуйста разобраться. У меня есть смешанная ячейка (А) в ней и текст и цифры, как мне разделить с помощью дополнительных 2 столбцов отдельно текстовую часть от цифровой.

Пытаюсь сделать с помощью формул
Код
ЛЕВСИМВ[code] и
ПРАВСИМВ[/code],

но цифр где-то 9, где-то 11, где-то 12, да и текст везде разный в плане количества символов.

Заранее большое СПАСИБО.
К сообщению приложен файл: _Microsoft_Exce.xlsx(8.4 Kb)


Сообщение отредактировал ПАМ - Среда, 22.08.2018, 16:26
 
Ответить
СообщениеПриветствую знатоки и любители Экселя!

Помогите мне пожалуйста разобраться. У меня есть смешанная ячейка (А) в ней и текст и цифры, как мне разделить с помощью дополнительных 2 столбцов отдельно текстовую часть от цифровой.

Пытаюсь сделать с помощью формул
Код
ЛЕВСИМВ[code] и
ПРАВСИМВ[/code],

но цифр где-то 9, где-то 11, где-то 12, да и текст везде разный в плане количества символов.

Заранее большое СПАСИБО.

Автор - ПАМ
Дата добавления - 22.08.2018 в 16:23
_Boroda_ Дата: Среда, 22.08.2018, 16:26 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12984
Репутация: 5337 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
По запятой
Код
=ЛЕВБ(A1;ПОИСК(",";A1)-1)

Код
=ПСТР(A1;ДЛСТР(D1)+2;99)
или
Код
=ПОДСТАВИТЬ(A1;D1&", ";)
К сообщению приложен файл: 7775440.xlsx(8.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
По запятой
Код
=ЛЕВБ(A1;ПОИСК(",";A1)-1)

Код
=ПСТР(A1;ДЛСТР(D1)+2;99)
или
Код
=ПОДСТАВИТЬ(A1;D1&", ";)

Автор - _Boroda_
Дата добавления - 22.08.2018 в 16:26
ПАМ Дата: Среда, 22.08.2018, 16:32 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 142
Репутация: 11 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Александр. Спасибо. С 1 похоже подходит, а вот со вторым немножко нет (или я не понимаю) мне нужно чтобы в ячейке C была формула зависимая от А без добавления еще столбцов
 
Ответить
Сообщение_Boroda_, Александр. Спасибо. С 1 похоже подходит, а вот со вторым немножко нет (или я не понимаю) мне нужно чтобы в ячейке C была формула зависимая от А без добавления еще столбцов

Автор - ПАМ
Дата добавления - 22.08.2018 в 16:32
_Boroda_ Дата: Среда, 22.08.2018, 16:39 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12984
Репутация: 5337 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ну Вы же все равно в столбце В выводите текст, правильно? Почему бы его не использовать?
У меня в формуле не В, а D, но это только потому, что я в Вашем файле сделал формулы в D и Е. Посмотрите приложенный файл и все станет понятно


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

Автор - _Boroda_
Дата добавления - 22.08.2018 в 16:39
ПАМ Дата: Среда, 22.08.2018, 16:47 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 142
Репутация: 11 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Александр прошу прощения, я действительно не внимательно посмотрел. Кажется теперь все ясно. Огромное Вам спасибо.
 
Ответить
Сообщение_Boroda_, Александр прошу прощения, я действительно не внимательно посмотрел. Кажется теперь все ясно. Огромное Вам спасибо.

Автор - ПАМ
Дата добавления - 22.08.2018 в 16:47
sv2014 Дата: Среда, 22.08.2018, 18:42 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 219
Репутация: 60 ±
Замечаний: 0% ±

Excel 2013
ПАМ, добрый день,в завершение вариант UDF в столбцах B C или UDF ccc ddd

[vba]
Код
Function aaa$(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "\d+": aaa = .Execute(t)(0)
End With
End Function
[/vba]

[vba]
Код
Function bbb$(t$)
With CreateObject("VBScript.RegExp")
.Pattern = "[ а-яё-]+": bbb = .Execute(t)(0)
End With
End Function

[Function ccc$(t$)
With CreateObject("VBScript.RegExp"):  .Pattern = "(.+),(.+)"
ccc = .Replace(t, "$+")
End With
End Function
[/vba]

[vba]
Код
Function ddd$(t$)
With CreateObject("VBScript.RegExp"):  .IgnoreCase = True:  .Pattern = "(.+),(.+)"
ddd = .Replace(t, "$1")
End With
End Function
[/vba]
К сообщению приложен файл: example_22_08_2.xls(35.0 Kb)


Сообщение отредактировал sv2014 - Среда, 22.08.2018, 19:39
 
Ответить
СообщениеПАМ, добрый день,в завершение вариант UDF в столбцах B C или UDF ccc ddd

[vba]
Код
Function aaa$(t$)
With CreateObject("VBScript.RegExp"): .Pattern = "\d+": aaa = .Execute(t)(0)
End With
End Function
[/vba]

[vba]
Код
Function bbb$(t$)
With CreateObject("VBScript.RegExp")
.Pattern = "[ а-яё-]+": bbb = .Execute(t)(0)
End With
End Function

[Function ccc$(t$)
With CreateObject("VBScript.RegExp"):  .Pattern = "(.+),(.+)"
ccc = .Replace(t, "$+")
End With
End Function
[/vba]

[vba]
Код
Function ddd$(t$)
With CreateObject("VBScript.RegExp"):  .IgnoreCase = True:  .Pattern = "(.+),(.+)"
ddd = .Replace(t, "$1")
End With
End Function
[/vba]

Автор - sv2014
Дата добавления - 22.08.2018 в 18:42
Никанор Дата: Понедельник, 27.08.2018, 15:04 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте уважаемые товарищи помогающие!
Предлагаю рассмотреть мои варианты ответов на этот вопрос.
Ответы я сделал на основании формул _Boroda_, – Александра из Сообщение № 2 Дата: Среда, 22.08.2018, 16:26.
Только я изменил – добавил с условием ЕСЛИ ПУСТАЯ ячейка столбца А, тогда и РЕЗУЛЬТАТ ПУСТАЯ ячейка.

1- ый вариант формулы:
Код
=ЕСЛИ(A4<>"";Формула;"")

Надо ли в этом примере формулу оформлять тегами, так как в формуле не указаны ячейки, а лишь пример?

2-ой вариант формулы: =
Код
ЕСЛИОШИБКА(Формула);"")

Надо ли в этом примере формулу оформлять тегами, так как в формуле не указаны ячейки, а лишь пример?

1-ый вопрос: Только у меня в ячейках, выделенных жёлтой заливкой формулы не получились там написано примечания – может подскажите как их написать?
Или это не стоило делать, так как в файле Александра
7775440.xlsx(8.4 Kb) тоже в столбцах В и С формулы автоматично дают Результат ЕСЛИ пустая ячейка столбца А – тогда и ПУСТЫЕ ячейки столбцов B и C.
Только в столбцах D и E при пустой ячейке А Результат #ЗНАЧ!


В прилагаемом файле И ещё, ЕСЛИ очень длинное название или другое количество символов в столбце А, эти формулы не работают. То есть работают но неправильно.
В прилагаемом файле это 3-ий вариант.

2-ой вопрос: Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?

Файл прилагаю.
С уважением.
К сообщению приложен файл: _____7775440.xlsx(11.1 Kb)


Сообщение отредактировал Никанор - Понедельник, 27.08.2018, 15:10
 
Ответить
СообщениеЗдравствуйте уважаемые товарищи помогающие!
Предлагаю рассмотреть мои варианты ответов на этот вопрос.
Ответы я сделал на основании формул _Boroda_, – Александра из Сообщение № 2 Дата: Среда, 22.08.2018, 16:26.
Только я изменил – добавил с условием ЕСЛИ ПУСТАЯ ячейка столбца А, тогда и РЕЗУЛЬТАТ ПУСТАЯ ячейка.

1- ый вариант формулы:
Код
=ЕСЛИ(A4<>"";Формула;"")

Надо ли в этом примере формулу оформлять тегами, так как в формуле не указаны ячейки, а лишь пример?

2-ой вариант формулы: =
Код
ЕСЛИОШИБКА(Формула);"")

Надо ли в этом примере формулу оформлять тегами, так как в формуле не указаны ячейки, а лишь пример?

1-ый вопрос: Только у меня в ячейках, выделенных жёлтой заливкой формулы не получились там написано примечания – может подскажите как их написать?
Или это не стоило делать, так как в файле Александра
7775440.xlsx(8.4 Kb) тоже в столбцах В и С формулы автоматично дают Результат ЕСЛИ пустая ячейка столбца А – тогда и ПУСТЫЕ ячейки столбцов B и C.
Только в столбцах D и E при пустой ячейке А Результат #ЗНАЧ!


В прилагаемом файле И ещё, ЕСЛИ очень длинное название или другое количество символов в столбце А, эти формулы не работают. То есть работают но неправильно.
В прилагаемом файле это 3-ий вариант.

2-ой вопрос: Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?

Файл прилагаю.
С уважением.

Автор - Никанор
Дата добавления - 27.08.2018 в 15:04
_Boroda_ Дата: Понедельник, 27.08.2018, 15:12 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12984
Репутация: 5337 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Код
=ЕСЛИ(A4<>"";ПСТР(A4;ДЛСТР(D4)+2;99);"")

Код
=ЕСЛИОШИБКА(ПСТР(A11;ДЛСТР(D11)+2;99);"")

Не А, а D


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Код
=ЕСЛИ(A4<>"";ПСТР(A4;ДЛСТР(D4)+2;99);"")

Код
=ЕСЛИОШИБКА(ПСТР(A11;ДЛСТР(D11)+2;99);"")

Не А, а D

Автор - _Boroda_
Дата добавления - 27.08.2018 в 15:12
Nic70y Дата: Понедельник, 27.08.2018, 15:19 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4802
Репутация: 1037 ±
Замечаний: 0% ±

Excel 2013
без проверки на пустоту и не нужные пробелы в конце (для разнообразия)
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A4);" ";ПОВТОР(" ";99));99))


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениебез проверки на пустоту и не нужные пробелы в конце (для разнообразия)
Код
=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A4);" ";ПОВТОР(" ";99));99))

Автор - Nic70y
Дата добавления - 27.08.2018 в 15:19
Никанор Дата: Понедельник, 27.08.2018, 15:20 | Сообщение № 10
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Александр!
Спасибо за быстрый ответ на 8-ой минуте после публикации моего вопроса.
Скажите пожалуйста, почему
Не А, а D
, если данные вносятся в ячейки столбца А, а не столбца D?

2-ой вопрос: Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?


Сообщение отредактировал Никанор - Понедельник, 27.08.2018, 15:44
 
Ответить
СообщениеАлександр!
Спасибо за быстрый ответ на 8-ой минуте после публикации моего вопроса.
Скажите пожалуйста, почему
Не А, а D
, если данные вносятся в ячейки столбца А, а не столбца D?

2-ой вопрос: Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?

Автор - Никанор
Дата добавления - 27.08.2018 в 15:20
Никанор Дата: Понедельник, 27.08.2018, 15:23 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Nic70y, – Николай Здравствуйте!
Спасибо за быстрый ответ на 15-ой минуте после публикации моего вопроса.
Эта формула извлекает цифры, а как извлечь ТЕКСТ любой длины?


Сообщение отредактировал Никанор - Понедельник, 27.08.2018, 15:46
 
Ответить
СообщениеNic70y, – Николай Здравствуйте!
Спасибо за быстрый ответ на 15-ой минуте после публикации моего вопроса.
Эта формула извлекает цифры, а как извлечь ТЕКСТ любой длины?

Автор - Никанор
Дата добавления - 27.08.2018 в 15:23
Nic70y Дата: Понедельник, 27.08.2018, 15:28 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4802
Репутация: 1037 ±
Замечаний: 0% ±

Excel 2013
так вроде ответ уже есть
=ЛЕВБ(A1;ПОИСК(",";A1)-1)
ну и +
Код
=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(A4;",";ПОВТОР(" ";99));99))


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

Автор - Nic70y
Дата добавления - 27.08.2018 в 15:28
Никанор Дата: Понедельник, 27.08.2018, 15:42 | Сообщение № 13
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Да, совершенно верно – я понял, в формуле Александра в 22.08.2018 в 16:26, в сообщении № 2
формула ищет ПОИСК ТЕКСТ, до запятой, сколько ни было бы букв. Верно?

И Вы предложили другой вариант формулы.
Большое Вам спасибо за молниеносный ответ на мой вопрос на 5-ой минуте после публикации.
С уважением.


Сообщение отредактировал Никанор - Понедельник, 27.08.2018, 15:43
 
Ответить
СообщениеДа, совершенно верно – я понял, в формуле Александра в 22.08.2018 в 16:26, в сообщении № 2
формула ищет ПОИСК ТЕКСТ, до запятой, сколько ни было бы букв. Верно?

И Вы предложили другой вариант формулы.
Большое Вам спасибо за молниеносный ответ на мой вопрос на 5-ой минуте после публикации.
С уважением.

Автор - Никанор
Дата добавления - 27.08.2018 в 15:42
_Boroda_ Дата: Понедельник, 27.08.2018, 15:48 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12984
Репутация: 5337 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Никанор, 27.08.2018 в 15:42, в сообщении № 13 ()
Верно?

Верно. Это и будет ячейка столбца D. А потом мы считаем сколько букв (символов) в извлеченном тексте (пусть их будет, например, 15) и из исходного текста с помощью ПСТР вынимаем 99 символов, начиная с семнадцатого (15+2=17)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Никанор, 27.08.2018 в 15:42, в сообщении № 13 ()
Верно?

Верно. Это и будет ячейка столбца D. А потом мы считаем сколько букв (символов) в извлеченном тексте (пусть их будет, например, 15) и из исходного текста с помощью ПСТР вынимаем 99 символов, начиная с семнадцатого (15+2=17)

Автор - _Boroda_
Дата добавления - 27.08.2018 в 15:48
Никанор Дата: Понедельник, 27.08.2018, 15:55 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
Александр!
Спасибо за объяснение.
Правильно ли я понял, что ячейку столбца D можно назвать «промежуточной», для получения конечного результата?


Сообщение отредактировал Никанор - Понедельник, 27.08.2018, 15:56
 
Ответить
СообщениеАлександр!
Спасибо за объяснение.
Правильно ли я понял, что ячейку столбца D можно назвать «промежуточной», для получения конечного результата?

Автор - Никанор
Дата добавления - 27.08.2018 в 15:55
_Boroda_ Дата: Понедельник, 27.08.2018, 16:34 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 12984
Репутация: 5337 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Нет, неправильно. У нас конечный результат состоит из двух ячеек - с текстом и с цифрами. Никто не запрещает им ссылаться друг на друга. При этом они обе являются конечными, а не промежуточными.
Если бы нужно было только цыфры вытащить, то формула была бы другая.
Например
Код
=ПСТР(A1;ПОИСК(",";A1)+2;99)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНет, неправильно. У нас конечный результат состоит из двух ячеек - с текстом и с цифрами. Никто не запрещает им ссылаться друг на друга. При этом они обе являются конечными, а не промежуточными.
Если бы нужно было только цыфры вытащить, то формула была бы другая.
Например
Код
=ПСТР(A1;ПОИСК(",";A1)+2;99)

Автор - _Boroda_
Дата добавления - 27.08.2018 в 16:34
Никанор Дата: Понедельник, 27.08.2018, 17:02 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 325
Репутация: 2 ±
Замечаний: 0% ±

Excel 2007
_Boroda_ – Александр!
Большое спасибо за объяснение и за формулу «вытаскивания» – извлечения цифр!

Да, нужно мне было внимательно прочитать условие темы, где сказано:
как мне разделить с помощью дополнительных 2 столбцов отдельно текстовую часть от цифровой.

но цифр где-то 9, где-то 11, где-то 12, да и текст везде разный в плане количества символов.


и ПАМ – Александр в Сообщение № 5 Дата: Среда, 22.08.2018, 16:47. Поблагодарил Вас.

А я в Сообщение № 7 Дата: Понедельник, 27.08.2018, 15:04 во 2-ом вопросе «Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?» спрашивал то же самое что и было в условии.
Надо мне быть более внимательным.

С благодарностью и уважением.
 
Ответить
Сообщение_Boroda_ – Александр!
Большое спасибо за объяснение и за формулу «вытаскивания» – извлечения цифр!

Да, нужно мне было внимательно прочитать условие темы, где сказано:
как мне разделить с помощью дополнительных 2 столбцов отдельно текстовую часть от цифровой.

но цифр где-то 9, где-то 11, где-то 12, да и текст везде разный в плане количества символов.


и ПАМ – Александр в Сообщение № 5 Дата: Среда, 22.08.2018, 16:47. Поблагодарил Вас.

А я в Сообщение № 7 Дата: Понедельник, 27.08.2018, 15:04 во 2-ом вопросе «Можно ли написать универсальную формулу Разделение текста и цифр с условием, ЕСЛИ в ячейках столбца А разное количество цифр и букв?» спрашивал то же самое что и было в условии.
Надо мне быть более внимательным.

С благодарностью и уважением.

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

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