Как разбить число на цифры в Excel
Обновлено: 21.11.2024
Иногда нам может понадобиться отделить или получить только числа из любого комбинированного ввода текста и чисел. Не делая этого вручную, Excel предоставляет различные способы отделения чисел из любого набора данных. В этой статье я покажу несколько способов разделения чисел в Excel с помощью формул в разных случаях.
Загрузить рабочую тетрадь
5 способов разделения чисел в Excel с помощью формулы
1. Отделяйте числа от текста с помощью функций
Предположим, у нас есть набор данных с названиями офисов MS. Мы знаем, что для каждой версии MS Office есть год или номер, чтобы указать версию Office, например MS Office 2019. Теперь я покажу, как получить только числа из заданного набора данных с помощью формулы. В нашей формуле нам понадобятся функции ПРАВИЛЬНО, ДЛСТР, МИН, НАЙТИ. Давайте сначала поговорим об этих функциях.
Это одна из наиболее часто используемых функций для поиска чего-либо в Excel. В первой части параметра функции нам нужно передать значение или текст, который мы хотим узнать. Затем, во-вторых, нам нужно передать диапазон, в котором мы будем искать значение. Наконец, мы можем указать начальный номер или значение, которое является необязательным.
Эта функция используется для определения минимального значения любого заданного числового значения. Мы можем передать любое число в параметр этой функции, тогда она вернет минимальное значение из заданных нами чисел.
Другой функцией является LEN, которая принимает любой текст в качестве параметра и подсчитывает символы в данном входном тексте, а затем возвращает количество символов.
Эта функция возвращает последний символ или символы в текстовой строке в зависимости от количества символов из указанного нами входного текста. Текстовая строка содержит символы, которые мы хотим извлечь, что является необязательным.
Это то же самое, что и функция ВПРАВО. Все параметры required value также одинаковы, но здесь вместо возврата последнего символа возвращается первый символ вводимого текста.
Когда нам нужно заменить любой текст новым текстом, эта функция помогает в этом. Первый раздел принимает текстовое значение, которое будет размещено. Затем нам нужно передать текущий текст, который будет заменен. Третья часть берет текст для замены. Наконец, экземпляр для замены. Если он не указан, заменяются все экземпляры, что необязательно.
Итак, наш данный набор данных будет таким:
Шаг 1. Введите приведенную ниже формулу в ячейку C4.
Разбивка формулы
Поскольку это немного сложная формула, для лучшего понимания давайте сначала поговорим о внутренней функции
Используя эту часть, мы ищем первый порядковый номер чисел. Перед извлечением числа сначала нам нужно узнать начальное местоположение чисел.
После нахождения позиции начального числа с помощью функций RIGHT и LEN мы извлекаем числа из заданного текста.
Шаг 2. Скопируйте формулу до C13.
В этом примере числа были справа от введенного текста. Теперь давайте посмотрим, находится ли вводимый текст слева. Для этого рассмотрим набор данных следующим образом:
Шаг 1. Введите формулу в первую ячейку и скопируйте формулу вниз.
Разбивка формулы
Для получения чисел правой части заданного текста нам нужны четыре функции: ЛЕВЫЙ, СУММ, ДЛСТР, ПОДСТАВИТЬ.
Используя эту часть, мы просто узнаем число из заданного текста.
Затем, используя функцию LEFT, мы извлекаем общую длину числа и общее число из левой части нашего текста.
2. Отделение чисел от текста с помощью быстрой заливки
Предполагая, что у нас есть набор данных о каком-то человеке, где его идентификатор прикреплен к их именам в одном столбце.Теперь наша задача состоит в том, чтобы отделить номера идентификаторов от этого столбца и показать их в другом столбце с именем идентификатор.
[ Примечание. Это автоматическое мгновенное заполнение доступно только в более новых версиях Excel-подобных версий 2016 или 2019. ]
Шаг 1. Сначала введите числа из ячейки B4 в ячейку C4.
Шаг 2. Перейдите к ячейке ниже и попробуйте ввести количество ячеек B5, после чего автоматически будут показаны все числа. Просто нажмите Enter.
Шаг 3. Все номера будут заполнены автоматически.
Шаг 4. Этот процесс также применим, если цифры справа.
3. Отделение десятичных чисел от текста в Excel
Мы можем использовать ту же формулу для десятичных чисел, что и в первом методе. Давайте попробуем это сделать, используя тот же пример и формулу, но здесь вместо целых чисел мы также будем использовать десятичные числа.
Шаг 1. Измените числа, которые использовались в предыдущем методе, и просмотрите изменения.
Из рисунка выше видно, что с помощью этой формулы:
Мы можем извлекать не только целые значения, но и десятичные значения из любой входной строки.
4. Разделить номер с помощью разделителей
Предположим, что у нас есть набор данных с именем и идентификатором какого-либо человека в одном столбце. Формирование этого столбца - это идентификатор имени. Теперь я покажу, как извлечь идентификатор из этого столбца и отправить его в другой столбец.
Шаг 1. Выберите столбец и перейдите на вкладку "Данные". Затем выберите раздел «Текст в столбцы».
Шаг 2: Затем откроется окно. Выберите параметр «С разделителями», затем нажмите «Далее».
Шаг 3. Убедитесь, что нажат пробел, затем нажмите кнопку "Далее". Вы также можете увидеть, как Data Review знает, как ваши данные будут выглядеть после нажатия кнопки «Далее».
Шаг 4. В этом разделе вы можете изменить формат данных нового столбца. После этого нажмите Готово.
Шаг 5. После этого все данные будут отображаться в соответствии с нашими требованиями.
5. Отделение чисел от текста с помощью определяемой пользователем функции в VBA (наиболее универсальный метод)
В Excel мы можем легко изменять функции или создавать собственные функции в соответствии с нашими потребностями, используя код VBA.В этом методе я покажу, как отделить любые числа от заданного входного текста с помощью нашей собственной определяемой пользователем функции с использованием кода VBA. Для этого метода предположим, что у нас есть набор данных из некоторой входной строки.
Шаг 1. Сначала перейдите к параметру "Разработчик", затем нажмите Visual Basic или используйте сочетание клавиш Alt + F11.
Шаг 2. В окне Microsoft Visual Basic для приложений сначала щелкните параметр «Вставить», затем выберите «Модуль».
Шаг 3. Введите следующий код в модуль.
Код
Пояснение кода:
Это будет слишком долго, если мы хотим правильно объяснить каждую строку. Но здесь я объясню главное в кодах.
- 1. В первой строке мы объявляем функцию SplitNumAndText, и она будет иметь два аргумента в параметре, один из которых является строковым типом, а другой — логическим типом.
- Здесь мы будем перебирать всю строку, используя цикл For.
- Используя условие ЕСЛИ, мы проверяем, является ли значение числом или нет.
- Опять же, используя условие, он выдаст вывод на основе числа или текста. Если мы передадим 0 в качестве второго аргумента, он извлечет текст, а если 1, он извлечет число из данной строки.
Шаг 4. Перейдите на рабочий лист и введите приведенную ниже формулу в ячейку C4.
[ Примечание: поскольку мы хотим напечатать все числа, поэтому 2-й аргумент равен 1. Если вы хотите извлечь, просто введите 0 во 2-й аргумент. ]
Шаг 5. Скопируйте формулу до C10.
Заключение
Это способы отделения чисел от любой строки в Excel. Я показал все методы с соответствующими примерами. Кроме того, я обсудил основы этой функции и наиболее часто используемые коды формата этой функции. Если у вас есть другой способ добиться этого, поделитесь им с нами.
Чтобы разделить текст и числа, вы можете использовать формулу, основанную на функции НАЙТИ, функции МИН и функции ДЛСТР с функцией ВЛЕВО или ВПРАВО, в зависимости от того, хотите ли вы извлечь текст или число. В показанном примере формула в C5 выглядит так:
который возвращает 7, позицию числа 3 в строке "apples30".
Обзор
Формула выглядит сложной, но на самом деле механизм довольно прост.
Как и в большинстве формул, которые разбивают или извлекают текст, ключевым моментом является определение позиции того, что вы ищете. Получив позицию, вы можете использовать другие функции, чтобы извлечь то, что вам нужно.
В этом случае мы предполагаем, что числа и текст объединены и что число появляется после текста. Из исходного текста, который появляется в одной ячейке, вы хотите разделить текст и числа на отдельные ячейки, например:
Исходный Текст Число Яблоки30 Яблоки 30 персики24 персики 24 апельсины12 апельсины 12 персики0 персики td>0 Как указано выше, в этом случае ключом является определение начальной позиции числа, что можно сделать с помощью следующей формулы:
После того, как у вас есть позиция, чтобы извлечь только текст, используйте:
Чтобы извлечь только число, используйте:
В первой приведенной выше формуле мы используем функцию НАЙТИ, чтобы найти начальную позицию числа. Для find_text мы используем константу массива, это заставляет функцию НАЙТИ выполнять отдельный поиск для каждого значения в константе массива. Так как константа массива содержит 10 чисел, результатом будет массив с 10 значениями. Например, если исходный текст — «яблоки30», результирующий массив будет таким:
Каждое число в этом массиве представляет позицию элемента в константе массива внутри исходного текста.
Далее функция MIN возвращает наименьшее значение в списке, которое соответствует положению первого числа в исходном тексте. По сути, функция НАЙТИ получает все числовые позиции, а МИН. дает нам первую числовую позицию: обратите внимание, что 7 — это наименьшее значение в массиве, которое соответствует позиции числа 3 в исходном тексте.
Возможно, вас интересует странная конструкция для within_text в функции поиска:
Эта часть формулы объединяет все возможные числа от 0 до 9 с исходным текстом в ячейке B5. К сожалению, функция НАЙТИ не возвращает ноль, если значение не найдено, так что это просто умный способ избежать ошибок, которые могут возникнуть, если число не найдено.
В этом примере, поскольку мы предполагаем, что число всегда будет вторым в исходном тексте, это работает хорошо, потому что MIN использует только наименьшее или первое вхождение. , числа, которое нужно вернуть. Если число есть в исходном тексте, будет возвращена эта позиция.
Если исходный текст не содержит цифр, будет возвращена "фиктивная" позиция, равная длине исходного текста + 1. С этой фиктивной позицией ЛЕВАЯ формула выше все равно вернет текст, а ПРАВАЯ формула вернет пустую строку ("").
Что можно сделать, если вы хотите разделить число, состоящее из нескольких цифр, на каждую отдельную цифру в каждом столбце? Я думаю, вы можете использовать комбинацию функций MID и COLUMN для решения вашей проблемы. См. способ ниже в этой статье.
Давайте сначала изучим две функции.
Формула = MID(text, start_num, num_chars) .
Формула = СТОЛБЦ(ссылка)
COLUMN() без какой-либо ссылки возвращает номер текущего столбца для выбранной ячейки. См. пример ниже.
Тогда мы можем начать изучать, как мы можем разделить число на каждую отдельную цифру.
Убедитесь, что мы получили возвращаемое значение 2. Это потому, что COLUMN() возвращает номер текущего столбца, а выбранная ячейка – B2, поэтому функция MID равна =MID(A2,2,1) , поэтому возвращаемое значение равно 2.
Примечание: если мы по-прежнему используем формулу =MID(A2,COLUMN()-1,1) без $ перед A2, в C2 формула изменится на =MID(B2,COLUMN()-1,1) неправильно.
Если вы часто пользуетесь Microsoft Excel, возможно, вы сталкивались с ситуациями, когда вам нужно было добавить или подсчитать ячейки с учетом регистра. Если вы выполняли эту задачу вручную, я хотел бы отметить .
Среднее значение – это полезная функция Excel, позволяющая быстро вычислить среднее значение последних N значений в столбце.Тем не менее, вам может потребоваться время от времени вставлять новые цифры под вашей исходной информацией, и .
В этом уроке показано, как использовать функцию ЕСЛИ в Excel для распределения баллов на основе количества прошедшего времени. Если вы намерены следовать этому руководству, вы можете сделать это, загрузив .
Предположим, что у вас есть список дат с разными форматами дат, как показано на прилагаемом рисунке. В этом случае функция сортировки Excel не сможет их правильно отсортировать. Однако вы можете преобразовать все различные форматы даты в определенный файл .
В статье показано, как проверить, является ли диапазон ячеек пустым или пустым, с помощью ISBLANK и других функций, чтобы распознавать пустые ячейки в Excel и предпринимать соответствующие действия в зависимости от их состояния. Есть несколько обстоятельств, при которых .
В Excel вы можете вычислить площадь параллелограмма, используя обычную формулу и оператор умножения. Чтобы получить площадь круга, используйте функцию PI и оператор экспоненты (). В Excel стандартный набор.
В этой публикации показано, как определить будущую стоимость инвестиций с помощью формулы годового графика начисления сложных процентов в Google Таблицах, а также приведены примеры того, как это сделать с использованием годовых, ежемесячных или ежедневных процентных ставок. Кроме того, вы будете .
В этой публикации показано, как определить будущую стоимость инвестиций с помощью формулы годового графика сложных процентов в Excel, а также приведены примеры того, как это сделать с использованием годовых, ежемесячных или ежедневных процентных ставок. Кроме того, вы получите .
Финансовый выбор играет решающую роль в разработке и реализации корпоративных стратегий и планов. В повседневной жизни мы также сталкиваемся с множеством финансовых решений. Например, предположим, что вы подаете заявку на получение кредита на покупку нового автомобиля. .
Мы можем легко разделить текст и числа из ячейки, используя различные комбинации функций, таких как НАЙТИ, МИН, ДЛСТР, ВЛЕВО и ПРАВО. В приведенных ниже шагах описан процесс разделения текста и чисел, содержащихся в ячейке.
Рисунок 1. Разделение текста и чисел с помощью формулы
Настройка данных
Мы настроим данные, вставив имена пяти студентов колледжа в столбец A, включая дату их рождения, не оставляя пробела между именем и датой рождения.
Мы также введем должность, имя и дату рождения в ячейки B3, ячейки C3 и ячейки D3 соответственно.
Рисунок 2. Настройка данных
Основная формула
- Мы можем использовать базовую формулу для разделения текста и чисел из ячейки в соответствии с функцией ВЛЕВО или ВПРАВО в соответствии с требованиями.
Определение положения ПЕРВОГО ЧИСЛА в ячейках A4 и A8
- Мы вставим приведенную ниже формулу в ячейку B4.
Рисунок 3: Позиция первого числа
Рисунок 4: Позиция первого числа
- Как показано на рис. 4, эта формула возвращает позицию первого числа в ячейке A4. JOHN состоит из четырех букв, следующее значение — не текст, а число, следовательно, позиция первого числа — 5
- Мы будем использовать функцию раскрывающегося списка, чтобы получить позиции первого числа для оставшихся четырех учащихся.
Рисунок 5: Позиция первого числа
Извлечение текста с помощью функции LEFT
- Мы будем использовать приведенную ниже формулу для извлечения текста (имен) из столбца A:
- Мы вставим эту формулу в ячейку C4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.
Рис. 6. Разделение текста и чисел с помощью функции ВЛЕВО
Извлечение чисел с помощью функции ПРАВИЛЬНО
- Мы будем использовать приведенную ниже формулу для извлечения чисел (даты рождения) из столбца А:
=ВПРАВО(A4,ДЛСТР(A4)-B4+1)
- Мы вставим эту формулу в ячейку D4 и воспользуемся функцией раскрывающегося списка, чтобы заполнить оставшиеся ячейки.
Рис. 7. Разделение текста и чисел с помощью функции RIGHT
Мгновенное подключение к эксперту через нашу службу Excelchat
В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.
Читайте также: