Текст в экселе: Функция ТЕКСТ() в EXCEL. Примеры и описание

Текст в ячейке Excel. Как использовать функцию текста

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

Кстати, учебный центр РУНО посвятил этой тебе уникальный практический курс, по окончании которого начинающий пользователь может разобраться в любых инструментах Excel и с легкостью сможет использовать их на практике. Узнайте о курсе Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности.

1. Что такое функция ТЕКСТ в Excel;
2. Для чего нужна функция ТЕКСТ в Excel;
3. Как применять функцию ТЕКСТ;

Что такое текстовая функция Excel?

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

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

Для чего нужна функция ТЕКСТ в Excel

Функция Текста в Excel может быть полезна в следующих случаях:

1. Когда мы хотим отобразить даты в нужном формате;
2. Когда мы хотим отобразить числа в нужном формате или в более детализированном виде;
3. Когда мы хотим объединить числа с текстом или символами.

Как применять функцию текста

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

Затем необходимо выбрать нужный тип преобразования в выпадающем списке:

Функция преобразования работает по этой формуле:

ТЕКСТ(значение; формат)

Значение — это числовое значение или ссылка на ячейку, которая его содержит.

А формат — это текстовая строка, которая может выглядеть, например, как “Д.М.ГГГГ» для вывода даты в нужном форматие. Или так: «# ##0,00», — для вывода в виде числа с разделителями разрядов и двумя знаками после запятой.

Для примера рассмотрим один из вариантов применения функции текста в Excel.

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

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

Важно знать:
 
Инструмент “ТЕКСТ” в  Excel преобразует числовые значения в текстовый формат. Стоит помнить, что данный факт может затруднить их использование в дальнейших вычислениях. Поэтому специалисты рекомендуют сохранять исходные данные в одной ячейке, а функцию текста использовать в другой. Затем, если потребуется создать другие формулы, можно будет сослаться на исходное значение, а не на результат преобразования в текст.

Возможно, что упомянутые выше инструменты станут для вас полезными в работе и позволят повысить производительность при составлении отчетной информации. Еще больше информации о полезных инструментах Excel можно найти на сайте образовательного центра “РУНО”. Стать настоящим профессионалом в Excel поможет дистанционный курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности

Наш профессиональный совет! Для уверенной работы с пакетом Excel необходимо владеть базовыми навыками и умениями. Пройдя учебные курсы Microsoft Excel, вы сможете в короткие сроки освоить работу с продуктом Microsoft и успешно применять полученные навыки на практике. Курс ведёт сертифицированный тренер Microsoft. По окончании комплексного курса  “Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности” вы  научитесь: применять продвинутые инструменты вычисления, а также инструменты проверки и подготовки данных; эффективно работать с большими табличными массивами; анализировать данные с помощью сводных таблиц; применять новые диаграммы Excel; применять альтернативные методики форматирования; защищать данные книги. После завершения обучения вы получите удостоверение о повышении квалификации! Получить доступ     Учебная программа

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

Все слушатели закрепляют материал по готовым конспектам лекций. Это значит, что на занятиях никто не тратит время на записи «под диктовку».

СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:

почему не видно значение? – WindowsTips.Ru. Новости и советы

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

Причины

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

1. Цвет надписи сливается с общим фоном.
2. Информация банально не вмещается по ширине / высоте или налезает на другие столбцы, из-за чего появляется ошибка.
3. Не установлен перенос симовлов.
4. Количество символов больше 256 символов.
5. Неправильный формат ячейки Excel.
6. Ячейка скрыта от просмотра.
7. Сбои в работе программы и т. д.

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

Что делать

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

Программа показывает ###

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

• Установите формат ячейки на «Общие». Часто такой шаг позволяет решить проблему.

• Проверьте форматирование. Для этого жмите по ячейке и жмите «Форматировать…». Далее жмите на «Номер» и «Custom» в поле с левой стороны, а потом «Общие».
• Кликните правой кнопкой мышки на интересующей секции и выберите «Форматировать …», а далее установите интересующую категорию.
• Попробуйте сместить границу. Для этого жмите на панели, где не отображается текст в Excel. При появлении плюса стрелками растяните границу вправо. После отпустите кнопку мышки, чтобы увидеть содержимое. Если этого недостаточно, еще раз сместите границу.
• Выровняйте границу по тексту. Для этого наведите указатель на место, где не отображается текст. После этого вместо захвата / сдвига линии границы дважды кликните по ней. Выждите, пока столбец настроится под данные Excel.
• Попробуйте уменьшить размер текста. Выделите проблемный участок, где не видно информации, и уменьшите шрифт в основной панели. Попробуйте разные варианты, чтобы найти подходящее решение.

• Используйте автоматический подбор ширины. Выберите область, где не отображается текст в Excel, кликните ПКМ, а после «Формат…». Здесь выберите «Выравнивание», а в блоке «Отображение» поставьте отметку возле «Автоподбор ширины». Далее жмите «ОК».

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

