Как разбить число на цифры в excel
Ниже мы объяснили различные способы извлечения чисел из строк в Excel. Прочтите всю статью, чтобы узнать об этой технике.
Когда мы получаем данные, они следуют определенному шаблону, и наличие всех чисел в конце строки является одним из шаблонов.
Вы можете скачать это извлечение числа из строкового шаблона Excel здесь — Извлечение числа из строкового шаблона Excel
Например, город с его пин-кодом ниже является примером того же.
В приведенном выше примере у нас есть название города и почтовый индекс. В этом случае мы знаем, что нам нужно извлечь почтовый индекс из правой части строки. Но одна из проблем заключается в том, что мы не знаем точно, сколько цифр нам нужно из правой части строки.
Одна из обычных вещей перед началом числового значения — это символ подчеркивания (_). Во-первых, нам нужно определить положение символа подчеркивания. Это можно сделать с помощью метода НАЙТИ. Поэтому примените функцию НАЙТИ в Excel.
Какой текст нам нужно найти? Найти текст аргумент? В этом примере нам нужно найти позицию подчеркивания, поэтому введите подчеркивание в двойных кавычках.
Внутри текста в каком тексте нам нужно найти упомянутый текст, поэтому выберите ссылку на ячейку.
Последний аргумент не требуется, поэтому оставьте его сейчас.
Итак, у нас есть позиции символа подчеркивания для каждой ячейки. Теперь нам нужно полностью определить, сколько символов у нас есть во всем тексте. Примените функцию LEN в Excel, чтобы получить общую длину текстового значения.
Теперь у нас есть общее количество символов и позиции подчеркивания перед числовым значением. Чтобы предоставить количество символов, необходимое для функции ВПРАВО, нам нужно вычесть общее количество символов с позицией подчеркивания.
Таким образом, мы можем получить числа с правой стороны, когда у нас есть общая буква перед числом, начинающимся в строковом значении. Вместо того чтобы иметь так много вспомогательных столбцов, мы можем применить формулу к одной ячейке.
Это устранит все опорные колонны и значительно сократит время.
1) Выделяем самую первую ячейку столбца, в который будет выводится результат вычисления. Устанавливаем в ней знак равенства. Теперь нажимаем на первую ячейку в столбце, который будет являться делимым, после чего устанавливаем знак деления.
Так же в редакторе таблиц Microsoft Excel вы сможете разделить числа из ячеек целого столбца на какое-либо одно неизменное число. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Рассмотрим самый простой способ разделить текст в ячейке, не по сути, а по исполнению. Для примера, очень наглядно это можно продемонстрировать на ФИО в одной ячейке, которые необходимо разделить на отдельные столбики для удобства сортировки и фильтрации.
Извлечь число из строки Excel.
Как выделить время из даты в Excel?
Извлечь время только из даты и времени с помощью формулы
= ВРЕМЯ (ЧАС (A1); МИНУТА (A1); СЕКУНДА (A1)) (A1 — первая ячейка списка, из которого вы хотите извлечь время), нажмите Enter и перетащите маркер заполнения, чтобы заполнить диапазон.
Допустим, вы хотите узнать количество человеко-часов, затраченных на завершение проекта общее количество сотрудников в Project , или фактические нормы миль на галлон для вашего последнего межфирменного перерыва общее количество мильных галлонов. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Важно: Убедитесь, что перед вводом чисел и оператора () в ячейку введите знак равенства ( =); в противном случае приложение Excel будет интерпретировать введенную дату. Например, если ввести 7/30, Excel может отобразить 30-е в ячейке. Если вы вводите 12/36, Excel сначала преобразует это значение в 12/1/1936 и отобразит в ячейке 1-дек.
Как в эксель разделить сумму на число
Деление, наряду с остальными тремя простейшими арифметическими действиями (сложение, вычитание и умножение), часто используется при работе с данными в таблицах Эксель. И практически любые расчеты в той или иной их части требуют выполнения операции деления.
Рассоединяем текст с помощью формул
Для этого способа нам понадобятся возможности сочетаний функций ПОИСК и ПСТР. При помощи функции ПОИСК мы будем искать все пробелы, которые есть между словами (например, между фамилией, именем и отчеством). Потом функцией ПСТР выделяем необходимое количество символов для дальнейшего разделения.
И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.
А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:
- Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
- Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).
В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.
Если же значение в ячейке будете делить на две части, то ищете только один пробел (или иной разделитель), а вот чтобы разделить более 4 слов, в формулу добавьте поиск необходимых разделителей.
При необходимости, два описанных метода выше можно совмещать, используя в одном арифметическом выражении как адреса ячеек, так и конкретные числовые значения, вводимые при помощи клавиатуры. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В этом методе мы опишем алгоритм действий, при помощи которого можно разделить данные в столбце на определенное число с постоянным значением – константу, и поместить результат вычислений в ячейки отдельного столбца. Вот что для этого нужно сделать:
Как в экселе разделить на 1000
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст » Было доставлено кусков мыла 763шт., а заказывали 780 » и ему подобный, где числа раскиданы по тексту.
Добрый день!
Спасибо за замечательный код! Подскажите пожалуйста, как оставить среди неудаляемых символов пробел?
Например, в ячейке было «Т-образный поворот SPB-RF60 TE-200 SS316L».
Ваш код оставляет (после некоторой модификации) «60200316».
А хотелось бы «60 200 316».
Я не понимаю как вписать пробел в строку
Like «*[0-9.,;:-]*» Then
Здравствуйте! Подскажите пожалуйста, как разложить число на составляющие его цифры (пример прилагается). Как проще и лучше это реализовать, макросом или формулой. Спасибо!
Здравствуйте! Подскажите пожалуйста, как разложить число на составляющие его цифры (пример прилагается). Как проще и лучше это реализовать, макросом или формулой. Спасибо! djon2012
для написания в столбце В и протягивании от него вправо. Если формула не в В, а, например, в Е, то -1 нужно заменить на -4, что не очень удобно
Можно написать так
для написания в столбце В и протягивании от него вправо. Если формула не в В, а, например, в Е, то -1 нужно заменить на -4, что не очень удобно
Можно написать так
для написания в столбце В и протягивании от него вправо. Если формула не в В, а, например, в Е, то -1 нужно заменить на -4, что не очень удобно
Можно написать так
Подскажите пожалуйста, почему в прикрепленном файле число в ячейке А2 неправильно разлагается на цифры. Спасибо!
Подскажите пожалуйста, почему в прикрепленном файле число в ячейке А2 неправильно разлагается на цифры. Спасибо! djon2012
Потому, что Excel умный слишком. Он автоматически преобразовал большое число в
7,45636353444355E+30
Вот так попробуйте
Но только учтите, что в Excel выводится 15 значащих цифр, (остальное заполняется нулями) и Ваше произведение на самом деле может быть вовсе не таким, как посчитал его Excel
Потому, что Excel умный слишком. Он автоматически преобразовал большое число в
7,45636353444355E+30
Вот так попробуйте
Но только учтите, что в Excel выводится 15 значащих цифр, (остальное заполняется нулями) и Ваше произведение на самом деле может быть вовсе не таким, как посчитал его Excel _Boroda_
Но только учтите, что в Excel выводится 15 значащих цифр, (остальное заполняется нулями) и Ваше произведение на самом деле может быть вовсе не таким, как посчитал его Excel Автор - _Boroda_
Дата добавления - 05.07.2019 в 09:22
Спасибо Boroda за совет, а то я пробовал изменять числовой формат в ячейке но увы безрезультатно. djon2012
А если цыфры идут и двузначные тоже. Например 4 12 15 22 только слитно. Это не выполнимо этими формулами
А если цыфры идут и двузначные тоже. Например 4 12 15 22 только слитно. Это не выполнимо этими формулами Leonid9156
двузначных цИфр не бывает, бывают числа. и закономерный вопрос - по какому признаку надо определить, что именно 4 12 15 22, а не 41 2 15 22 или 4 1 21 52 2 ?
[p.s.]а исходная задачка в 365 решается без протяжки формул:
двузначных цИфр не бывает, бывают числа. и закономерный вопрос - по какому признаку надо определить, что именно 4 12 15 22, а не 41 2 15 22 или 4 1 21 52 2 ?
[p.s.]а исходная задачка в 365 решается без протяжки формул:
двузначных цИфр не бывает, бывают числа. и закономерный вопрос - по какому признаку надо определить, что именно 4 12 15 22, а не 41 2 15 22 или 4 1 21 52 2 ?
[p.s.]а исходная задачка в 365 решается без протяжки формул:
И как формула должна понять, что именно извлекать 41 или 12, 21 или 15 и т.д.?
П.С. Пока писал Михаил меня опередил)
И как формула должна понять, что именно извлекать 41 или 12, 21 или 15 и т.д.?
П.С. Пока писал Михаил меня опередил) Egyptian
И как формула должна понять, что именно извлекать 41 или 12, 21 или 15 и т.д.?
П.С. Пока писал Михаил меня опередил) Автор - Egyptian
Дата добавления - 13.03.2022 в 22:24
ну, смею предположить что записана последовательность по возрастанию и следовательно можно предположить как разделить Но вот анализ формулой много сложнее и совершенно не относится к теме.
ну, смею предположить что записана последовательность по возрастанию и следовательно можно предположить как разделить Но вот анализ формулой много сложнее и совершенно не относится к теме. bmv98rus
Как разбить или разбить число на отдельные цифры в Excel?
Предположим, вам нужно разбить номер на отдельные цифры, как показано на скриншоте ниже, что вы можете сделать для этого? Эта статья предоставит вам два метода.
Kutools for Excel's Split Cells utility helps you easily split number into individual digits or split cell contents by space, comma, new line or other separators into separated rows or columns in Excel as below screenshot showed. Download the full feature 60-day free trail of Kutools for Excel now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Разбейте или разделите номер на отдельные цифры с помощью формулы
В этом разделе будет показана формула для разделения выбранных числовых ячеек на отдельные цифры в Excel.
1. Выберите пустую ячейку (говорит ячейка C1), чтобы найти первую разделенную цифру числа в ячейке A1, затем введите формулу = MID ($ A1; COLUMN () - (COLUMN ($ C1) - 1); 1) в строку формул, а затем нажмите Enter ключ.
Внимание: В формуле A1 - это ячейка с номером, который нужно разделить на цифры, а C1 - это ячейка для определения первой разделенной цифры. Пожалуйста, измените их по своему усмотрению.
2. Продолжайте выбирать ячейку C1, затем перетащите маркер заполнения в правые ячейки, пока все цифры ячейки A1 не будут разделены.
3. Не снимая выделения с этих ячеек с разделенными цифрами, перетащите маркер заполнения вниз к ячейкам, пока все числа не будут разделены на отдельные цифры. Смотрите скриншот:
Разбейте или разделите номер на отдельные цифры с помощью Kutools for Excel
Разделить клетки полезности Kutools for Excel помогает легко разделить все выбранные числовые ячейки на отдельные цифры сразу. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выделите ячейки с номерами, которые нужно разбить на цифры, затем нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:
2. в Разделить клетки диалоговое окно, выберите Разделить на столбцы вариант в Тип и в разделе Указать разделитель выберите Указать ширину и введите число 1 в текстовое поле. Щелкните значок OK кнопку.
3. В следующем всплывающем окне Разделить клетки диалоговом окне укажите пустую ячейку для поиска первой цифры разделения, а затем щелкните OK кнопку.
После нажатия OK Кнопка, все числа в выбранных ячейках сразу же разбиваются на отдельные цифры, как показано на скриншоте ниже.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Разделить текст и числа в ячейке в Excel
Предположим, что данные ячейки смешаны с текстом и числами, как вы можете разделить их на отдельные ячейки столбца? Этот туториал покажет вам подробные шаги, чтобы разобраться с формулами.
Как разделить текст и числа в ячейке в Excel?
В этом случае вам необходимо применить три формулы для разделения текста и чисел в ячейке на отдельные ячейки столбца.
Во-первых, вам нужно определить позицию первого числа;
Во-вторых, извлечь текст из ячейки;
Наконец, получите числа.
Общие формулы
Общая формула для определения позиции первого числа
Общая формула для получения текста
Общая формула для получения чисел
Аргументы приведенных выше формул
A1: Представляет ячейку, содержащую текст и числа, которые вы хотите разделить;
Позиция: Позиция первого числа в A1.
Как пользоваться этими формулами?
Предположим, у вас есть список текстовых строк, как показано на скриншоте ниже, вы можете сделать следующее, чтобы разделить текст и числа на отдельные ячейки.
Во-первых, верните начальную позицию чисел в текстовой строке
1. Выберите пустую ячейку для вывода позиции. В данном случае я выбираю C3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата, перетащите маркер заполнения вниз, чтобы применить формулу к другой ячейке, пока не отобразятся все нужные позиции.
Во-вторых, получить текст
1. Выберите пустую ячейку, чтобы найти текст. Здесь я выбираю D3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите эту ячейку результата, перетащите маркер заполнения вниз, чтобы получить все тексты ячеек ниже.
=LEFT(B3,C3-1)
Наконец, получите числа
1. Выберите пустую ячейку, например E3, чтобы найти числа.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите эту ячейку результата, перетащите маркер заполнения вниз, чтобы получить все номера ячеек ниже.
=RIGHT(B3,LEN(B3)-C3+1)
Как работают эти формулы?
- B3&"0123456789" возвращает результат как: ana varela 240.150123456789.
- FIND(, "ana varela 240.150123456789") : Функция Find находит позицию цифр «0–9» в ana varela 240.150123456789 и возвращает результат как .
Результат означает, что число 0 находится на 14-й позиции в ана варела 240.150123456789, число 1 находится на 16 позиции в ана варела 240.150123456789…
2. =MIN() : Функция 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
В этом руководстве подробно объясняется, как разбить текстовую строку по определенному символу в ячейке с формулами.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Разбить число на отдельные цифры в Excel
В этом руководстве объясняется, как использовать комбинацию функций MID и COLUMN для разделения определенного числа в ячейке на отдельные цифры в Excel.
Как разбить число на отдельные цифры в Excel?
Как показано на скриншоте ниже, вы хотите разделить число в каждой ячейке на отдельные цифры. Пожалуйста, сделайте следующее, чтобы это сделать.
Общая формула
аргументы
$ B3: Представляет ячейку, содержащую число, которое вы хотите разделить на отдельные цифры;
$ D3: Представляет ячейку для вывода первой цифры числа.
Как пользоваться этой формулой?
1. Выберите пустую ячейку для вывода первой цифры. В данном случае я выбираю D3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем была извлечена первая цифра числа в ячейке B3.
=MID($B3,COLUMN()-(COLUMN($D3)- 1),1)
3. Выберите ячейку результата, а затем перетащите ее маркер заполнения вправо, чтобы применить формулу к правым ячейкам, пока не будут извлечены все цифры числа в B3.
4. Не снимая выделения с указанных выше ячеек, перетащите маркер заполнения вниз, чтобы получить все цифры других ячеек в столбце B. См. Снимок экрана.
Как работает эта формула?
=MID($B3,COLUMN()-(COLUMN($D3)- 1),1)
1. COLUMN() : В этом случае я выбираю ячейку D3 для ввода формулы, поэтому функция COLUMN возвращает 4, поскольку столбец D является четвертым столбцом на листе;
2. COLUMN($D3) : Здесь функция COLUMN возвращает номер столбца ссылки $ D3. Здесь результат 4;
3. MID(12345,4-(4- 1),1) : Функция MID извлекает одну цифру из числа «12345», начиная с первой цифры.
Связанные функции
Функция Excel MID
Функция Excel MID возвращает определенное количество символов из текстовой строки, начиная с указанной вами позиции, в зависимости от количества указанных вами символов.
СТОЛБЕЦ в Excel
Функция СТОЛБЕЦ Excel возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки.
Родственные формулы
Разделение размеров на две части в Excel
В этой статье объясняется, как использовать формулу для разделения размеров в ячейке на две части без единиц измерения (индивидуальная длина и ширина).
Отделяйте числа от единиц измерения
В этой статье объясняется, как использовать формулу для отделения чисел от единиц измерения.
Разделить текст и числа в ячейке в Excel
Если вы хотите разделить текст и числа в ячейке на разные ячейки столбца с формулами, это руководство окажет вам услугу.
Разделить текст с разделителем в ячейке в Excel
В этой статье мы предлагаем пошаговое руководство, которое поможет вам разделить текст в ячейке с помощью определенного разделителя в Excel.
Разделить текст и числа в ячейке в Excel
Предположим, что данные ячейки смешаны с текстом и числами, как вы можете разделить их на отдельные ячейки столбца? Этот туториал покажет вам подробные шаги, чтобы разобраться с формулами.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Читайте также: