Progress-servis55.ru

Новости из мира ПК
32 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Символ конца строки в excel

ПСТР, ПСТРБ (функции ПСТР, ПСТРБ)

В этой статье описаны синтаксис формулы и использование функций ПСТР и ПСТРБ в Microsoft Excel.

Описание

Функция ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Функция ПСТРБ возвращает определенное число знаков из текстовой строки, начиная с указанной позиции, на основе заданного числа байтов.

Эти функции могут быть доступны не на всех языках.

Функция ПСТР предназначена для языков с однобайтовой кодировкой, а ПСТРБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

Функция ПСТР всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

Функция ПСТРБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ПСТРБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПСТР и ПСТРБ описаны ниже.

Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция Обязательный. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Если start_num больше, чем длина текста, функция ПСТР/ПСТРБ возвращает «» (пустая текст).

Если start_num меньше, чем длина текста, но start_num и num_chars превышает длину текста, функция ПСТР/ПСТРБ возвращает символы до конца текста.

Если start_num меньше 1, функция ПСТР/ПСТРБ возвращает значение #VALUE! значение ошибки #ЗНАЧ!.

Число_знаков Требуется для MID. Указывает, сколько знаков должна вернуть функция ПСТР.

Если значение «число_знаков» отрицательно, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Число_байтов Требуется для ПСТРБ. Указывает, сколько знаков должна вернуть функция ПСТРБ (в пересчете на байты).

Если значение «число_байтов» отрицательно, функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Возвращает пять знаков из строки в ячейке А2, начиная с первого знака.

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

Так как начальная позиция больше, чем длина строки (10), возвращается пустая строка.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Часть символов с конца строки до первого пробела перенести в другую ячейку

Добрый день!
Помогите, пожалуйста, решить такую задачу:

Есть строка, в которой прописан адрес. Мне нужно часть символов с конца строки до первого пробела перенести в другую ячейку.
Например:

СТРОКА:
ул Волгоградская, 1/8, кв. 198, г Волгоград, Волгоградская Область

Нужно отделить ТОЛЬКО «Волгоградская область» и поместить в другую ячейку.

Областей много, Вся Российская Федерация.

Буду признательна за оказанную помощь.

23.03.2016, 14:38

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

Как перенести часть данных из ячейки эксель в другую ячейку
Просьба помочь! нужно перенести из одной ячейки: UAH:384, USD:15, RUB:1067, EUR:13 только 1067.

Читать еще:  Просмотр в excel примеры

Необходимо перенести значение в другую ячейку
Здравствуйте! Помогите пож-та, задача следующая: необходимо перенести значение (к примеру (в.

Перенести значение ячейки в другую ячейку
НУЖЕН МУДРЫЙ ХЕЛП. Нужно перенести значение ячейки в другую ячейку(функцией) так, что бы.

23.03.2016, 15:29224.03.2016, 05:46 [ТС]3

Да, это в Excel нужно сделать. А перенести в любую ячейку. Т.е. мне нужно просто отделить эти символы с конца строки.

Добавлено через 5 минут
«Мне нужно часть символов с конца строки до первого пробела перенести в другую ячейку.»

Ссори за некорректное пояснение. Нужно не до первого пробела, а до первой запятой.

24.03.2016, 09:334 Вложения

btv1980.xls (13.5 Кб, 20 просмотров)
24.03.2016, 10:13524.03.2016, 10:27 [ТС]6

А можно сделать так, чтобы слово, перенесённое в другую ячейку, удалялось из общей строки?

Чтобы получилось так:

СТРОКА:
ул Больничная, 10, кв. 1, г Томск, Томская Область

РЕЗУЛЬТАТ ПОСЛЕ ОТДЕЛЕНИЯ:

ул Больничная, 10, кв. 1, г Томск

24.03.2016, 10:30724.03.2016, 10:34824.03.2016, 11:04924.03.2016, 11:0610

добрый день,вариант с функцией uuu,ответ на последний вопрос

Вложения

example_24_03_2016_comma_first.xls (35.0 Кб, 8 просмотров)
24.03.2016, 11:221124.03.2016, 11:3112

добавлю функцию для первого вопроса

24.03.2016, 11:5713

еще вариант функций

Вложения

example_24_03_2016_comma1_cbr.xls (36.0 Кб, 12 просмотров)
24.03.2016, 13:22 [ТС]1407.10.2016, 13:4515

2 Svsh2015
Ваши две функции замечательно работают для поставленной задачи. Прошу Вас, переделайте эту формулу что бы она могла работать для похожего случая, с той разницей, что бы отсечение шло не с конца строки, а с начала.
Конкретный пример — Есть одна колонка «слипшихся» данных; хочу разделить в две отдельные колонки текстовые и цифровые значения времени, а так же желательно убрать две цифры после двойных кавычек, вместе с двойным кавычками. Благодарю за внимание.

Timer 1: 30.06.16 18:56
Общее время: 00:00’23″27
Наим. Круг: 00:00’03″38
Наиб. Круг: 00:00’06″47
Средний Круг: 00:00’04″65

Круг 1: 00:00’03″81
Круг 2: 00:00’03″38
Круг 3: 00:00’05″01
Круг 4: 00:00’04″59
Круг 5: 00:00’06″47

08.10.2016, 10:061609.10.2016, 00:4617

в прошлом сообщении некорректно добавил Вас в получатели. вероятно так вам придёт оповещение о востребованности вашего внимания обратно к данному вопросу. благодарю за понимание.

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

для колонки со значениями времени (С), конечно, в идеале хочется вместо исходного формата времени ЧЧ:ММ’СС»дс видеть после работы функции отсечения только ММ:СС (убирая и значения часов, поскольку бег никогда не будет превышать суммарного рубежа в 1 час и доли секунд можно отбрасывать даже без округления до ближайшего целого)

09.10.2016, 00:46
09.10.2016, 00:46

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

Из каждой строки перенести в новую ячейку все знаки кроме первого слова
Здравствуйте, подскажите как будет выглядеть макрос для след. задачи. Имеется таблица ворд в ней.

Вытащить из запроса к ютубу код, до первого пробела или конца строчки
watch?v=((.*)(s|$) Необходимо вытащить из запроса код, до первого пробела или конца строчки, а.

Вытащить из текста дату и перенести в другую ячейку
Доброго! Помогите, пожалуйста, второй день бьюсь с данной проблемой. Из столбца «Биография» нужно.

Перенести слово в другую книгу в определенную ячейку
Есть две excel файла. В первом файле, таблица диапазоном 100х100 в каждом ячейке одно слово. Во.

Канал в Telegram

Вы здесь

Работа со строками в Excel. Текстовые функции Excel

Часто в Excel приходится тем или иным образом обрабатывать текстовые строки. Вручную такие операции проделывать очень сложно когда кол-во строк составляет не одну сотню. Для удобства в Excel реализован не плохой набор функций для работы со строковым набором данных. В этой статье я коротко опишу необходимые функции для работы со строками категории «Текстовые» и некоторые рассмотрим на примерах.

Функции категории «Текстовые»

Итак, рассмотрим основные и полезные функции категории «Текстовые», с остальными можно ознакомиться самостоятельно.

  • БАТТЕКСТ (Значение) – функция преобразующая число в текстовый тип;
  • ДЛСТР (Значение) – вспомогательная функция, очень полезна при работе со строками. Возвращает длину строки, т.е. кол-во символов содержащихся в строке;
  • ЗАМЕНИТЬ (Старый текст, Начальная позиция, число знаков, новый текст) – заменяет указанное кол-во знаков с определенной позиции в старом тексте на новый;
  • ЗНАЧЕН (Текст) – преобразует текст в число;
  • ЛЕВСИМВ (Строка, Кол-во знаков) – очень полезная функция, возвращает указанное кол-во символов, начиная с первого символа;
  • ПРАВСИМВ (Строка, Кол-во знаков) – аналог функции ЛЕВСИМВ, с той лишь разницей, что возврат символов с последнего символа строки;
  • НАЙТИ (текст для поиска, текст в котором ищем, начальная позиция) – функция возвращает позицию, с которой начинается вхождение искомого текста. Регистр символов учитывается. Если необходимо не различать регистр символов, воспользуйтесь функцией ПОИСК. Возвращается позиция только первого вхождения в строке!
  • ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция) – интересная функция, на первый взгляд похожа на функцию ЗАМЕНИТЬ, но функция ПОДСТАВИТЬ способна заменить на новую подстроку все вхождения в строке, если опущен аргумент «позиция»;
  • ПСТР (Текст, Начальная позиция, Кол-во знаков) – функция похожа на ЛЕВСИМВ, но способна возвратить символы с указанной позиции:
  • СЦЕПИТЬ (Текст1, Текст 2 …. Текст 30) – функция позволяет соединить до 30-ти строк. Так же, можно воспользоваться символом «&», выглядеть будет так «=”Текст1” & ”Текст2” & ”Текст3”»;

Это в основном часто используемые функции при работе со строками. Теперь рассмотрим пару примеров, которые продемонстрируют работу некоторых функций.

Пример 1
Дан набор строк:

Необходимо из этих строк извлечь даты, номера накладных, а так же, добавить поле месяц для фильтрации строк по месяцам.

Извлечем в столбец В номера накладных. Для этого найдем так называемый ключевой символ или слово. В нашем примере видно, что перед каждым номером накладной стоит «№», а длина номера накладной 6 символов. Воспользуемся функциями НАЙТИ и ПСТР. Пишем в ячейку B2 следующую формулу :

= ПСТР (A2; НАЙТИ («№»;A2)+1;6)

Разберем формулу. Из строки А2 с позиции следующей после найденного знака «№», мы извлекаем 6 символов номера.

Теперь извлечем дату. Тут все просто. Дата расположена в конце строки и занимает 8 символов. Формула для С2 следующая:

= ПРАВСИМВ (A2;8)

но извлеченная дата у нас будет строкой, чтоб преобразовать ее в дату необходимо после извлечения, текст перевести в число:

= ЗНАЧЕН ( ПРАВСИМВ (A2;8))

а затем, задать формат отображения в ячейке, как это сделать было описано в статье «Формат данных в Excel».

Ну и последнее, для удобства дальнейшей фильтрации строк, введем столбец месяц, который мы получим из даты. Только для создания месяца нам необходимо откинуть день и заменить его на «01». Формула для D2:

= ЗНАЧЕН ( СЦЕПИТЬ («01»; ПРАВСИМВ (A2;6))) или = ЗНАЧЕН («01″& ПРАВСИМВ (A2;6))

Задайте формат ячеке «ММММ ГГГГ». Результат:

Пример 2
В строке «Пример работы со строками в Excel» необходимо все пробелы заменить на знак «_», так же перед словом «Excel» добавить «MS».

Формула будет следующая:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(A1;ПОИСК(«excel»;A1);0;»MS «);» «;»_»)

Для того, чтоб понять данную формулу, разбейте ее на три столбца. Начните с ПОИСК, последней будет ПОДСТАВИТЬ.

Все. Если есть вопросы, задавайте, не стесняйтесь

Извлекаем в EXCEL число из конца текстовой строки

Извлечем число из конца текстовой строки, например, из строки «Филатова123» получим «123».

Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0); 255)

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

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

О построении этой формулы смотрите здесь . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .

Несколько чисел в текстовой строке (через WORD)

Если в текстовой строке расположено несколько чисел, то вышеуказанные формулы работать не будут. В этом случае можно предложить следующий подход:

  • скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
  • нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
  • В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);

  • в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
  • нажмите ОК, буквы будут заменены пробелами или просто убраны;
  • скопируйте столбец обратно в MS EXCEL.

Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.

Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)

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

  • подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
  • создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
  • заголовкам столбцов присвойте порядковые номера ;
  • в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.

Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(—ПСТР($A3;C$2;1));»»;—ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0

Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)

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

  • как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
  • с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами — 1 пробел;
  • с помощью функции ПОИСК() находим начальные позиции каждого числа;
  • с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.

Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.

Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.

Ссылка на основную публикацию
Adblock
detector