Как разбить в экселе текст по строкам: Разделение текста по столбцам с помощью функций

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

Часто приходится оптимизировать структуру данных после импорта в Excel. Некоторые разные значения попадают в одну и туже ячейку образуя целую строку как одно значение. Возникает вопрос: как разбить строку на ячейки в Excel. Программа располагает разными поисковыми функциями: одни ищут по ячейках другие ищут по содержимому ячеек. Ведь выполнять поиск по текстовой строке, которая содержится в ячейке ¬– это также распространенная потребность пользователей Excel. Их мы и будем использовать для разделения строк.

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

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

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

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

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



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

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

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

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

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

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

Как в Excel разбить текст по столбцам




Перейти к содержанию





Search for:

Главная » Уроки MS Excel







Автор Елизавета КМ На чтение 7 мин Опубликовано



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

Содержание

  1. Необходимо разделить ФИО по отдельным столбцам
  2. Разделение текста с помощью формулы
  3. Этап №1. Переносим фамилии
  4. Этап №2. Переносим имена
  5. Этап №3. Ставим Отчество
  6. Заключение

Необходимо разделить ФИО по отдельным столбцам

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

  1. Открываем документ с допущенной ранее ошибкой.
  2. Выделяем текст, зажав ЛКМ и растянув выделение до крайней нижней ячейки.

1

  1. В верхней ленте находим «Данные» — переходим.

2

  1. После открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и переходим в следующее диалоговое окно.

3

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

4

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

  1. Затем нужно определить формат данных столбца. По умолчанию установлено «Общий». Для нашей информации этот формат наиболее уместен.
  2. В таблице выбираем ячейку, куда будет помещаться отформатированный текст. Отступим от исходного текста один столбец и пропишем соответствующий адресат в адресации ячейки. По окончанию нажимаем «Готово».

5

Замечание эксперта! Размещенный отформатированный текст из-за разного количества символов в ФИО может не вмещаться в выбранные ячейки, поэтому полученная таблица нуждается в корректировке. Для этого используется расширение размеров ячейки.

6

Разделение текста с помощью формулы

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

Этап №1. Переносим фамилии

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

  1. Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.

7

  1. Выберите ячейку, где будет записываться информация о фамилии сотрудника. Активируйте ее нажатием ЛКМ.

8

  1. Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
  2. Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».

9

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

10

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

11

  1. Соглашаемся и кликаем по кнопке «ОК».

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

Этап №2. Переносим имена

Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.

  1. В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.

12

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

13

  1. В строке «Искомый текст» указываем пробел.
  2. Кликнув по «Текст для поиска», активируем ячейку.

14

  1. Возвращаемся к формуле =ПСТР в верхней части экрана.
  2. В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.

15

  1. Переходим к определению количества знаков – вписываем формулу ПОИСК().
  2. Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
  3. Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
  4. Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
  5. Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.

16

Этап №3. Ставим Отчество

  1. Активировав ячейку и перейдя в аргументы функции, выбираем формулу ПРАВСИМВ. Жмем «ОК».

17

  1. В поле «Текст» вписываем адресацию редактируемой ячейки.
  2. Там, где необходимо указать число знаков, пишем ДЛСТР(A2).

18

Примечание эксперта! Формула определит автоматически количество символов.

  1. Для точного определения количества знаков в конце необходимо написать: -ПОИСК().
  2. Перейдите к редактированию формулы. В «Искомый текст» укажите пробел. В «Текст для поиска» — адресацию ячейки. В «Нач.позиция» вставьте формулу ПОИСК(). Редактируйте формулу, установив те же самые значения.
  3. Перейдите к предыдущему ПОИСК и строке «Нач.позиция» допишите +1.

19

  1. Перейдите к формуле ПРАВСИМВ и убедитесь, что все действия произведены правильно.

20

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:


Excel как разбить текст по столбцам как разделить текст по двум столбцам как распределить текст по двум столбцам распределение текста по двум столбцам столбцы Эксель













Adblock
detector

функция TEXTSPLIT — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Дополнительно. ..Меньше

Windows: 2208 (сборка 15601)
Mac: 16,65 (сборка 220911)
Интернет: введено 15 сентября 2022 г.
.
iOS: 2.65 (сборка 220905)
Андроид: 16.0.15629

Разбивает текстовые строки, используя разделители столбцов и строк.

Функция РАЗДЕЛИТЬ ТЕКСТ работает так же, как мастер преобразования текста в столбцы, но в форме формулы. Это позволяет разбивать данные по столбцам или по строкам. Это обратная функция TEXTJOIN.

Синтаксис

=TEXTSPLIT(текст,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

Аргументы синтаксиса функции TEXTSPLIT следующие:

  • text        Текст, который вы хотите разделить. Необходимый.

  • org/ListItem»>

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

  • row_delimiter        Текст, обозначающий точку, в которой текст переносится вниз по строкам. Необязательный.

  • ignore_empty        Укажите TRUE, чтобы игнорировать последовательные разделители. По умолчанию FALSE, что создает пустую ячейку. Необязательный.

  • match_mode     Укажите 1, чтобы выполнить поиск без учета регистра. По умолчанию 0, что означает совпадение с учетом регистра. Необязательный.

  • pad_with            Значение, которым следует дополнить результат. По умолчанию #Н/Д.

Примечания

Если имеется более одного разделителя, необходимо использовать константу массива. Например, чтобы разделить текст с помощью запятой и точки, используйте =TEXTSPLIT(A1,{«,»,».»}).

Примеры

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

Разделите имя и предложение, используя общий разделитель.

Данные

Дакота Леннон Санчес

Быть или не быть

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2, » «)

=РАЗДЕЛЕНИЕ ТЕКСТА(A3, » «)

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

Разделите константы массива в A2 на массив 2X3.

Данные

1,2,3;4,5,6

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,»,»,»;»)

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

Данные

Сделай. Или нет. Нет попытки. -Аноним

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,. «)

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,{«.»,»-«})

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,{«.»,»-«},ЛОЖЬ)

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

Данные

Делать. Или нет. Нет попытки. -Аноним

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,».»)

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

Данные

Делать. Или нет. Нет попытки. -Аноним

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,{«.»,»-«})

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

Данные

Делать. Или нет. Нет попытки. -Аноним

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,{«.»,»-«},ИСТИНА)

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

Совет       Чтобы удалить ошибку #NA, используйте функцию IFNA. Или добавьте аргумент pad_with.

Данные

Делать. Или нет. Нет попытки. -Аноним

Формулы

=РАЗДЕЛЕНИЕ ТЕКСТА(A2,» «,».»,ИСТИНА)

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

См.

также

Текстовые функции (ссылка)

Функция ТЕКСТПЕРЕД

Функция ТЕКСТАФТЕР

функция ТЕКСТОВОЕ СОЕДИНЕНИЕ

СЦЕПНАЯ функция

Разделение текста и чисел в Excel (4 простых способа)

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

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

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

Приступим!

В этом учебном пособии рассматриваются:

Разделение текста и чисел с помощью мгновенного заполнения

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

Из этих данных я хочу разделить текстовую часть и числовую часть и поместить их в два отдельных столбца (столбцы B и C).

Первый способ разделения текста и чисел в Excel, который я хочу вам показать, — это использование Flash Fill.

Мгновенное заполнение (представлено в Excel 2013) работает путем определения шаблонов на основе пользовательского ввода.

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

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

  1. Выберите ячейку B2
  2. Вручную введите ожидаемый результат в ячейку B2, который будет MKT
  1. Выделив ячейку B2, поместите курсор в нижнюю правую часть выделения. Вы заметите, что курсор изменится на значок плюса (это называется дескриптор заполнения)
  1.  Удерживая левую клавишу мыши/трекпада, перетащите маркер заполнения, чтобы заполнить ячейки. Не беспокойтесь, если ячейки заполнены одним и тем же текстом
  2. Щелкните значок «Параметры автозаполнения», а затем выберите параметр «Быстрое заполнение».

Вышеуказанные шаги извлекут текстовую часть из ячеек в столбце A и дадут результат в столбце B.

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

Вы можете выполнить тот же процесс, чтобы извлечь числа в столбце C. Все, что вам нужно сделать, это ввести ожидаемый результат в ячейку C2 (шаг 2 в процессе, изложенном выше)

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

Разделение текста и чисел с помощью формулы

Ниже у меня есть данные о сотрудниках в столбце A, и я хочу использовать формулу для извлечения только текстовой части и помещения ее в столбец B, извлечения числовой части и помещения ее в столбец C

Поскольку данные не согласованы (т. е. алфавиты в коде отдела и числа в номере сотрудника имеют разную длину), я не могу использовать функцию ВЛЕВО или ВПРАВО для извлечения только текстовой части или только числа часть.

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

 =ЛЕВЫЙ(A2,МИН(ЕСЛИОШИБКА(НАЙТИ({0,1,2,3,4,5,6,7,8,9) },A2),""))-1)

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

 =MID(A2,MIN(ЕСЛИОШИБКА(НАЙТИ({0,1,2,3,4,5,6,7,8,9},A2),"")),100)

Как эта формула работает?

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

=ЛЕВО(A2,МИН(ЕСЛИОШИБКА(НАЙТИ({0,1,2,3,4,5,6,7,8,9},A2)»,»))-1)

Часть НАЙТИ в формуле находит положение цифр от 0 до 9 в ячейке A2. Если он находит эту цифру в ячейке A2, он возвращает позицию этой цифры, а если он не может найти эту цифру, он возвращает ошибку значения (#ЗНАЧ!)

Для ячейки A2 результат показан ниже:

{#ЗНАЧ!,4,#ЗНАЧ!,#ЗНАЧ!,#ЗНАЧ!,6,#ЗНАЧ!,5,#ЗНАЧ!,#ЗНАЧ! }

  • Для 0 возвращается #ЗНАЧ! так как он не может найти эту цифру в ячейке A2
  • Для 1 возвращается 4, так как это позиция первого вхождения 1 в ячейке A2
  • и так далее…

Эта формула НАЙТИ затем включается в функцию ЕСЛИОШИБКА, которая удаляет все ошибки значений, но оставляет числа.

Результат выглядит так, как показано ниже:

{«»,4″,»»,»»,»,6″,»,5″,»»,»»}

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

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

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

А что, если ситуация обратная – у нас сначала цифры, а потом текст, и мы хотим разделить цифры и текст?

Вы все еще можете использовать ту же логику с одним небольшим изменением — вместо поиска минимального значения, которое дает нам позицию 1-й цифры в ячейке, вам нужно использовать функцию МАКС. Чтобы найти позицию последней цифры в этой ячейке клетка. Как только вы это сделаете, вы можете снова использовать функцию LEFT или функцию MID, чтобы разделить числа и текст.

Разделение текста и чисел с помощью VBA (пользовательская функция)

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

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

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

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

 'Код, созданный Sumit Bansal с https://trumpexcel.com
'Этот код создаст функцию, которая может отделять числа от ячейки
Функция GetNumber (CellRef как строка)
Dim StringLength как целое число
StringLength = Len(CellRef)
Для i = 1 до StringLength
Если IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Далее я
ПолучитьЧисло = Результат
Завершить функцию

И ниже код VBA, который создаст другую функцию «GetText», которая примет ссылку на ячейку в качестве входного аргумента и предоставит вам весь текст из этой ячейки

 «Код, созданный Sumit Bansal из https://trumpexcel. com
''Этот код создаст функцию, которая может отделять текст от ячейки
Функция GetText (CellRef как строка)
Dim StringLength как целое число
StringLength = Len(CellRef)
Для i = 1 до StringLength
Если нет (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Далее я
ПолучитьТекст = Результат
Завершить функцию

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

  1. Щелкните вкладку «Разработчик» на ленте

    2. .

  2. Щелкните значок Visual Basic

    3. .

  1. В открывшемся редакторе Visual Basic слева вы увидите Project Explorer. Это будет иметь рабочую книгу и имена рабочих листов вашей текущей рабочей книги Excel. Если вы этого не видите, нажмите «Вид» в меню, а затем нажмите «Проводник проекта 9».0027
  1. Выберите любое имя листа (или любой объект) для книги, в которую вы хотите добавить эту функцию
  2. Нажмите кнопку «Вставить» на верхней панели инструментов, а затем нажмите «Модуль». Это вставит новый модуль для этой книги

    3. .

  1. Дважды щелкните значок модуля в «Проводнике проектов». Это откроет окно кода модуля.
  1. Скопируйте и вставьте вышеуказанный код пользовательской функции в окно кода модуля
  1. Закрыть редактор VB

С помощью описанных выше шагов мы добавили код пользовательской функции в книгу Excel.

Теперь вы можете использовать функции =ПОЛУЧИТЬЧИСЛО или =ПОЛУЧИТЬТЕКСТ так же, как и любую другую функцию рабочего листа.

Примечание. Если у вас есть код макроса в окне кода модуля, вам необходимо сохранить файл как файл с поддержкой макросов (с расширением .xlsm вместо расширения .xlsx)

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

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

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

При использовании функций, хранящихся в личной книге макросов, следует помнить одну важную вещь: перед именем функции необходимо добавлять префикс =PERSONAL.XLSB!. Например, если я хочу использовать функцию ПОЛУЧИТЬЧИСЛО в рабочей книге Excel и сохранил код для нее в рабочей книге почтовых макросов, мне придется использовать =ЛИЧНОЕ.XLSB!ПОЛУЧИТЬЧИСЛО(A2)

Разделение текста и чисел с помощью Power Query

Power Query постепенно становится моей любимой функцией в Excel.

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

Если у вас есть данные в Excel и вы хотите использовать Power Query для преобразования этих данных, одним из предварительных условий является преобразование этих данных в таблицу Excel (или именованный диапазон).

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

Вот шаги для этого:

  1. Выберите любую ячейку в таблице Excel
  2. Перейдите на вкладку Данные на ленте
  1. В группе «Получить и преобразовать» щелкните «Из таблицы/диапазона»
  1. В открывшемся редакторе Power Query выберите столбец, из которого вы хотите отделить числа и текст
  2. Перейдите на вкладку «Преобразование» на ленте Power Query

    3. .

  1. Щелкните параметр «Разделить столбец»
  1. Нажмите «От нецифры к цифре».
  1. Вы увидите, что столбец был разделен на два столбца, в одном из которых только текст, а в другом только цифры
  1. [Необязательно] Измените имена столбцов, если хотите
  2. Перейдите на вкладку «Главная», а затем нажмите «Закрыть и загрузить». Это вставит новый лист и даст нам результат в виде таблицы Excel.

Описанные выше шаги берут данные из исходной таблицы Excel, используют Power Query для разделения столбца и разделения текста и числовых частей на два отдельных столбца, а затем возвращают нам вывод на новом листе в та самая рабочая тетрадь.

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

Теперь позвольте мне рассказать вам самое интересное об этом методе. Ваша исходная таблица Excel (которая является источником данных) связана с выходной таблицей Excel.

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

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

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

Теперь, если вы получаете новый набор данных, вы можете просто поместить его вместо ваших исходных данных и обновить запрос, и вы получите результат через несколько секунд. Кроме того, вы можете просто изменить источник в Power Query с существующей таблицы Excel на другую таблицу Excel (в той же или другой книге).

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