Как отделить числа от текста в Excel

Обновлено: 24.11.2024

Чтобы разделить текст и числа, вы можете использовать формулу, основанную на функции НАЙТИ, функции МИН и функции ДЛСТР с функцией ВЛЕВО или ВПРАВО, в зависимости от того, хотите ли вы извлечь текст или число. В показанном примере формула в C5 выглядит так:

который возвращает 7, позицию числа 3 в строке "apples30".

Обзор

Формула выглядит сложной, но на самом деле механизм довольно прост.

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

В этом случае мы предполагаем, что числа и текст объединены и что число появляется после текста. Из исходного текста, который появляется в одной ячейке, вы хотите разделить текст и числа на отдельные ячейки, например:

td>
Исходный Текст Число
Яблоки30 Яблоки 30
персики24 персики 24
апельсины12 апельсины 12
персики0 персики0

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

После того, как у вас есть позиция, чтобы извлечь только текст, используйте:

Чтобы извлечь только число, используйте:

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

Каждое число в этом массиве представляет позицию элемента в константе массива внутри исходного текста.

Далее функция MIN возвращает наименьшее значение в списке, которое соответствует положению первого числа в исходном тексте. По сути, функция НАЙТИ получает все числовые позиции, а МИН. дает нам первую числовую позицию: обратите внимание, что 7 — это наименьшее значение в массиве, которое соответствует позиции числа 3 в исходном тексте.

Возможно, вас интересует странная конструкция для within_text в функции поиска:

Эта часть формулы объединяет все возможные числа от 0 до 9 с исходным текстом в ячейке B5. К сожалению, функция НАЙТИ не возвращает ноль, если значение не найдено, так что это просто умный способ избежать ошибок, которые могут возникнуть, если число не найдено.

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

Если исходный текст не содержит цифр, будет возвращена "фиктивная" позиция, равная длине исходного текста + 1. С этой фиктивной позицией ЛЕВАЯ формула выше все равно вернет текст, а ПРАВАЯ формула вернет пустую строку ("").

Нам часто приходится отделять текст и числа от некоторых данных в Excel. Сегодня я покажу 4 простых способа сделать это удобно.

Загрузить практическую рабочую тетрадь

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

Давайте посмотрим на этот набор данных. У нас есть объединенные данные некоторых учеников школы Saint Xaviers School.

У нас есть два отдельных столбца. Столбцы C и D, где мы хотим отдельно извлечь имена и идентификаторы учащихся.

Как вы можете это сделать?

Вот методы, которым вы можете следовать.

1. Использование меню «Текст в столбец» на панели инструментов Excel

Выберите ячейки, в которых вы хотите разделить текст и числа. Здесь я выбираю ячейки с B4 по B13.

Затем перейдите в меню «Данные»> «Текст в столбцы» на панели инструментов Excel в разделе «Инструменты данных».

Нажмите. Вы получите окно мастера преобразования текста в столбцы. Поскольку мы хотим разделить текст и числа с помощью разделителя (запятой), отметьте параметр «С разделителями».

Вы можете предварительно просмотреть свои данные.

Затем нажмите "Далее".

Затем вы перейдете к шагу 2 мастера преобразования текста в столбцы.

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

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

Теперь вы увидите предварительный просмотр разделенных данных.

Затем нажмите "Далее".

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

В нижней части окна вы видите, что ваши данные разделены на необходимое количество столбцов. В данном случае в два столбца.

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

В этом случае я хочу, чтобы оба столбца были в общем формате.

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

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

Здесь я выбираю значение $C$4.

Затем нажмите "Готово".

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

2. Использование быстрой заливки

Теперь предположим, что в наборе данных у вас нет запятых (,) в комбинированных данных. Вот так.

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

Не волнуйтесь. Лучший способ разделения сейчас — использовать Flash Fill. Вы можете использовать его двумя способами.

Способ 1: с панели инструментов Excel

Сначала разделите первые данные вручную. Здесь я помещаю Стива Морриса в ячейку C4 и 101 в ячейку D4.