Текст вообще не отображается в Excel

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

1. Выделите интересующие секции.
2. В меню «Формат» укажите пункт «Ячейки» и перейдите в раздел «Число».
3. В области «Категория» укажите общий или другой формат (главное, чтобы не пользовательский).
4. Жмите на кнопку Ок.

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

Альтернативные шаги

Существует еще ряд действий, если не отображается текст в Excel. Сделайте следующие шаги:

• Обратите внимание на наличие знака переноса, а именно символа 10 или 13. Выделите ячейку и жмите «Перенос …». Если этот процесс происходит, значит, в строке формул видна только верхняя строка. В таком случае потяните ее вниз или установите курсор за символом и жмите Del.

• Проверьте возможности своего ПК / ноутбука. Возможно, информация не отображается в ячейке Excel из-за слабой оперативной памяти или чрезмерной загрузки процессора. Для решения проблемы попробуйте разгрузить устройство и проверьте работоспособность еще раз.
• Сделайте перенос, если эта опция еще не настроена. Для этого выделите нужную секцию (одну или несколько), зайдите в раздел «Главная» и жмите «Перенос по словам», а после «Обтекание …». После этого данные, которые находятся в графе, переносятся с учетом ширины столбца. Если этот параметр меняется, подстройка происходит в автоматическом режиме.

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

В крайнем случае, когда ничего не помогает, попробуйте связаться с поддержкой Excel по ссылке support.microsoft.com/ru-ru/excel. В процессе обращения нужно описать ситуацию и спросить, как можно исправить проблему. Во время общения необходимо отметить, какие шаги уже были сделаны для достижения результата.

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

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

10 лучших текстовых функций в Excel

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

Текстовые функции Excel

Определение: Функция НАЙТИ используется в Excel для поиска позиции требуемой текстовой строки в другой доступной текстовой строке

Аргументы/Синтаксис: НАЙТИ(найти_текст, внутри_текста, [начальный_номер])

найти_текст: Это обязательный аргумент. Требуется текст, который пользователь хочет найти

внутри_текста: Это обязательный аргумент. Текст, содержащий текст, который хочет найти пользователь

start_num: Это необязательный аргумент. Он указывает символ, с которого следует начать поиск. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет найти позицию текста «an» в имени человека, можно использовать функции НАЙТИ; например, НАЙТИ(«an»,»Roland Watson»,1). Выход этой функции равен 4, поскольку текст начальной позиции «an» равен 4 в «Roland Watson».

 

2. СЛЕВА:

Определение:

ЛЕВАЯ функция в Excel возвращает левые символы из текстовой строки на основе количества символов, указанного пользователем.

Аргументы/Синтаксис: LEFT(текст, [число_знаков])

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

num_chars: Это необязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь с левой стороны. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь первые 5 букв имени, можно использовать ЛЕВЫЕ функции; например, ЛЕВЫЙ(«Роланд Ватсон»,5). Результатом этой функции является «Rolan».

 

3. СПРАВА:

Определение: ПРАВАЯ функция в Excel возвращает правые символы из текстовой строки на основе количества символов, указанного пользователем.

Аргументы/Синтаксис: ПРАВО(текст, [число_символов])

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

num_chars: Это необязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь с правой стороны. По умолчанию принимает значение 1.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь последние 5 букв имени, можно использовать функцию ПРАВИЛЬНО; например, ПРАВО(«Роланд Ватсон», 5). Выход этой функции — «атсон».

4. СРЕДНИЙ:

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

Аргументы/Синтаксис: MID(text, start_num, num_chars)

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

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

num_chars: Обязательный аргумент. Он указывает количество символов, которое пользователь хочет извлечь из начальной позиции

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет извлечь текст «и» из имени, можно использовать функцию MID; например, MID(«Роланд Ватсон»,4,3). Результатом этой функции является «и».

5. ОТДЕЛКА:

Определение: Функция TRIM в Excel удаляет все лишние пробелы из текста, кроме одиночных пробелов между словами. Он используется, когда в тексте есть неравномерные интервалы между словами.

Аргументы/Синтаксис: TRIM(текст)

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

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд   Ватсон». Но между именем («Роланд») и фамилией («Ватсон») два пробела. Теперь, если пользователь хочет удалить лишние пробелы, можно использовать функцию TRIM; например, TRIM(«Роланд Ватсон»). Результатом этой функции является «Роланд Ватсон».

6. СОБСТВЕННЫЙ:

Определение: Функция PROPER в Excel делает заглавной первую букву каждого слова в строке и преобразует все остальные буквы слов в нижний регистр.

Аргументы/Синтаксис: PROPER(текст)

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

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет сделать имя правильно, можно использовать функцию PROPER; например, PROPER(«РОЛАНД УОТСОН»). Результатом этой функции является «Роланд Ватсон».

7. ЗАМЕНА:

Определение: Функция ПОДСТАВИТЬ в Excel используется для замены любой части старой текстовой строки новой текстовой строкой.

Аргументы/Синтаксис: ПОДСТАВИТЬ (текст,старый_текст,новый_текст,[instance_num])

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

old_text: Обязательный аргумент. Это текст, который пользователь хочет заменить

new_text: Обязательный аргумент. Это текст, который пользователь хочет заменить старым текстом на

. [номер_экземпляра]: необязательный аргумент. Указывает, какое вхождение old_text пользователь хочет заменить новым_текстом. Если пользователь указывает instance_num, заменяется только этот экземпляр old_text. В противном случае каждое вхождение old_text в тексте заменяется на new_text.

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «Роланд Ватсон». Теперь, если пользователь хочет заменить «Роланд» на «Джон», можно использовать функцию ЗАМЕНА; например, ПОДСТАВИТЬ («Роланд Ватсон», «Роланд», «Джон»). Результатом этой функции является «Джон Ватсон».

8. ВЕРХНИЙ:

Определение: ПРОПИСНАЯ функция в Excel делает заглавными все буквы каждого слова в строке Аргументы/Синтаксис: ПРОПИСНЫЕ(текст)

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

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет преобразовать имя в полные заглавные буквы, можно использовать функцию ПРОПИСНЫЕ; например, ВЕРХНИЙ(«РОЛАНД УОТСОН»). Выход этой функции: «РОЛАНД УОТСОН»

9. НИЖНИЙ:

Определение: НИЖНЯЯ функция в Excel преобразует все буквы каждого слова в строке в маленькие буквы

Аргументы/Синтаксис: НИЖНЯЯ(текст)

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

Пример: Предположим, в Excel ячейка A2 содержит полное имя человека как «РОЛАНД УОТСОН». Теперь, если пользователь хочет полностью преобразовать имя в строчные буквы, можно использовать функцию LOWER; например, LOWER(«РОЛАНД УОТСОН»). Результатом этой функции является «roland watson» 9.0003

10. ПОВТОР:

Определение: Функция ПОВТОР в Excel повторяет указанный текст заданное количество раз. Функция ПОВТОР может использоваться для заполнения ячейки несколькими экземплярами текстовой строки

Аргументы/Синтаксис: ПОВТОР(текст, число_время)

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

число_раз: Это обязательный аргумент. Требуется положительное число, указывающее, сколько раз повторять текст

Пример: REPT(«-«,5) = «—–»  Отображает дефис (-) 5 раз

 

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

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

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

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

Приступим!

В этом руководстве рассматриваются:

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

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

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

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

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

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

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

1. Выберите ячейку B2
2. Вручную введите ожидаемый результат в ячейку B2, который будет MKT

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

4.  Удерживая левую клавишу мыши/трекпада, перетащите маркер заполнения, чтобы заполнить ячейки. Не беспокойтесь, если ячейки заполнены одним и тем же текстом
5. Щелкните значок «Параметры автозаполнения», а затем выберите параметр «Быстрое заполнение»

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

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

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

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

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

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

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

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

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

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

 =MID(A2,MIN(IFERROR(FIND({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», которая будет принимать ссылку на ячейку в качестве входного аргумента, извлекать все числа в ячейке и выдавать их в качестве результата.

 'Код, созданный Сумит Бансал с 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», которая будет принимать ссылку на ячейку в качестве входного аргумента и давать вам весь текст из этой ячейки

 'Код, созданный Сумит Бансал с 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. Щелкните значок Visual Basic.

3. В открывшемся редакторе Visual Basic слева вы увидите Project Explorer. Это будет иметь рабочую книгу и имена рабочих листов вашей текущей рабочей книги Excel. Если вы этого не видите, нажмите «Вид» в меню, а затем нажмите «Проводник проектов»

4. . Выберите любое из имен листов (или любой объект) для книги, в которую вы хотите добавить эту функцию.
5. Нажмите кнопку «Вставить» на верхней панели инструментов, а затем нажмите «Модуль». Это вставит новый модуль для этой книги

6. Дважды щелкните значок модуля в «Проводнике проектов». Это откроет окно кода модуля.

7. Скопируйте и вставьте приведенный выше код пользовательской функции в окно кода модуля

8. Закройте редактор 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. Щелкните вкладку «Данные» на ленте

3. В группе «Получить и преобразовать» щелкните «Из таблицы/диапазона»

4. В открывшемся редакторе Power Query выберите столбец, из которого вы хотите отделить числа и текст
5. Щелкните вкладку «Преобразование» на ленте Power Query.

6. Щелкните параметр «Разделить столбец».

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

8. [Необязательно] Измените имена столбцов, если хотите
9. Перейдите на вкладку «Главная». а затем нажмите «Закрыть и загрузить». Это вставит новый лист и даст нам результат в виде таблицы Excel.

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

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

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

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

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

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

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

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