Функция расцепить в excel примеры

Содержание:

Разделить текст и числа в ячейке в Excel

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

Как разделить текст и числа в ячейке в Excel?

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

Во-первых, вам нужно определить позицию первого числа; Во-вторых, извлечь текст из ячейки; Наконец, получите числа.

Общие формулы

Общая формула для определения позиции первого числа

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&»0123456789″))

Общая формула для получения текста

=LEFT(A1,position-1)

Общая формула для получения чисел

=RIGHT(A1,LEN(A1)-position+1)

Аргументы приведенных выше формул

A1: Представляет ячейку, содержащую текст и числа, которые вы хотите разделить;

Позиция: Позиция первого числа в A1.

Как пользоваться этими формулами?

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

Во-первых, верните начальную позицию чисел в текстовой строке

1. Выберите пустую ячейку для вывода позиции. В данном случае я выбираю C3.

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

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&»0123456789″))

Во-вторых, получить текст

1. Выберите пустую ячейку, чтобы найти текст. Здесь я выбираю D3.

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

=LEFT(B3,C3-1)

Наконец, получите числа

1. Выберите пустую ячейку, например E3, чтобы найти числа.

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

=RIGHT(B3,LEN(B3)-C3+1)

Как работают эти формулы?

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B3&»0123456789″))

1. FIND({0,1,2,3,4,5,6,7,8,9},B3&»0123456789″)

B3&»0123456789″ возвращает результат как: ana varela 240.150123456789.
FIND({0,1,2,3,4,5,6,7,8,9}, «ana varela 240.150123456789»): Функция Find находит позицию цифр «0–9» в ana varela 240.150123456789 и возвращает результат как {14,16,12,21,13,17,24,25,26,27}. Результат {14,16,12,21,13,17,24,25,26,27} означает, что число 0 находится на 14-й позиции в ана варела 240.150123456789, число 1 находится на 16 позиции в ана варела 240.150123456789…

2. =MIN({14,16,12,21,13,17,24,25,26,27}): Функция MIN получает минимальное значение массива. Здесь результат 12.

=LEFT(B3,C3-1)

LEFT(ana varela 240.15,12-1) это означает, что функция LEFT извлекает первые 11 символов из левой части текстовой строки «ana varela 240.15». Конечный результат — ана варела.

=RIGHT(B3,LEN(B3)-C3+1)

1. LEN(B3)-C3+1:

Функция LEN вычисляет общую длину текстовой строки (ana varela 240.150123456789) в B3 и возвращает результат как 17.
17-12 + 1: Общая длина чисел в ана варела 240.15. Результат — 6.

2. =RIGHT(B3,6): Функция ВПРАВО извлекает 6 символов из правой части ana varela 240.15. И окончательный результат — 240.15.

Связанные функции

Функция Excel MIDФункция Excel MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.

Функция поиска в Excel Функция Excel FIND используется для поиска строки внутри другой строки и возвращает начальную позицию строки внутри другой строки.

Функция ВЛЕВО в Excel Функция Excel LEFT извлекает заданное количество символов из левой части предоставленной строки.

Функция Excel ВПРАВО Функция Excel RIGHT извлекает определенное количество символов из правой части текстовой строки.

Функция ДЛСТР в Excel Функция Excel LEN возвращает количество символов в текстовой строке.

Родственные формулы

Разделение размеров на две части в Excel В этой статье объясняется, как использовать формулу для разделения размеров в ячейке на две части без единиц измерения (индивидуальная длина и ширина).

Отделяйте числа от единиц измерения В этой статье объясняется, как использовать формулу для отделения чисел от единиц измерения.

Разделить текстовую строку на определенный символ в ячейке в Excel В этом руководстве подробно объясняется, как разбить текстовую строку по определенному символу в ячейке с формулами.

Объединение нескольких ячеек в одну в Excel

Функция консолидация в excel примеры

Существует несколько различных способов слияния ячеек, а также несколько разновидностей объединения. Но все они сводятся к выделению клеток и применению функции объединения. Используя разные способы, вы получите одинаковый результат и сможете выбрать тот, который вам больше понравится.

Способ 1: Использование контекстного меню

Выделите несколько секций, которые вы хотите объединить. Область с выделенными клетками должна иметь форму прямоугольника, а соседние ячейки должны примыкать к границам друг друга. Для этого нажмите и удерживайте нажатой клавишу «Ctrl». В это время сделайте клик по элементам для выделения. Либо зажмите левую клавишу мыши на начальной клетке и растяните область выделения. Тем самым, покрыв ней нужный диапазон элементов.
Сделайте клик правой клавишей мыши по области с выделенными секциями. В открывшемся меню найдите «Формат ячеек».
Перейдите ко вкладке «Выравнивание». В разделе «Отображение» найдите пункт «Объединение ячеек». Ставьте галку в этом пункте. Нажмите на кнопку «ОК» для применения операции.

В результате выполнения всех шагов у вас должна получиться одна клетка, которая заменяет предыдущие.

Способ 2: Слияние без потери значений

Если же вы не хотите потерять данные из клеток которые вы будете объединять, воспользуйтесь этим способом. Он предполагает использование специальной функции «СЦЕПИТЬ». Эта функция позволит вам сохранить данные объединяемых элементов.

Первым делом вам нужно вставить еще одну ячейку между теми, которые вы хотите объединить. Для этого сделайте клик правой клавишей мыши по крайней правой клетке из области объединяемых. В открывшемся меню сделайте клик по пункту «Вставить».
Откроется окно, в котором вам нужно будет выбрать пункт «Столбец» и нажать кнопку «ОК».
Активируйте новую клетку, кликнув по ней мышкой. Теперь вам необходимо ввести в эту ячейку специальную формулу. В строке значения данных вставьте следующую строку без кавычек «=СЦЕПИТЬ(X;Y)». Вместо X и Y вы должны ввести координаты ваших клеток, которые необходимо объединить. В нашем случае формула будет иметь вид «=СЦЕПИТЬ(А1;С1)». После чего жмите клавишу «Enter».

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

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

Выделите новый элемент и сделайте по нему клик правой клавишей мыши. В появившемся меню выберите «Копировать».
Чтобы вставить скопированную ячейку вместе с ее данными на место другой ячейки, сделайте следующее. Кликните правой клавишей мыши по месту для вставки. В открывшемся меню выберите в параметрах вставки «Значения».

Теперь вы можете удалить две предыдущие клетки, оставив только эту.

Способ 3: Применив кнопку на панели инструментов

Повторите выделение ячеек, пользуясь методами описанными выше.
Выделив необходимые клетки, нажмите на кнопку «Объединить и поместить в центре». Она располагается на вкладке «Главная» в блоке «Выравнивание».

После применения этой операции вы увидите аналогичный результат. Однако содержимое элемента будет выровнено по центру.

Способ 4: Скопировать и вставить

Еще один очень интересный метод создания больших ячеек — вставка уже готовых элементов. К примеру, если вам нужно создать множество объединенных клеток, не обязательно каждый раз использовать функцию объединения.

Создайте одну большую ячейку из нескольких маленьких путем их слияния.
Теперь выделите эту клетку при помощи курсора мыши, сделав клик по ней.
Используйте комбинацию клавиш «Ctrl+C» для копирования этой секции либо сделайте клик правой клавишей мыши и выберите пункт «Копировать».
Выделите курсором клетку, в которую вам нужно вставить этот элемент и нажмите сочетание клавиш «Ctrl+V» для вставки. Либо используйте правую клавишу мыши и выберите пункт «Вставить».

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

Теперь основная суть приема.

Функция счетесли в excel

Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки

То есть ищем запятую, в тексте, начиная с позиции 1

Шаг 2. Далее в ячейке E1 прописываем формулу для нахождения второго знака (в нашем случае запятой). Формула аналогичная, но с небольшими изменениями.

Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

Начальная позиция у нас 1, количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз

Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1

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

Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1 Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.

Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением.

Так же можно сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получим итоговое решение задачи

Примечание. Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений.

Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя

а первого текста как

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

В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.

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

Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.

Таким образом последний текст будет рассчитываться начиная от последней запятой до чуть меньше 1000 знаков, то есть до конца строки, что нам и требуется.

Оба варианта выложу для скачивания.

Скачать пример: Как разделить текст по столбцам с помощью функции_1.xlsx (исправлено: доп поле)
Скачать пример: Как разделить текст по столбцам с помощью функции_2.xlsx (исправлено: заведомо большое число)

(Пока оценок нет)

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

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

Для удобства также приводим ссылку на оригинал (на английском языке).

Умножение и деление в Excel не представляют никаких сложностей: достаточно создать простую формулу. Не забывайте, что все формулы в Excel начинаются со знака равенства (=), а для их создания можно использовать строку формул.

Как разделить текст на две ячейки Excel

Функция если в excel

Допустим на лист Excel были импортированные данные из другой программы. Из-за несовместимости структуры данных при импорте некоторые значение из разных категорий были внесены в одну ячейку. Необходимо из этой ячейки отделить целые числовые значения. Пример таких неправильно импортированных данных отображен ниже на рисунке:

Сначала определим закономерность, по которой можно определить, что данные из разных категорий, несмотря на то, что они находятся в одной и той же строке. В нашем случае нас интересуют только числа, которые находятся вне квадратных скобок. Каким способом можно быстро выбрать из строк целые числа и поместить их в отдельные ячейки? Эффективным решением является гибкая формула основана на текстовых функциях.

В ячейку B3 введите следующую формулу:

Теперь скопируйте эту формулу вдоль целого столбца:

Выборка чисел из строк в отдельные ячейки.

Описание формулы для разделения текста по ячейкам:

Функция ПСТР возвращает текстовое значение содержащие определенное количество символов в строке. Аргументы функции:

Первый аргумент – это ссылка на ячейку с исходным текстом.
Второй аргумент – это позиция первого символа, с которого должна начинаться разделенная строка.
Последний аргумент – это количество символов, которое должна содержать разделенная строка.

С первым аргументом ПСТР все понятно – это ссылка на ячейку A3. Второй аргумент мы вычисляем с помощью функции НАЙТИ(“]”;A3)+2. Она возвращает очередной номер символа первой закрывающейся квадратной скобки в строке. И к этому номеру мы добавляем еще число 2, так как нам нужен номер символа после пробела за квадратной скобкой. В последнем аргументе функция вычисляет какое количество символов будет содержать разделенная строка после разделения, учитывая положение квадратной скобки.

Обратите внимание! Что в нашем примере все исходные и разделенные строки имеют разную длину и разное количество символов. Именно поэтому мы называли такую формулу – гибкой, в начале статьи

Она подходит для любых условий при решении подобного рода задач. Гибкость придает ей сложная комбинация из функций НАЙТИ. Пользователю формулы достаточно определить закономерность и указать их в параметрах функций: будут это квадратные скобки либо другие разделительные знаки. Например, это могут быть пробелы если нужно разделить строку на слова и т.п.

В данном примере функция НАЙТИ во втором аргументе определяет положение относительно первой закрывающейся скобки. А в третьем аргументе эта же функция вычисляет положение нужного нам текста в строке относительно второй открывающийся квадратной скобки. Вычисление в третьем аргументе более сложное и оно подразумевает вычитание одной большей длинны текста от меньшей. А чтобы учитывать еще 2 пробела следует вычитать число 3. В результате чего получаем правильное количество символов в разделенной строке. С помощью такой гибкой формулы можно делать выборку разной длинны разделенного текста из разных длинны исходных строк.

Теперь основная суть приема.

=НАЙТИ(B1-A1-1)

То есть ищем запятую, в тексте, начиная с позиции 1

=НАЙТИ($B1-$A1-D1+1)

Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

=ПСТР(A1-1-D1-1)

Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1

=ПСТР($A1-D1+1-E1-D1-1)

Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

=ЕСЛИОШИБКА(ПСТР($A1-D1+1-E1-D1-1)- «»)

=НАЙТИ($B1-$A1-C1+1)

а первого текста как

=ПСТР($A1-C1+1-D1-C1-1)

=ЕСЛИОШИБКА(НАЙТИ($B1-$A1-C1+1)-1000)

Оба варианта выложу для скачивания.

Скачать пример: Как разделить текст по столбцам с помощью функции_1.xlsx (исправлено: доп поле)
Скачать пример: Как разделить текст по столбцам с помощью функции_2.xlsx (исправлено: заведомо большое число)

Делим текст вида ФИО по столбцам.

Если выяснение загадочных поворотов формул Excel — не ваше любимое занятие, вам может понравиться визуальный метод разделения ячеек, который демонстрируется ниже.

В столбце A нашей таблицы записаны Фамилии, имена и отчества сотрудников. Необходимо разделить их на 3 столбца.

Можно сделать это при помощи инструмента «Текст по столбцам». Об этом методе мы достаточно подробно рассказывали, когда рассматривали, как можно разделить ячейку по столбцам.

Кратко напомним:

На ленте «Данные» выбираем «Текст по столбцам» — с разделителями.

Далее в качестве разделителя выбираем пробел.

Обращаем внимание на то, как разделены наши данные в окне образца. В следующем окне определяем формат данных

По умолчанию там будет «Общий». Он нас вполне устраивает, поэтому оставляем как есть. Выбираем левую верхнюю ячейку диапазона, в который будет помещен наш разделенный текст. Если нужно оставить в неприкосновенности исходные данные, лучше выбрать B1, к примеру

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

В итоге имеем следующую картину:

При желании можно дать заголовки новым столбцам B,C,D.

А теперь давайте тот же результат получим при помощи формул.

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

Итак, чтобы выделить из нашего ФИО фамилию, будем использовать выражение

В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.

Далее будет чуть сложнее.

Нужно извлечь второе слово, то есть имя. Чтобы вырезать кусочек из середины, используем функцию ПСТР.

Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:

ПСТР (текст; начальная_позиция; количество_знаков)

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:

Начальная позиция — это позиция первого пробела плюс 1:

ПОИСК(» «;A2) + 1

Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В итоге имя у нас теперь находится в C.

Осталось отчество. Для него используем выражение:

В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.

Вот результат нашей работы по разделению фамилии, имени и отчества из одной по отдельным ячейкам.

Как в Excel разбить предложение на слова?

Вы когда-нибудь пробовали разбить предложение на отдельные слова и поместить каждое слово в одну ячейку? Здесь я расскажу, как быстро разбить предложение на отдельные ячейки по словам в Excel.

Разделите предложение на отдельные слова с помощью функции Text to Column

В Excel есть функция Text to Columns, которая может помочь вам с этим справиться.

1. Выберите предложения, которые хотите разбить на слова, и нажмите Данные > Текст в столбцы. Смотрите скриншот:

2. в шаг 1 мастера преобразования текста в столбцы, проверить разграниченный и нажмите Next продолжать. Смотрите скриншот:

3. в шаг 2 мастера, проверить Space в Разделители раздел. Смотрите скриншот:

4. Нажмите Next , чтобы перейти к последнему шагу мастера и выбрать ячейку для размещения отдельных слов. Смотрите скриншот:

5. Нажмите Завершить, а выбранные предложения разбиты на слова в отдельных ячейках.

Разделите предложение на отдельные слова с помощью Kutools for Excel

С функцией Text to Columns шаги мастера немного сложны, но с Kutools for ExcelАвтора Разделить клетки утилита, операции будут намного проще.

Kutools for Excel, с более чем
300 удобные функции, облегчающие вашу работу.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выделите ячейки предложения и щелкните Kutools > Слияние и разделение > Разделить клетки. Смотрите скриншот:

2. в Разделить клетки в диалоговом окне выберите нужный тип разделения. Тип раздел, а чек Spacе в Разделить на раздел. Смотрите скриншот:

3. Нажмите В порядке, выберите ячейку для вывода разделенных ячеек в всплывающем диалоговом окне и нажмите OK заканчивать. Смотрите скриншот:

После этого предложения были разбиты на отдельные слова.

Разбить предложение на слова

В некоторых случаях вам может потребоваться транспонировать диапазон ячеек в один столбец или строку или наоборот, преобразовать столбец или строку в несколько строк и столбцов на листе Excel. Есть ли у вас быстрый способ решить эту проблему? Здесь Диапазон транспонирования функции в Kutools for Excel может легко справиться со всеми вышеуказанными работами.Нажмите, чтобы получить бесплатную полнофункциональную пробную версию через 30 дней!

Kutools for Excel: с более чем 300 удобными надстройками Excel, вы можете попробовать бесплатно без ограничений в течение 30 дней.

Относительные статьи:

Как разделить дату и время из ячейки на две отдельные ячейки в Excel?
Как разбить длинный столбец на несколько столбцов в Excel?
Как быстро разделить дату на день, месяц и год в Excel?