Затем выберите остальные ячейки в первом столбце. Я выбираю все ячейки от C4 до C13.

Затем выберите «Данные» > «Быстрое заполнение» на панели инструментов Excel в разделе «Инструменты данных».

Нажмите «Быстрая заливка». Вы найдете тексты из всех ячеек столбца B, красиво расположенные в столбце C.

Проделайте это с остальными столбцами. Я делаю это снова для столбца D.

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

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

Снова разделите первую ячейку вручную. Я поместил Стива Морриса в ячейку C4 и 101 в ячейку D4.

Затем перетащите маркер заполнения первого столбца через остальные ячейки. После перетаскивания вы получите небольшой значок под названием «Параметры автозаполнения» в правом нижнем углу.

Нажмите на связанное с ним раскрывающееся меню. Вы получите четыре варианта. Нажмите "Быстрая заливка".

Вы найдете тексты из всех ячеек столбца B, красиво расположенные в столбце C.

Затем проделайте то же самое с остальными ячейками. Я делаю это снова для столбца D.

Примечание. Функция Fill Flash доступна в Excel 2013. Пользователи предыдущих версий не получат ее.

3. Использование функций Excel

Метод 1: использование комбинации функций ПРАВИЛЬНО(), ВЛЕВО(), МИН() и НАЙТИ()

Это будет полезно, когда у нас есть объединенные данные, не разделенные запятыми (,). Подобно тому, что мы использовали в разделе 2.

Функция ПРАВО()

Синтаксис

  • Он принимает два аргумента, один текст и один номер позиции.
  • Возвращает самую правую часть текста, начиная с заданного номера позиции с конца.

Если вы хотите узнать больше о функции RIGHT(), перейдите по этой ссылке.

Функция ВЛЕВО()

Синтаксис

=LEFT(текст,[num_char])

Он принимает два аргумента, один текст и один номер позиции.

  • Возвращает самую левую часть текста до заданного номера позиции.

Если вы хотите узнать больше о функции LEFT(), перейдите по этой ссылке.

Функция МИН()

Синтаксис

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

Если вы хотите узнать больше о функции MIN(), перейдите по этой ссылке.

Функция НАЙТИ()

Синтаксис

  • Принимает три входных параметра: find_text (может быть отдельным значением или массивом),within_text, start_num.
  • Возвращает позицию, в которой входные данные find_text совпадают внутри текста inside_text.
  • Начинает поиск с позиции start_num.
  • С учетом регистра.

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

Формула для этого будет

Если вы хотите узнать больше о функции НАЙТИ(), перейдите по этой ссылке.

Видите ли, в имени Steve Morris101 числа начинаются с 13-й позиции.

Поэтому формула для разделения Имени будет

Видите, оно удачно разделяет имя Стив Моррис.

Теперь перетащите маркер заполнения, чтобы разделить имена остальных ячеек.

А для студенческих билетов формула будет

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

Примечание. Если в объединенных данных сначала стоят числа, а за ними текст, например 101Стив Моррис, то формула для чисел будет заменена формулой для текста. И вы должны использовать MAX() вместо функции MIN().

Метод 2: использование комбинации функций ПРАВО(), ВЛЕВО() и ПОИСК()

Если объединенные данные разделены запятыми (,), как набор данных, который мы использовали в разделе 1, вы можете использовать этот метод.

Функция ПОИСК()

Синтаксис

  • Принимает три входных параметра: find_text (может быть отдельным значением или массивом),within_text, start_num.
  • Возвращает позицию, в которой входные данные find_text совпадают внутри текста inside_text.
  • Начинает поиск с позиции start_num.

Это почти то же самое, что и функция FIND().

Если вы хотите узнать больше о функции ПОИСК(), перейдите по этой ссылке.

Теперь формула для разделения имен будет

И формула для разделения идентификаторов будет

Примечание. Если у вас сначала числа, а затем текст, например 101Стив Моррис, вам нужно поменять местами формулы.

4. Использование макроса (код VBA)

Теперь мы будем отделять текст и числа от ячейки, используя макрос (код VBA)

  • Сначала нажмите Alt + F11. У вас откроется окно VBA.
  • Затем перейдите к параметру «Вставка» на панели инструментов VBA. Нажмите на нее. Вы получите несколько вариантов. Выберите модуль.

  • Нажмите «Модуль». Вы получите новое окно модуля.

Этот сайт помог нам создать и развить код.

  • Этот код создает новую функцию SplitText(), которая принимает два аргумента: объединенные данные и логическое значение (ИСТИНА или ЛОЖЬ).
  • Затем сохраните его как тип «Excel Macro Enabled Worksheet».
  • Теперь вернитесь к своему рабочему листу. В столбец Имя учащегося вставьте формулу

Имена будут разделены.

  • И в столбце ID учащегося вставьте формулу.

Идентификаторы учащихся будут разделены.

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

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

Например, он может отделить 101 и Стива Морриса от Стива10 М1орриса, чего не могут сделать другие методы. Посмотрите на изображения ниже.

Заключение

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

Дополнительная литература

Рифат Хассан

Здравствуйте! Добро пожаловать в мой профиль. Здесь я буду публиковать статьи, связанные с Microsoft Excel. Я страстный инженер-электрик со степенью бакалавра в области электротехники и электронной техники Бангладешского инженерно-технологического университета. Помимо академических занятий, я всегда люблю идти в ногу с революцией в технологиях, к которой мир стремится изо дня в день. Я прилежна, ориентирована на карьеру и готова дорожить знаниями на протяжении всей жизни.

Как разделить ячейку в Excel? С помощью функции «Текст в столбцы», «Быстрая заливка», формул или инструмента «Разделить текст». В этом руководстве описаны все варианты, которые помогут вам выбрать технику, наиболее подходящую для вашей конкретной задачи.

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

Как разделить ячейки в Excel с помощью преобразования текста в столбцы

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

Как разделить ячейки в Excel с помощью разделителя

Предположим, у вас есть список участников, в котором имя участника, страна и ожидаемая дата прибытия находятся в одном столбце:

Мы хотим разделить данные в одной ячейке на несколько ячеек, таких как Имя, Фамилия, Страна, Дата прибытия и Статус. Для этого выполните следующие действия:

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

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

  • Рассматривать последовательные разделители как один. Обязательно выберите этот параметр, если ваши данные могут содержать два или более разделителя подряд, например. когда между словами есть несколько последовательных пробелов или данные разделены запятой и пробелом, например "Смит, Джон".
  • Указание квалификатора текста. Используйте этот параметр, если некоторый текст заключен в одинарные или двойные кавычки, и вы хотите, чтобы такие части текста были неразделимы. Например, если вы выберете запятую (,) в качестве разделителя и кавычки (") в качестве квалификатора текста, тогда любые слова, заключенные в двойные кавычки, например "Калифорния, США", будут поместить в одну ячейку как Калифорния, США. Если вы выберете в качестве квалификатора текста, то "Калифорния будет распределено в одну ячейку (вместе с открывающей кавычкой ) и США" в другую (вместе с закрывающим знаком).
  • Предварительный просмотр данных. Прежде чем нажимать кнопку Далее, имеет смысл прокрутить раздел Предварительный просмотр данных, чтобы убедиться, что Excel правильно разделил все содержимое ячеек.

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

  • Если вы не хотите импортировать какой-либо столбец, отображаемый в предварительном просмотре данных, выберите этот столбец и установите переключатель Не импортировать столбец (пропускать) в разделе Формат данных столбца.
  • Невозможно импортировать разделенные данные в другую таблицу или книгу. Если вы попытаетесь сделать это, вы получите сообщение об ошибке недопустимого адресата.

Как разделить текст фиксированной ширины

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

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

Поскольку все идентификаторы продуктов содержат 9 символов, вариант с фиксированной шириной идеально подходит для этой работы:

  1. Запустите мастер Преобразовать текст в столбцы, как описано в приведенном выше примере. На первом шаге мастера выберите «Фиксированная ширина» и нажмите Далее.
  2. Установите ширину каждого столбца в разделе предварительного просмотра данных. Как показано на снимке экрана ниже, вертикальная линия представляет собой разрыв столбца, и для создания новой строки разрыва достаточно щелкнуть в нужном месте (в нашем случае 9 символов):

    Чтобы удалить разрыв , дважды щелкните строку; чтобы переместить разрыв в другое место, просто перетащите линию мышью.
  3. На следующем шаге выберите формат данных и место назначения для разделенных ячеек точно так же, как в предыдущем примере, и нажмите кнопку Готово, чтобы завершить разделение.

    4. Как разделить ячейки Excel с помощью Fill Flash

    Начиная с Excel 2013, вы можете воспользоваться функцией мгновенного заполнения, которая может не только автоматически заполнять ячейки данными, но и разделять содержимое ячеек.

    Возьмем столбец данных из нашего первого примера и посмотрим, как Flash Fill в Excel может помочь нам разделить ячейку пополам:

    1. Вставьте новый столбец рядом со столбцом с исходными данными и введите нужную часть текста в первую ячейку (в данном примере имя участника).
    2. Введите текст еще в пару ячеек. Как только Excel обнаружит шаблон, он автоматически заполнит аналогичные данные в других ячейках. В нашем случае Excel потребовалось 3 ячейки, чтобы вычислить шаблон:
    3. Если вас устраивает то, что вы видите, нажмите клавишу Ввод, и все имена будут сразу скопированы в отдельный столбец.

      4. Как разделить ячейку в Excel с помощью формул

      Какую бы разнообразную информацию ни содержали ваши ячейки, формула разделения ячейки в Excel сводится к поиску положения разделителя (запятая, пробел и т. д.) и извлечению подстроки до, после или между разделителями. Как правило, для определения положения разделителя используются функции ПОИСК или НАЙТИ, а для получения подстроки — одну из текстовых функций (ЛЕВАЯ, ПРАВАЯ или СРЕДНЯЯ).

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

      Чтобы извлечь имя из B2:
      =LEFT(A2, SEARCH(",",A2)-1)

      Чтобы извлечь страну из C2:
      =RIGHT(A2,LEN(A2)-SEARCH(",",A2)-1)

      Подробное объяснение логики и другие примеры формул для разделения ячеек в Excel можно найти на следующих ресурсах:

      Разделить ячейки с помощью функции «Разделить текст»

      Теперь, когда вы знакомы со встроенными функциями, позвольте мне показать вам альтернативный способ разделения ячеек в Excel. Я имею в виду инструмент «Разделить текст», входящий в состав Ultimate Suite for Excel. Он может выполнять следующие операции:

      • Разделить ячейку по символу
      • Разделить ячейку по строке
      • Разделить ячейку по маске (шаблону)

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

      1. Выберите ячейки, которые хотите разделить, и нажмите значок "Разделить текст" на вкладке Данные Ablebits в разделе Текст
      2. На панели Разделить текст настройте следующие параметры:
        • В разделе Разделить по символу выберите в качестве разделителей запятую и пробел.
        • Установите флажок Рассматривать последовательные разделители как один.
        • В разделе "Выбор способа разделения ячеек" выберите "Разделить на столбцы".
        • Нажмите кнопку "Разделить".

      Готово! Четыре новых столбца с разделенными данными вставляются между исходными столбцами, и вам нужно только дать этим столбцам соответствующие имена:

      • Инструмент «Разделить текст» предоставляет еще два параметра для разделения ячейки в Excel. Дополнительные сведения см. в разделах Разделение ячеек по строке и Разделение ячеек по маске.
      • Чтобы разделить столбец имен на имя, фамилию и отчество, используйте специальный инструмент "Разделить имена".

      Если вам интересно увидеть инструменты «Разделить текст» и «Разделить имена» в действии, мы приглашаем вас загрузить 14-дневную пробную версию нашего Ultimate Suite для Excel.
      Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

      В этом примере вы хотите разделить имена и числа из столбца A на столбцы B и C. Это можно сделать тремя способами.

      Вариант 1. Быстрое заполнение

      Мгновенная заливка — это функция, представленная в Excel 2013. Если у вас более старая версия или версия для Mac, вы не сможете ее использовать.

      Мгновенная заливка делает это чрезвычайно простым, и это почти всегда лучший выбор для подобных задач.

      Чтобы использовать Flash Fill, вам сначала нужно предоставить ему пример того, что вы пытаетесь сделать, как показано выше. Предоставив пример, нажмите:
      Главная > Редактирование > Заливка > Мгновенная заливка

      Для этого также можно использовать сочетание клавиш +.

      Flash Fill просматривает ваш пример, выясняет, что вы пытаетесь сделать, и автоматически извлекает все остальные имена.

      Вы можете повторить тот же процесс для столбца C, и все имена и номера будут извлечены!

      Мгновенная заливка более подробно описана в нашем курсе "Основные навыки" наряду с некоторыми более сложными методами мгновенной заливки.

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

      Вариант 2. Текст в столбцы

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

      В этом случае Текст в столбцы можно использовать для разделения данных, используя пробел в качестве разделителя. Это приведет к 3 столбцам, а не к двум, которые вы хотите, поэтому вам нужно будет затем объединить имена вместе.

      Вы можете получить доступ к инструменту Текст в столбцы, щелкнув: Данные > Работа с данными > Текст в столбцы

      Использование Text to Columns подробно рассматривается в нашем курсе Expert Skills.

      Вариант 3. Использование формул

      Предыдущие варианты предлагают только одноразовое решение. Если бы данные в столбце A поступали из внешнего источника данных, вам не нужно было бы повторно запускать Flash Fill или Text to Columns каждый раз, когда вы обновляете данные.

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

      Поиск позиции первого числа

      Первое, что нужно сделать вашей формуле, — найти позицию первого числа в ячейке. Формула для этого:

      Это выглядит устрашающе, но на самом деле это не так уж сложно понять, если вы разбираетесь в функции НАЙТИ. Программа FIND подробно описана в нашем курсе "Экспертные навыки".

      Числа в фигурных скобках также могут сбивать с толку. Это называется «массивом». Это означает, что функция НАЙТИ будет выполняться 10 раз, по одному разу для каждого числа. Затем функция MIN вернет наименьшее возвращаемое значение (т. е. позицию первого числа в ячейке).

      Причина, по которой вы объединяете текст "0123456789", заключается в том, что функция НАЙТИ вернет ошибку, если не найдет то, что ищет, и это предотвратит работу функции МИН. способный вернуть результат. Чтобы убедиться, что FIND всегда может возвращать правильный результат при каждом запуске, вы добавляете все числа в конец искомого текста.

      Извлечение имен

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

      =ЛЕВОЕ(A2, МИН(НАЙТИ(,A2&"0123456789″)) -1)

      Функция LEFT подробно описана в нашем курсе Expert Skills.

      Извлечение чисел

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

      =ВПРАВО(A2,ДЛСТР(A2)-МИН(НАЙТИ(,A2&"0123456789″)) +1)

      Как и в случае с ЛЕВОЙ функцией, функции ПРАВАЯ и ДЛСТРИЯ рассматриваются в нашем курсе повышения квалификации.

      Результат

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

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

      Это единственные современные книги Excel, изданные в настоящее время и включающие новые функции динамических массивов.

      Это также единственные книги, которые научат вас абсолютно всем навыкам работы с Excel, включая Power Pivot, OLAP и DAX.

      Некоторые вещи, которые вы узнаете

      Поделиться этой статьей

      Последние статьи

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

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

      Сочетание клавиш для автозаполнения в Excel

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

      Формула Excel для проверки наличия в ячейке текста

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

      12 ответов

      Исходя из приведенного выше совета, он не может разделять текст и числа, когда числа находятся перед текстом, например. 12345abcde. Есть еще советы?

      Все приведенные выше решения должны работать с цифрами перед текстом.

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

      НА МЕНЯ ЭТО НА САМОМ ДЕЛЕ НЕ РАБОТАЕТ.

      Если вы можете уточнить, что вы пытаетесь сделать и почему это не работает, я смогу дать вам совет.

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

      Это распространенная проблема на веб-сайтах: статьи написаны с помощью программного обеспечения для обработки текстов, которое заменяет двойные кавычки, набираемые с клавиатуры, более удобными (в Word это называется использованием умных кавычек), предназначенных для публикации. А если этого не происходит, или корректно исправлено потом, то любая обработка при загрузке на сайт тоже может это делать.

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

      Если два кода имеют одинаковую длину, вы сможете легко сделать это с помощью Flash Fill или Text To Columns, как упоминалось выше. Если вам нужно решение на основе формулы, а коды всегда имеют одинаковую длину, вы также сможете легко сделать это, используя функции ВЛЕВО и ВПРАВО. Только в тех случаях, когда коды могут иметь переменную длину, вам понадобится формула, которая ищет позицию последней цифры или первой буквы кода.

      Решение MAX будет работать правильно только в том случае, если все числа от 0 до 9 могут быть найдены в цели, поэтому на самом деле это может быть не лучший подход в подобной ситуации. Существуют формулы, которые могут надежно извлекать последнее число из значения без какого-либо предварительного знания того, какие числа могут присутствовать, но они намного сложнее.

      Более простым решением в этом случае было бы найти первую букву вместо последней цифры.Предполагая, что значение находится в ячейке A2, вы можете сделать это, используя:
      =MIN(FIND(,A2&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”))-1

      Это работает точно так же, как приведенная выше формула, но функция НАЙТИ выполняется 26 раз (по одному разу для каждой буквы алфавита) и возвращает наименьший результат. Затем вы можете извлечь значения с каждой стороны, используя функции ВЛЕВО, ВПРАВО и ДЛСТР.

      Теперь, когда вы извлекли обе части значения, вы можете соединить их вместе с пробелом между ними, используя оператор &, например:
      =B2&” “&C2

      Не могли бы вы помочь найти наибольшее и наименьшее значение из этого
      пожалуйста, вставьте приведенные ниже данные в одну ячейку (пример A1), и вам нужно найти наибольшее значение

      Ячейка: A1
      a)63Ra b)64Ra c)65Ra d)62Ra e)61Ra f)63Ra g)60Ra h)62Ra

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

      На наших курсах подробно рассматриваются оба навыка.

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

      Большое спасибо

      =MIN(FIND(,A2&”0123456789″))
      мне ничего не помогает
      я пытался заменить ” и ; и т.д. даже набрал вручную новый, а он не работает.
      в строке всегда просто написано
      =MIN(FIND(,A2&”0123456789″))
      вместо отображения результата.
      Есть мысли, как это исправить?

      Я только что проверил это, и у меня это отлично сработало. С текстом «Джордж Барнс 197,2″ в ячейке A2 формула: =MIN(НАЙТИ(,A2&”0123456789”)) вернула 15, как и ожидалось. Если вы копируете и вставляете с веб-страницы, вам придется вручную повторно ввести двойные кавычки (кавычки веб-страницы отличаются от кавычек Excel), но вы сказали, что сделали это.

      Наверное, проблема в версии Excel. Было огромное количество версий Excel, и в некоторых есть ошибки, которых нет в других, в некоторых также есть функции, которых нет у других. Все, что я могу сообщить, это то, что он отлично работал с Excel 365 версии 2103 Build 13901.20462, которая была последней версией Excel на момент написания. Сказав это, я также ожидаю, что он будет работать во всех других версиях Excel, но ваш опыт говорит об обратном!

      Читайте также: