Excel формула в текстовой ячейке

Бывает, что введя формулу и нажав клавишу

ENTER

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

Итак, по какой-то причине пользователь ввел формулу в ячейку с Тектовым форматом.

Заставить EXCEL интерпретировать введенное в ячейку выражение как формулу, а не как текст — очень просто: нажмите клавишу

F2

, затем

ENTER (ВВОД)

.

Ниже приведено более подробное объяснение:

• выделите ячейку с формулой (на рисунке это ячейка
  
  А1
  
  );
• формат ячейки установите
  
  Общий
  
  (нажав
  
  CTRL+1
  
  и выбрав соответствующий Числовой формат или через меню

  );

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

  Строку формул

  ;

• нажмите
  
  ENTER
  
  .

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

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

Однако, если перед формулами стоит  апостроф (‘), то существует подход, который позволяет быстро преобразовать значение ячейки в формулу.  Апостроф сообщает EXCEL что ячейка д.б. текстовой не зависимо от того какой фактически установлен Формат ячеек. В

Строке формул

значения такой ячейки выглядят, например, как ‘=C15.

Формально слевастоящий апостроф не является символом и инструмент Найти/Заменить не сработает, но сработает

Текст-по-столбцам

:

• Выделите столбец с формулами (формулы м.б. разными)
• Вызовите

  Текст-по-столбцам

• После вызова этого инструмента во втором окне укажите в качестве разделителя апостроф ‘
• Нажмите Готово

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

Преобразование формул в значения

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

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

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его правой кнопкой мыши – Копировать (Copy).
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

   
   либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

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

   В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

   

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

1. Копируем выделенный диапазон Ctrl+C
2. Тут же вставляем обратно сочетанием Ctrl+V
3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

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

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
 

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub
 

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых

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

В этом случае:

• всё будет максимально быстро и просто
• можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
• в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
• любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Ссылки по теме

• Что такое макросы, как их использовать, копировать и запускать
• Как скопировать формулы без сдвига ссылок
• Как считать в Excel без формул

Вставка текста в ячейку с формулой в Microsoft Excel

​Смотрите также​ квадратной скобки.​ потребность пользователей Excel.​ так как в​ этом аргументе, то​ большинства будет слишком​ Макрофункция будет называться​ способом, важно лишь​ знака в текстовой​ Excel определяет количество​Excel вернул порядковый номер​ используется​ товара») облегчает понимание​ первый столбец​СЦЕПИТЬ​ текст. После этого​ потом поместить курсор​Довольно часто при работе​Обратите внимание! Что в​ Их мы и​

​ ее третьем аргументе​ по умолчанию он​

Процедура вставки текста около формулы

​ много или мало​ ВЗЯТЬФОРМАТ (или можете​ соблюдать правила оформления​ строке. А функция​ знаков для функции​ для даты и​&​ данных. Чтобы включить​«Общая сумма затрат»​. Данное окно состоит​ закрываем кавычки. Ставим​ в строку формул.​ в Excel существует​ нашем примере все​ будем использовать для​ указано число позиции​ равен = 1,​​ количество символов, возвращаемых​​ назвать ее по-своему).​

Способ 1: использование амперсанда

​ форматов, который должен​ ПСТР возвращает текстовые​ ПРАВСИМВ. Функция ДЛСТР​ общий формат вместо​​для объединения форматированной​​ текст в функции​с исходной формулой,​ из полей под​ знак амперсанда. Затем,​Сразу после формулы ставим​ необходимость рядом с​ исходные и разделенные​

​ разделения строк.​ первого дефиса. А​ то есть с​ функцией ПРАВСИМВ.​ Исходный VBA-код макрофункции​ распознаваться в Excel.​ значения (см. пример​ «считает» общую длину​ денежного. Чтобы избежать​ даты со словами​ и формулы, заключите​ чтобы он не​ наименованием​ в случае если​ знак амперсанд (​​ результатом вычисления формулы​​ строки имеют разную​

1. ​Допустим на лист Excel​ значит поиски будут​ первого, а значит​На практике очень часто​ выглядит так:​Например, ниже заполненная аргументами​ выше). Либо можно​ текста. Затем отнимается​​ подобного результата, применяется​​ «Сегодня ». И,​ его в кавычки​ занимал лишнее место​«Текст»​

   

2. ​ нужно внести пробел,​&​​ вставить поясняющий текст,​​ длину и разное​ были импортированные данные​​ вестись не по​​ целая строка.​ приходится автоматически находить​Public Function ВЗЯТЬФОРМАТ(val As​ функция ТЕКСТ возвращает​ заменить найденный текст​ количество знаков до​ функция ТЕКСТ. Она​ да-да, после слова​ («»). Кавычки указывают,​ на листе. Просто​. Их количество достигает​​ открываем кавычки, ставим​​). Далее в кавычках​

   

3. ​ который облегчает понимание​ количество символов. Именно​ из другой программы.​ всей строке, а​На пример, в примере​​ определенный символ, чтобы​​ Range) As String​ число в денежном​ посредством функции ЗАМЕНИТЬ.​ первого пробела (найденное​ форматирует значения по​ «сегодня» необходимо поставить​

   

   ​ что введенная фраза​ удалить его не​255​ пробел и закрываем​ записываем слово​ этих данных. Конечно,​​ поэтому мы называли​​ Из-за несовместимости структуры​

   

4. ​ только по ее​ функция находит первый​ функция сама находила​Dim money As​ формате пересчитанному по​Скачать пример функции для​ ПОИСКом).​ заданию пользователя.​ пробел.​ является текстом. Под​ получится, так как​, но для нашего​ кавычки. Щелкаем по​​«рублей»​​ можно выделить для​ такую формулу –​​ данных при импорте​​ части начиная от​

   
5. ​ дефис в строке​ начальную позицию для​ String​ курсу 68 руб./1$:​

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

​ «PWR-16-Small». В результате​ отделения фрагмента текста​money = WorksheetFunction.Text(val,​Измененная ниже формула возвращает​ в Excel​ отчество и фамилию.​ выглядит так:​ и нажав клавишу​подразумевается любой символ,​СЦЕПИТЬ​ три поля. В​Enter​ не будут отображаться​ но не во​​ статьи. Она подходит​​ разных категорий были​

​Таким образом мы создали​ своего вычисления она​ из исходной строки.​ val.NumberFormat)​ долю в процентах​Читайте так же: как​ Для имени используем​

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

Способ 2: применение функции СЦЕПИТЬ

​ динамическую формулу, которая​ по умолчанию возвращает​Для реализации данной задачи​ВЗЯТЬФОРМАТ = Replace(money,​​ от общей выручки:​​ перевести число и​ такую же формулу:​ формат. Где брать​пользователь видит в​ цифры, пробелы и​ вполне можно. Кликаем​ текст, во втором​Для записи текста вместе​ числа выводимого формулой.​

​ дополнительных элементов является​

​ при решении подобного​ ячейку. Необходимо из​​ автоматически определяет где​​ число 4. Так​​ следует использовать формулу​​ Application.ThousandsSeparator, » «)​Простой способ проверки синтаксиса​ сумму прописью.​Формула для извлечения фамилии​ строку формата? Щелкаем​ ячейке не результат​ знаки пунктуации. Пример:​

​ левой кнопкой мыши​ – ссылку на​ с функцией, а​ Они просто служат​ рациональным. Впрочем, в​ рода задач. Гибкость​ этой ячейки отделить​​ (на какой позиции)​​ как первый дефис​

1. ​ с комбинацией функций​​End Function​​ кода формата, который​​Синтаксис функции ПОИСК:​​ несколько иная: Это​ правой кнопкой мыши​

   

2. ​ вычисления формулы, а​​= A2 &» продал​​ по сектору панели​​ ячейку, в которой​​ не с обычной​​ указателем для программы,​​ Экселе имеются способы​ придает ей сложная​​ целые числовые значения.​​ в строке находится​

   

3. ​ в и сходной​​ ПРАВСИМВ, ДЛСТР и​​Скопируйте его в модуль​ распознает Excel –​«искомый текст» — что​​ пять знаков справа.​​ по ячейке со​​ саму формулу. Причина​​ «& B2 &»​ координат того столбца,​ содержится формула, и​ формулой, все действия​ что это текст.​ поместить формулу и​ комбинация из функций​ Пример таких неправильно​ первый и второй​ строке находится на​

   ​ НАЙТИ:​​ («Insert»-«Module») VBA-редактора (ALT+F11).​​ это использование окна​​ нужно найти;​​ Вложенные функции ПОИСК​ значением. Нажимаем «Формат​ — Текстовый формат​ единицы.»​

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

   ​ разместим текст.​​ как были описаны​​ вывести результат в​

   ​ ячейку вместе. Давайте​ достаточно определить закономерность​​ ниже на рисунке:​​ их можно использовать​

   
4. ​Но если мы воспользуемся​ автоматически определять позицию​Примеры как создать пользовательскую​ этого:​ искать;​ третий пробелы в​ окне выбираем «все​

   

5. ​ в этом случае​ что ячейки в​ столбец выделяется. Щелкаем​Устанавливаем курсор в поле​​ выше.​​ ячейку, щелкаем по​ разберемся, как это​ и указать их​Сначала определим закономерность, по​ как аргументы в​ третьим необязательным аргументом​ в текстовой строке​

   ​ функцию в Excel.​

   ​Щелкните правой кнопкой мышки​«начальная позиция» — с​ строке. ПОИСК(» «;A3;1)​ форматы». Копируем нужный​ заставить формулу работать.​

   ​ столбце A содержат​

   ​ по выделению правой​​«Текст1»​​Текст также можно указывать​ кнопке​

   

6. ​ можно сделать при​ в параметрах функций:​​ которой можно определить,​​ других функциях.​ и укажем в​ для указанного символа​Теперь изменяем нашу формулу​ по любой ячейке​ какой позиции начинать​ находит первый пробел​​ в строке «Тип».​​Итак, по какой-то причине​ названия, а ячейки​ кнопкой мыши. Запускается​. Вписываем туда слово​ в виде ссылки​Enter​ помощи различных вариантов.​ будут это квадратные​ что данные из​Пример из практического опыта​ нем число 5.​ в ее аргументах.​​ и получаем максимально​​ с числом и​

   

7. ​ искать (по умолчанию​ слева (перед отчеством).​ Вставляем скопированное значение​ пользователь ввел формулу​ в столбце B —​ контекстное меню. Выбираем​​«Итого»​​ на ячейку, в​

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

​ офисного сотрудника. Необходимо​ То есть просматривать​ А после использовать​ эффективный результат:​ выберите из появившегося​ – 1).​ К найденному результату​​ в формулу.​​ в ячейку с​ количество продаж. Результат​ в нем пункт​. Писать текстовые выражения​ которой он расположен.​Как видим, после этого​ Excel​ разделительные знаки. Например,​​ на то, что​​ получить часть кода​

​ первую не целиком,​

lumpics.ru

Использование текста в формулах

​ номер позиции в​​Благодаря функции ВЗЯТЬФОРМАТ написанной​ контекстного меню опцию​Если нужно учитывать регистр,​ добавляем единицу (+1).​Приведем еще пример, где​ Тектовым форматом.​ будет приблизительно такой:​«Скрыть»​ можно без кавычек,​ В этом случае,​ действия, вслед за​Если просто попробовать вставить​ это могут быть​ они находятся в​ товаров, которая начинается​ а начиная после​ следующих операциях, например,​ на VBA-макросе мы​ «Формат ячейки». Или​ используется функция НАЙТИ.​ Получаем ту позицию,​

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

​ данная функция. Добавим​ в ячейку выражение​.​

​После этого, как видим,​ проставит их сама.​ прежним, только сами​ формула, находится пояснительная​ ячейку с функцией,​ разделить строку на​ же строке. В​​ Для этого создаем​​ символа. Тогда функция​

​ значений для второго​ и формат из​ клавиш CTRL+1.​ необходимо объединять текст​ искать второй пробел.​​ нули в начале​​ как формулу, а​Для объединения значений в​ ненужный нам столбец​Потом переходим в поле​ координаты ячейки в​ надпись​ то при такой​

​ слова и т.п.​ нашем случае нас​ динамическую формулу:​ будет возвращать нам​ аргумента функции ПРАВСИМВ.​ исходной ячейки и​Перейдите на вкладку «Число».​ с числами. Проблема​

​Часть формулы – ПОИСК(«​ числа. Если ввести​ не как текст​

​ столбцах А и​ скрыт, но при​«Текст2»​ кавычки брать не​«рублей»​ попытке Excel выдаст​В данном примере функция​ интересуют только числа,​Здесь мы использовали автоматический​ порядковую позицию второго​ Реализуется генерация путем​ подставляем его как​В секции «Числовые форматы:»​ заключается в том,​ «;A3;ПОИСК(» «;A3;1)+1) –​

​ вручную, Excel их​ — очень просто:​ В с текстом​ этом данные в​. Устанавливаем туда курсор.​ нужно.​. Но у этого​ сообщение об ошибке​ НАЙТИ во втором​ которые находятся вне​ поиск первого дефиса.​ «-», то есть​ определения необходимого числа​ текстовую строку. Так​ выберите категорию «(все​ что вовремя объединения​

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

support.office.com

Формула в MS EXCEL отображается как Текстовая строка

​ аргументе определяет положение​ квадратных скобок. Каким​​ Номер позиции послужил​​ число – 7.​ при вычитании от​ наша формула с​ форматы)».​ текста с числом​ Это будет конечная​ формулу:​ (на рисунке это​

​ амперсанды (​ расположена функция​ указать то значение,​ вместе с результатом​

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

• ​Текстовую функцию НАЙТИ чаще​ длины строки, которую​ пользовательской макрофункцией ВЗЯТЬФОРМАТ​​Введите в поле «Тип:»​​ нельзя сохранить числовой​
• ​ позиция отчества.​Если нужно вернуть прежние​​ ячейка​​&​СЦЕПИТЬ​ которое выводит формула,​ подсчета формулы можно​
• ​ и текстовое пояснение​​ такую вставку. Но​​ скобки. А в​ выбрать из строк​ аргументом функции НАЙТИ​ всего используют как​
• ​ возвращает функция ДЛСТР​​ автоматически определяет валюту​​ свой пользовательский код​

excel2.ru

Функции для работы с текстом в Excel

​ формат данных ячейки.​Далее из общей длины​ числовые значения (без​А1​). Также обратите внимание,​отображаются корректно.​ а значит, следует​

Примеры функции ТЕКСТ в Excel

​ использовать функцию​ слились воедино без​ существует два способа​ третьем аргументе эта​ целые числа и​ для автоматического поиска​ вспомогательную указав ее​ номера позиции символа​

​ для каждой суммы​ формата и в​ Число в ячейке​ строки отнимается количество​ нулей), то используем​);​ что ячейка В2​Читайте также:​ дать ссылку на​СЦЕПИТЬ​

​ пробела.​ все-таки вставить текст​ же функция вычисляет​ поместить их в​

​ каждого второго дефиса​ в качестве аргумента​ – «-».​

​ в исходных значениях​ секции «Образец» наблюдайте​ отформатировано как текст.​ знаков с начала​ оператор «—»:​формат ячейки установите Общий​ не заключена в​Функция СЦЕПИТЬ в Экселе​

​ ячейку, её содержащую.​. Данный оператор предназначен​

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

​ (нажав​ кавычки. Они окружают​Как скрыть столбцы​ Это можно сделать,​ для того, чтобы​ попытаемся поставить пробел​ выражением. Первый из​ текста в строке​

​ решением является гибкая​ товара. Далее используя​ функций. Например, если​В следующем примере, изображенном​

​Функции ЛЕВСИМВ, ПРАВСИМВ и​ распознан в Excel​ изображен ниже на​

​ пробела. Результат –​

Функция разделения текста в Excel

​ теперь отображаются в​CTRL+1​ текст, который идет​ в Экселе​ просто вписав адрес​

• ​ соединять в одной​ вручную, то это​ них заключается в​ относительно второй открывающийся​
• ​ формула основана на​ функцию ДЛСТР, мы​ мы используем ее​ на рисунке, функция​
• ​ ПСТР прекрасно применяются​ и отображен в​ рисунке. Допустим, например,​ число символов справа,​ числовом формате.​и выбрав соответствующий​ до и после​

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

​ квадратной скобки. Вычисление​ текстовых функциях.​ определяем длину исходной​ как второй аргумент​

​ НАЙТИ используется в​ для разделения строк​ ячейке.​ необходимо в отчете​ которые нужно вернуть.​​ Числовой формат или​ ячейки.​ что существуют два​ установить курсор в​ в нескольких элементах​ Как только будет​

​ второй – в​

​ в третьем аргументе​В ячейку B3 введите​ строки и вычитаем​ для функции ПСТР,​ формуле вместе с​ на слова или​​ поместить список магазинов​Формула «для отчества» строится​

​Отдельные текстовые функции и​ через меню Главное/​Вот еще один пример​ способа вписать в​

​ поле и кликнуть​ листа. Он относится​ нажата кнопка​ использовании функции​ более сложное и​ следующую формулу:​ от нее число​ то мы получим​ функцией ПСТР для​ фрагменты текстов, но​Более упрощенным альтернативным решением​ и их результаты​ по тем же​ их комбинации позволяют​

​ Число/ Числовой формат);​ распространенные задачи, добавив​ одну ячейку формулу​ по ячейке, содержащей​ к категории текстовых​

​Enter​СЦЕПИТЬ​ оно подразумевает вычитание​Теперь скопируйте эту формулу​ позиции второго символа.​ возможность вырезать фрагмент​ выборки средних чисел​

​ при условии, что​ для данной задачи​ продаж в одной​

Функция объединения текста в Excel

​ принципам:​ распределить слова из​нажмите клавишу​ даты на лист.​ и текст: при​

​ формулу на листе.​ функций. Его синтаксис​

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

​ помощи амперсанда и​ Адрес отобразится в​

​ следующий:​

​Но из сложившейся ситуации​Самый простой способ решить​ текста от меньшей.​Выборка чисел из строк​ длины кода вычитаем​

Функция ПОИСК текста в Excel

​ автоматически определяя необходимую​ кода товаров прайс-листа.​ позиции символов с​ РУБЛЬ, которая преобразует​

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

​ окошке аргументов автоматически.​=СЦЕПИТЬ(текст1;текст2;…)​ все-таки существует выход.​ данную задачу –​ А чтобы учитывать​ в отдельные ячейки.​ количество символов до​ позицию в строке​

​Как видно на рисунке​ которых будет разделение.​ любое число в​

​ число после объединения​ одну строку используется​ЛЕВСИМВ (текст; кол-во знаков)​

​ Режим Правки ячейки)​

• ​ составления фразы, например​СЦЕПИТЬ​
• ​В поле​Всего у этого оператора​
• ​ Снова активируем ячейку,​ это применить символ​ еще 2 пробела​​

​ второго дефиса (включительно​ как маркер для​

exceltable.com

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

​ формула сначала ищет​ Что же делать​ текст и отображает​ с текстом (в​ оператор амперсанд (&)​ – отображает заданное​ или поставьте курсор​ «Сегодня — Пятница,​. Первый вариант проще​«Текст3»​

Отформатировать число как текст в Excel с денежным форматом ячейки

​ может быть от​ которая содержит формульное​ амперсанда (​ следует вычитать число​Функция ПСТР возвращает текстовое​ с ним, о​ отделения ее части.​ номер позиции для​

​ если заранее вам​ его в денежном​ столбце D) не​ или функция СЦЕПИТЬ.​ число знаков с​ в Строку формул;​ январь 20.»​ и для многих​

​вписываем слово «рублей».​1​ и текстовое выражения.​&​ 3. В результате​ значение содержащие определенное​ чем свидетельствует сложение​Если использовать формулу, указанную​ символа с помощь​ не известно где​

​ формате:​ сохраняет свой денежный​Например, значения расположены в​ начала ячейки;​нажмите​= «Сегодня» & текст​ пользователей удобнее. Но,​После этого щелкаем по​до​ Сразу после амперсанда​). Данный знак производит​

​ чего получаем правильное​ количество символов в​ +1). Таким образом​

1. ​ в примере мы​ функции НАЙТИ. А​ в текстовой строке​
2. ​Данное решение весьма ограничено​ формат, который определен​ разных столбцах (ячейках):​

​ПРАВСИМВ (текст; кол-во знаков)​ENTER​ (сегодня (); «дддд,​ тем не менее,​ кнопке​

​255​ открываем кавычки, затем​ логическое отделение данных,​ количество символов в​ строке. Аргументы функции:​

​ мы динамически определяем​ вырезаем 2 числовых​ после найденный номер​

​ находится тот символ,​ по функциональности и​ в исходной ячейке​Ставим курсор в ячейку,​ – возвращает заданное​.​

1. ​ мммм дд»)​ в определенных обстоятельствах,​«OK»​аргументов. Каждый из​ устанавливаем пробел, кликнув​ которые содержит формула,​ разделенной строке. С​Первый аргумент – это​
2. ​ второй аргумент для​
3. ​ символа, которые находиться​ позиции использует в​ начиная с которого​
4. ​ подходит только для​ (столбца B).​ где будут находиться​ количество знаков с​Для удобства работы с​Давайте посмотрим, как выполняется​ например при обработке​.​

​ них представляет либо​

Функция РУБЛЬ для форматирования числа как текст в одной ячейке

​ по соответствующей клавише​ от текстового выражения.​ помощью такой гибкой​ ссылка на ячейку​ функции ПРАВСИМВ чтобы​ после первого дефиса​ своих аргументах функция​ нужно вырезать фрагмент​

​ тех случаев если​Чтобы решить данную задачу,​ объединенные три значения.​ конца ячейки;​ текстом в Excel​ данный из внутри​ сложных формул, лучше​Результат выведен в предварительно​ текст (включая цифры​ на клавиатуре, и​ Давайте посмотрим, как​ формулы можно делать​

​ с исходным текстом.​ вырезать разной величины​ из каждой строки​

1. ​ ПСТР.​ текста?​ соединяемое число с​
2. ​ необходимо поместить ссылку​ Вводим равно. Выбираем​

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

​ПОИСК (искомый текст; диапазон​ существуют текстовые функции.​ и снаружи. Функция​ пользоваться оператором​ выделенную ячейку, но,​ и любые другие​ закрываем кавычки. После​ можно применить указанный​ выборку разной длинны​Второй аргумент – это​ фрагмент текста из​ кодов товаров. Обратите​Функция НАЙТИ требует заполнить​Допустим у нас имеется​ текстом является денежно​ к ячейкам с​ первую ячейку с​ для поиска; начальная​ Они облегчают обработку​ TODAY вычисляет текущую​

​СЦЕПИТЬ​ как видим, как​
​ символы), либо ссылки​ этого снова ставим​
​ способ на практике.​ разделенного текста из​
​ позиция первого символа,​ строк. К том​
​ внимание на сложение​


​ минимум 2 из​ прайс-лист с кодами​ суммой в валюте​

​ числами денежных сумм​ текстом и нажимаем​

​ позиция) – показывает​ сразу сотен строк.​ дату, но отображается​

​.​ и в предыдущем​ на ячейки, которые​ знак амперсанда (​У нас имеется небольшая​ разных длинны исходных​ с которого должна​ уже все строки​ в формуле +1,​ 3-х аргументов:​ товаров, как получить​ рубли (или той​ в функцию ТЕКСТ.​

exceltable.com

Примеры использования функции НАЙТИ в Excel формулах

​ на клавиатуре &.​ позицию первого появления​ Рассмотрим некоторые из​ номер, например 40679.​Автор: Максим Тютюшев​ способе, все значения​ его содержат.​&​ таблица, в которой​ строк.​ начинаться разделенная строка.​ с разной длиной,​ благодаря которому мы​Искомый_текст – здесь необходимо​ часть символов после​ которая является по​ Благодаря ей можно​

Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel

​ Затем – знак​ искомого знака или​ них на примерах.​ Функция текст нажмите​Примечание:​ записаны слитно без​Посмотрим, как работает данная​). Затем щелкаем по​ в двух столбцах​

• ​Не результат решения формулы,​
• ​Последний аргумент – это​
• ​ да еще и​

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

​ кавычки (“ “).​ слева направо​ Синтаксис: значение (числовое​ удобочитаемым дату сначала​ можно оперативнее обеспечивать​Для того, чтобы решить​ Для примера возьмем​

​Enter​ переменные затраты предприятия.​ формулы… Заранее благодарен​ должна содержать разделенная​ находится второй дефис.​

​ чтобы определить его​ получить его порядковый​ каждый раз находиться​ панели управления Windows).​ строке. Формула изображена​ Снова — &.​При разделении текста в​ или ссылка на​ задание числа в​ вас актуальными справочными​ данную проблему, снова​ все ту же​.​ В третьем столбце​Бакыт наурзалинов​ строка.​ Но умная формула​ позицию на символы​

​ номер (позицию) в​

Пример использования НАЙТИ и ПСТР в формуле Excel

​ на новой позиции​Функция РУБЛЬ требует для​ на рисунке:​ И так последовательно​ строке учитывается положение​ ячейку с формулой,​ текст, а затем​ материалами на вашем​

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

​ символов?​ заполнения только 2​Формула позволяет решить данную​

1. ​ соединяем ячейки с​ каждого знака. Пробелы​ дающей в результате​ указав «дддд, мммм​ языке. Эта страница​ оператор​
2. ​ в неё ещё​ вычисления формулы и​ сложения, которая суммирует​ состояния, так в​ все понятно –​
3. ​ автоматическом режиме.​ его).​Просматриваемый_текст – тут указываем​PTR-422​ аргумента:​ задачу если речь​ текстом и пробелы.​ показывают начало или​ число); формат (для​ дд» для управления​ переведена автоматически, поэтому​СЦЕПИТЬ​ один столбец​ текстовое выражение разделены​ их и выводит​ ней хранится формула​ это ссылка на​Часто приходится оптимизировать структуру​Как уже упоминалось выше​ ссылку на ячейку​CORPO-6755​Число – ссылка на​

​ идет только об​Получаем в одной ячейке​ конец искомого имени.​ отображения числа в​ как дата отображается—«Пятница,​ ее текст может​и переходим в​«Общая сумма затрат»​ пробелом.​ общим итогом. Нам​ выделенной ячейки​

Динамические формулы с использованием функции НАЙТИ

​ ячейку A3. Второй​ данных после импорта​ по умолчанию функция​ с исходной строкой,​SVCCALL-56532​ числовое значение (обязательный​ одном типе валюты.​ объединенные значения:​Распределим с помощью функций​ виде текста).​ 20 янв.»​ содержать неточности и​ строку формул. Там​

​с пустой ячейкой.​Естественно, что все указанные​ требуется в ту​Vlad r.​ аргумент мы вычисляем​ в Excel. Некоторые​ НАЙТИ возвращает позицию​ которая содержит искомый​Функция ЛЕВСИМВ нам сразу​ аргумент для заполнения).​ Когда валют будет​Использование функции СЦЕПИТЬ:​ имя, фамилию и​Самая полезная возможность функции​Убедитесь, что формат даты​ грамматические ошибки. Для​

​ после каждого аргумента,​Выделяем пустую ячейку столбца​ действия проделывать не​ же ячейку, где​: копировать/специальная вставка формулы​ с помощью функции​ разные значения попадают​ первого найденного искомого​ символ или текст.​ не подходит, так​[Число_знаков] – Количество символов​ несколько придется использовать​С помощью кавычек в​ отчество в разные​ ТЕКСТ – форматирование​ «дддд, дд мммм»​

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

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

​ в конечное выражение​В первой строке есть​ объединения с текстовыми​ и обратите внимание​ вам полезна. Просим​ запятой добавляем следующее​«Вставить функцию»​ обычном введении без​ формулы поясняющее слово​ тогда формать нужных​

​ первой закрывающейся квадратной​ целую строку как​ текстовой строке. Когда​ можно указать номер​ кода. Функция ПРАВСИМВ​ содержаться значения в​ речь пойдет ниже.​

​ любой знак или​ только имя и​ данными. Без использования​ на то, как​ вас уделить пару​ выражение:​, расположенную слева от​ второго амперсанда и​«рублей»​ ячеек как текст​ скобки в строке.​ одно значение. Возникает​ нам нужно найти​ позиции символа в​ так же не​ ячейках с разным​ А пока разберемся​ текст.​ фамилия, разделенные пробелом.​ функции Excel «не​ в формате используются​ секунд и сообщить,​» «;​ строки формул.​ кавычек с пробелом,​.​Лусине​ И к этому​ вопрос: как разбить​ второй такой же​ строке, с которого​ справиться с данной​ форматом валют тогда​ с этой формулой.​Функция ПОИСК возвращает начальную​

exceltable.com

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

​ Формула для извлечения​ понимает», как показывать​ запятые и пробелы.​ помогла ли она​Между кавычками должен находиться​Производится активация​ формульные и текстовые​Активируем ячейку, содержащую формульное​: nuzhno v farmatirovanii​ номеру мы добавляем​ строку на ячейки​ символ и узнать​ следует начинать поиск.​ задачей, ведь в​ нам потребуется распознать​Функция ТЕКСТ требует 2​ позицию искомого текста​ имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)).​ числа, и преобразует​ Обычно запятые используются​ вам, с помощью​ пробел. В целом​Мастера функций​ данные сольются. Вы​

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

​ выражение. Для этого​ nuzhnoy yaycheyke dat​ еще число 2,​ в Excel. Программа​ его позицию в​ Если строка содержит​ ее аргументах следует​ все форматы в​ обязательных для заполнения​ (без учета регистра).​ Для определения второго​ их в базовый​ в формулах для​ кнопок внизу страницы.​

​ в строке функций​. Перемещаемся в категорию​ же можете установить​ либо производим по​ farmat textovi ili​ так как нам​ располагает разными поисковыми​ строке, можем воспользоваться​ более одного найденного​ указать точное количество​ каждой ячейке. По​ аргумента:​ Например:​ аргумента функции ЛЕВСИМВ​ формат.​ отделения аргументов — частей​ Для удобства также​ должно отобразиться следующее​«Текстовые»​ правильный пробел ещё​

​ ней двойной щелчок​ oktivirovat list i​

​ нужен номер символа​ функциями: одни ищут​

​ опциональным третьим аргументом​ искомого символа, то​

​ символов возвращаемого текста​

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

​ умолчанию в Excel​Значение – ссылка на​Функция ПОИСК вернула позицию​ – количества знаков​

1. ​Покажем на примере. Допустим,​ данных, необходимых для​ приводим ссылку на​
2. ​ выражение:​. Далее выделяем наименование​ при выполнении второго​ левой кнопкой мыши,​
3. ​ perevesti v textovi​ после пробела за​ по ячейках другие​ функции. В этом​

​ с помощью данного​ из всех разных​ нет стандартной функции,​ исходное число (в​ 10, т.к. слово​ – используется функция​ нужно объединить текст​ выполнения формулы. Но​ оригинал (на английском​=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)​«СЦЕПИТЬ»​ пункта данного руководства.​ либо выделяем и​ format.Tagda vsyo chto​ квадратной скобкой. В​ ищут по содержимому​ аргументе можно определить​ необязательного аргумента можно​ кодов разной длины.​ которая умеет распознавать​ данном случае).​ «Захар» начинается с​ ПОИСК. Она находит​

​ в строках и​ если запятые рассматриваются​ языке) .​Щелкаем по клавише​и жмем на​При написании текста перед​ жмем на функциональную​ vi napishite budet​ последнем аргументе функция​ ячеек. Ведь выполнять​ позицию символа исходной​ указать номер символа​ Если в аргументе​ и возвращать форматы​Формат – текстовый код​ десятого символа в​ пробел в ячейке​ числовые значения:​ как текст, их​Мы часто слышим, что​ENTER​ кнопку​ формулой придерживаемся следующего​ клавишу​ otabrazhatsya temi zhe​ вычисляет какое количество​

​ поиск по текстовой​ строки, с которого​ с которого будет​ будет указано фиксированное​ ячеек. Поэтому напишем​ формата ячеек Excel​ строке. Где это​ А2, начиная слева.​Использование амперсанда без функции​ можно использовать по​ использование текста в​. Теперь наши значения​«OK»​ синтаксиса. Сразу после​F2​ simvolomi.zhilayu udachi​ символов будет содержать​ строке, которая содержится​ следует начинать поиск.​ просматриваться остальная часть​ числовое значение, то​ свою пользовательскую макрофункцию​ (может быть пользовательский).​ может пригодиться?​Формула для извлечения фамилии:​ ТЕКСТ дает «неадекватный»​ необходимости.​ формулах (например, такого:​ разделены пробелами.​

exceltable.com

Вопрос для знатоков Excel! Как преобразовать формулу в текст?

​.​ знака «=» открываем​. Также можно просто​

​Волков николай​​ разделенная строка после​ в ячейке ¬–​Например, следующая формула возвращает​ строки. Если он​

​ для некоторых кодов​​ и добавим ее​
​Число можно форматировать любым​Функция ПОИСК определяет положение​С помощью функции ПОИСК​ результат:​

​И наконец, в формуле​​ «продано 2347 единиц​При желании можно спрятать​Запускается окошко аргументов оператора​ кавычки и записываем​ выделить ячейку, а​: F2 потом F9​ разделения, учитывая положение​ это также распространенная​ позицию второго дефиса,​

​ не указан в​​ сработает, а для​

​ в нашу формулу.​

Отображение текста формул

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

Для пользователя Excel всегда встаёт вопрос: как же узнать где в ячейке есть формула, а где нет? 

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

Способ №1

Определить есть ли формула в ячейке нам поможет функция =ЕФОРМУЛА.

Результатом вычислений будет два значения: ИСТИНА — формула в ячейке есть; ЛОЖЬ — формулы в ячейке нет.

Ячейки с формулами найдены. Преобразовать формулу в текст поможет функция =Ф.ТЕКСТ. Она возвращает формулу в виде строки. Есть и минус: её можно использовать только с версии Excel 2013 и выше.

Способ №2

Воспользуемся гибкими возможностями именованных диапазонов в Excel. Переходим на вкладку «Формулы», в блоке кнопок «Определенные имена» нажимаем «Диспетчер имён».

Щёлкаем «Создать», далее вводим имя диапазона (например, ПОЛУЧИТЬФОРМУЛУ), область оставим «Книга», поле «Примечание» можно оставить пустым, в поле «Формула» напишем =ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист2!C1). В примере формула находится в ячейке С1, у вас она может быть где угодно, но важно помнить одно — такой способ работает только в ячейке правее ячейки с формулой!

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

Как только оно появилось сразу щелкаем по нему два раза.

Подтверждаем ввод. Видим результат.

Способ №3

Воспользуемся созданием пользовательской функции на VBA.

Добавим новый модуль (как это сделать можно прочитать ЗДЕСЬ):

1. Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
2. Далее «Insert» — > «Module».

Вставляем код:

Function FFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
‘Application.Volatile True
If ShowFormula Then
If Cell.HasFormula Then
FFormula = «Текст формулы: » & IIf(Cell.HasArray, «{» & Cell.FormulaLocal & «}», Cell.FormulaLocal)
Else
FFormula = «Значение ячейки: » & Cell.Value
End If
Else
FFormula = Cell.HasFormula
End If
End Function

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

Подтверждаем ввод, смотрим на результат.

Если вы знаете ещё варианты решения этой задачи — оставляйте комментарии под статьёй!

Видео:

Обычно Microsoft Excel показывает рассчитанные результаты, когда вы вводите формулы в ячейки. Однако иногда может потребоваться показать только формулу в ячейке, например = СЦЕПИТЬ («000»; «- 2»), как ты с этим справишься? Есть несколько способов решить эту проблему:

Преобразование формулы в текстовую строку с функцией поиска и замены

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

Преобразование формулы в текстовую строку или наоборот одним щелчком мыши

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

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

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

2. Затем нажмите Ctrl + H , чтобы открыть Найти и заменить диалоговое окно в диалоговом окне под Замените вкладка, введите равно = войдите в Найти то, что текстовое поле и введите ‘= в Заменить текстовое поле, см. снимок экрана:

3. Затем нажмите Заменить все , вы можете увидеть, что все рассчитанные результаты заменены исходными текстовыми строками формулы, см. снимок экрана:

Следующий код VBA также поможет вам легко справиться с этим.

1. Удерживайте другой + F11 ключи в Excel, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля.

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

3. В пустой ячейке, например в ячейке D2, введите формулу. = ShowF (C2).

4. Затем щелкните ячейку D2 и перетащите маркер заливки. к тому ассортименту, который вам нужен.

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

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите формулы, которые хотите преобразовать.

2. Нажмите Кутулс > Содержание > Преобразовать формулу в текст, и выбранные вами формулы были сразу преобразованы в текстовые строки, см. снимок экрана:

Советы: Если вы хотите преобразовать текстовые строки формулы обратно в вычисленные результаты, просто примените утилиту преобразования текста в формулу, как показано на следующем снимке экрана:

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

Скачать и бесплатную пробную версию Kutools for Excel Сейчас !