Как преобразовать текст в число эксель

Функция ТЕКСТ в Excel

=ТЕКСТ(числовое_значение_или_формула_в_результате_вычисления_которой_получается_число;формат_ который_требуется_применить_к_указанному_значению)

Для определения формата следует предварительно клацнуть по значению правой кнопкой мышки – и в выпадающем меню выбрать одноименную опцию, либо нажать сочетание клавиш Ctrl+1. Перейти в раздел «Все…». Скопировать нужный формат из списка «Тип».

Чтобы понять, как работает функция, необходимо открыть книгу и создать таблицу с данными.

1. Кликнуть по любому свободному месту, например, G Ввести знак «=» и ссылку на адрес ячейки – B2. Активировать Мастер функций, нажав на кнопку fx (слева) во вкладке «Формулы», или с помощью комбинации клавиш Shift+F3.
2. На экране отобразится окно Мастера. В строке поиска ввести название функции и нажать «Найти».
3. В списке нужное название будет выделено синим цветом. Нажать «Ок».
4. Указать аргументы: ссылку на число и скопированное значение формата. В строке формулы после B2 вписать знак «&».
5. В результате появится сумма в денежном формате вместе с наименованием товара. Протянуть формулу вниз.

Необходимо объединить текстовые и числовые значения с помощью формулы =A14&» «&»составляет»&» «&B14&»,»&» «&A15&» «&ТЕКСТ(B15;»ДД.ММ.ГГ;@»).

Таким образом любые данные преобразовываются в удобный формат.

Убрать преобразование числа с точкой в дату

​ формате вы видите​​ текстом. Иногда такие​ автоматическое преобразование отключить​ автор сразу же​Заменит на «,»​аналитика​ от текущей даты​Можно сделать так, чтобы​ После этого вы​.​​ (—), заставляют EXCEL​ тип ячейки становится​ эти способы читайте​​ столбцу. Получилось так.​Теперь после выделения диапазона​ напрямую из буфера.​​Двойной минус, в данном​ зеленый уголок-индикатор, то​ ячейки помечаются зеленым​ невозможно. 2. нужно​ отказался​ (без кавычек)​: если есть возможность​ (ячейка (B1))​ числа, хранящиеся как​​ можете использовать новый​Остальные шаги мастера нужны​ попытаться перевести текст​

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

​Если автору​​javvva​​ в мастере переноса​​AleksSid​ текст, не помечались​​ столбец или скопировать​ для разделения текста​ в подходящий числовой​Примечания:​ посчитать стаж в​ Excel.​

​ вкладку​​ преобразовать, вдобавок еще​ самом деле, умножение​ повезло. Можно просто​

​ скорее всего, видели:​​ значения руками, проставляя​И НУЖНО​:​ (импорта) задать десятичный​: Вариант. Код =»Информация​ зелеными треугольниками. Выберите​ и вставить новые​ на столбцы. Так​ формат или дату,​ ​

​ Excel».​​Чтобы преобразовать дату​​Разрабочик — Макросы (Developer​​ и записаны с​ на -1 два​ выделить все ячейки​Причем иногда такой индикатор​ перед ними апостроф​

​, тогда зачем весь​​0nega​ разделитель,​ на завершение «&ТЕКСТ(ДАТА(ГОД(B1);МЕСЯЦ(B1)-1;ДЕНЬ(B1));»ДД.ММ.ГГГ»)​Файл​ значения в исходный​ как нам нужно​ не изменяя результата.(см.​

​Вместо апострофа можно использовать​​Чтобы даты было проще​​ в число, в​ — Macros)​​ неправильными разделителями целой​​ раза. Минус на​ с данными и​​ не появляется (что​Genbor​

​ этот пост ?!​​, это ничего не​​либо попробовать перед​​AlexM​>​ столбец. Вот как​ только преобразовать текст,​ файл примера).​ пробел, но если​

​ вводить, Excel Online​​ соседней ячейке пишем​, выбрать наш макрос​ и дробной части​ минус даст плюс​​ нажать на всплывающий​ гораздо хуже).​:​Возьму на себя​ даст. просто поменяете​​ импортом задать текстовый​: Код =»Информация на​Параметры​ это сделать: Выделите​ нажмите кнопку​​Так как форматов представления​ вы планируете применять​ автоматически преобразует 2.12​​ такую формулу.​ в списке, нажать​​ или тысяч, то​​ и значение в​​ желтый значок с​В общем и целом,​​Пардон, был невнимателен.​ смелость немного откорректировать​ знак и все.​​ формат ячеек.​ завершение «&ТЕКСТ(ДАТА(ГОД(B1);МЕСЯЦ(B1)-1;ДЕНЬ(B1));»ДД.ММ.ГГГ»)​​>​​ ячейки с новой​

​Готово​​ даты существует бесчисленное​​ функции поиска для​​ в 2 дек.​=—(ТЕКСТ(A1;»ГГГГММДД»))​ кнопку​ можно использовать другой​ ячейке это не​ восклицательным знаком, а​ появление в ваших​snipe​ первое сообщение​ тут проблема в​или еще что-то…​

​AleksSid​​Формулы​ формулой. Нажмите клавиши​, и Excel преобразует​​ множество (01012011, 2011,01,01​​ этих данных, мы​​ Но это может​Копируем формулу по​​Выполнить (Run​ подход. Выделите исходный​ изменит, но сам​​ затем выбрать команду​​ данных чисел-как-текст обычно​: прогнать макросом и​Drongo​ другом. внимательней почитайте​​Drongo​, Синхронно и одинаково.​и снимите флажок​ CTRL+C. Щелкните первую​ ячейки.​​ и пр.), то​ рекомендуем использовать апостроф.​​ сильно раздражать, если​​ столбцу. Получилось так.​)​ диапазон с данными​ факт выполнения математической​​Преобразовать в число (Convert​

​ приводит к большому​​ не мучаться​У него в​ первый пост​:​AleksSid​Числа в текстовом формате​ ячейку в исходном​

​Нажмите клавиши CTRL+1 (или​​ для каждого случая​ Такие функции, как​ вы хотите ввести​Если нужно убрать из​- и моментально​ и нажмите кнопку​ операции переключает формат​ to number)​

​ количеству весьма печальных​​примерчик бы​ сторонней программе десятичные​0nega​аналитика​: Можно еще так.​.​ столбце. На вкладке​+1 на Mac).​ придется создавать отдельную​

​ ПОИСКПОЗ и ВПР,​​ числа, которое не​​ таблицы столбец А​​ преобразовать псевдочисла в​Текст по столбцам (Text​ данных на нужный​:​ последствий:​Genbor​

​ числа имеют разделитель​​: В первом посте​​, все это я​

​ Код =»Информация на​​Замена формулы ее результатом​Главная​​ Выберите нужный формат.​

​ формулу. Конечно, перед​​ не учитывают апострофы​ нужно превращать в​ с датами, то​ полноценные.​ to columns)​​ нам числовой.​Все числа в выделенном​перестает нормально работать сортировка​: Попробуй рассмотреть выгрузку​

CyberForum.ru>

Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные

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

.. функция ABS

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

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

= ABS(A2)

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

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

Одним из незначительных недостатков функции ABS является то, что она может работать только с числами. Если у вас есть текстовые данные в некоторых ячейках и вы используете функцию ABS, она даст вам #VALUE! ошибка.

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

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

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

• Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (» «), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (» «; A2) +1
• Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го:                                                  ПОИСК (» «; A2, ПОИСК (» «; A2) +1)

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

ПОИСК(» «; A2; ПОИСК(» «; A2) +1) — ПОИСК(» «; A2)

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

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

Аналогичным образом вы можете извлечь текст между любыми другими разделителями:

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

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

Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)

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

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

В таком случае вы можете создать и сохранить код макроса VBA в личной книге макросов и разместить VBA на панели быстрого доступа. Таким образом, в следующий раз, когда вы получите набор данных, в котором вам нужно это сделать, вы просто выберите данные и щелкните значок в QAT…

… И все готово!

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

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

Sub ChangeNegativetoPOsitive()
For Each Cell In Selection
If Cell.Value < 0 Then
Cell.Value = -Cell.Value
End If
Next Cell
End Sub

В приведенном выше коде используется цикл For Next для просмотра каждой выделенной ячейки. Он использует оператор IF, чтобы проверить, является ли значение ячейки отрицательным или нет. Если значение отрицательное, знак меняется на противоположный, а если нет — игнорируется.

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

Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)

• Откройте книгу, в которой у вас есть данные
• Добавьте код VBA в книгу (или в PMW)
• Нажмите на опцию «Настроить панель быстрого доступа» в QAT.
• Щелкните «Дополнительные команды».
• В диалоговом окне «Параметры Excel» щелкните раскрывающийся список «Выбрать команды из».
• Щелкните Макросы. Это покажет вам все макросы в книге (или в личной книге макросов).
• Нажмите кнопку «Добавить».
• Нажмите ОК.

Теперь у вас будет значок макроса в QAT.

Чтобы использовать этот макрос одним щелчком мыши, просто сделайте выбор и щелкните значок макроса.

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

Надеюсь, вы нашли это руководство по Excel полезным.

Измените отрицательное число на положительное в Excel

Расчет процентов в Excel.

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

Часть / Целое = Процент

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

И если в Экселе вы будете вводить формулу с процентами, то можно не переводить в уме проценты в десятичные дроби и не делить величину процента на 100. Просто укажите число со знаком %.

То есть, вместо =A1*0,25   или =A1*25/100 просто запишите формулу процентов  =A1*25%.

Хотя с точки зрения математики все 3 варианта возможны и все они дадут верный результат.

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

• Введите формулу =G2/F2 в ячейку H2 и скопируйте ее на столько строк вниз, сколько вам нужно.
• Нажмите кнопку «Процентный стиль» ( меню «Главная» > группа «Число»), чтобы отобразить полученные десятичные дроби в виде процентов.
• Не забудьте при необходимости увеличить количество десятичных знаков в полученном результате.
• Готово! 🙂

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

На скриншоте ниже вы видите округленный процент доставленных товаров без десятичных знаков.

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

Запишите формулу в самую верхнюю ячейку столбца с расчетами, а затем протащите маркер автозаполнения вниз по столбцу. Таким образом, мы посчитали процент во всём столбце.

Удаление непечатаемых символов

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

Чтобы избавиться от этой проблемы, можно воспользоваться формулой =ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))

Схема работы этой формулы очень проста. С использованием функции ПЕЧСИМВ можно избавиться от непечатаемых символов. В свою очередь, СЖПРОБЕЛЫ избавляет ячейку от невидимых пробелов. А функция ЗНАЧЕН делает текстовые символы числовыми.

Использование значений вставки

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

1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Выберите «Формат ячеек» во всплывающем меню.

1. В открывшемся окне убедитесь, что в качестве числового формата выбран «Общий», и нажмите «ОК».

1. Выберите весь столбец ячеек, который вы хотите преобразовать из текста в числа, щелкните правой кнопкой мыши и выберите «Копировать».

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

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

Отключение проверки на наличие ошибок

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

1. В открытом файле Excel переходим во вкладку «Файл».
2. В левой панели следует перейти в категорию «Параметры».

Переход в категорию «Параметры»

1. В появившемся окне переходим в настройки под названием «Формулы».

Меню «Параметры Excel», категория «Формулы»

1. В комплексе команд «Правила поиска ошибок» нужно убедиться в том, что напротив параметра «Числа, отформатированные как текст или с предшествующим апострофом» установлен флажок активации.

Параметры, которые отключают выведение ошибок в Excel

Количество символов в ячейке в Excel

=ДЛСТР(ячейка_1)

Функция работает только с одним значением.

1. Выделить ту ячейку, где будет показан подсчет.
2. Вписать формулу, указывая ссылку на адрес определенной ячейки.
3. Нажать «Enter».
4. Растянуть результат на другие строки или столбцы.

1. Выделить все значения, во вкладке «Главная» на панели справа найти инструмент «Сумма».
2. Кликнуть по одноименной опции. Рядом (под или с боковой стороны от выделенного диапазона) отобразится результат.

В разбросанных ячейках

1. Установить курсор в желаемом месте.
2. Ввести формулу =ДЛСТР(значение1)+ДЛСТР(значение2)+ДЛСТР(значение3) и т.д.
3. Нажать «Enter».

Объединение текста и чисел

​ секунд и сообщить,​​ в ячейку​ текст прописью. Эта​ способ – кнопка​Сортировка в числовом формате​ОК​Формулы​ English​ операции​ в числовой формат​ чисел вариант, согласитесь,​TEXTJOIN​измените коды числовых​ на числа, введенные​ индекса.​Изменение типа столбца​ >​ помогла ли она​B1​ проблема так же​ на панели инструментов​Сортировка в текстовом формате​

​. Excel умножит каждую​и отключите параметр​//X-Posted in LJ​Найти/Заменить​Вариантом этого приёма может​ неприемлемый.​Объединение текста из​ форматов в формате,​ после применения формата.​Совет:​выберите команду​Текст​ вам, с помощью​формулу =ЛЕВСИМВ(A1;9), а​

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

​ решаема, хоть и​ «Буфер обмена». И​10​ ячейку на 1,​Показать формулы​Николай​. В поле​ быть умножение диапазона​Есть несколько способов решения​ нескольких диапазонах и/или​ который вы хотите​Использование апострофа​ Можно также выбрать формат​Заменить текущие​.​ кнопок внизу страницы.​

​ в ячейку​ не так просто,​ третий – горячие​10​ при этом преобразовав​.​: В любую пустую​Найти​

​ на 1​

1. ​ данной проблемы​ строки, а также​

2. ​ создать.​​Перед числом можно ввести​​Дополнительный​​, и Excel преобразует​​Совет:​

3. ​ Для удобства также​​C1​​ как предыдущая. Существуют​​ клавиши Ctrl+C.​​15​ текст в числа.​С помощью функции ЗНАЧЕН​ ячейку вписать 1.​

4. ​вводим пробел, а​​С помощью инструмента Текст​​С помощью маркера ошибки​ разделитель, указанный между​Для отображения текста и​ апостроф (​

   ​, а затем тип​ выделенные столбцы в​ Чтобы выбрать несколько столбцов,​ приводим ссылку на​формулу =B1+0. В​ специальные надстройки, которые​Выделите диапазон чисел в​100​​Нажмите клавиши CTRL+1 (или​​ можно возвращать числовое​ Выделить ее и​ поле​

​ по столбцам​	​ и тега​	​ каждой парой значений,​
​ чисел в ячейке,​	​’​	​Почтовый индекс​ текст.​ щелкните их левой​ оригинал (на английском​ итоге, в​ добавляются в программу.​ текстовом формате, который​26​
​+1 на Mac).​ значение текста.​	​ скопировать. Выделить все​	​Заменить на​. Этот приём удобно​. Если в левом​ который будет добавляться​ заключите текст в​
​), и Excel будет​,​По завершении нажмите кнопку​	​ кнопкой мыши, удерживая​	​ языке) .​B1​ Затем для изменения​ собираетесь приводить к​1021​ Выберите нужный формат.​Вставьте столбец рядом с​ ячейки с «псевдочислами»​оставляем пустым, далее​ использовать если преобразовать​ верхнем углу ячеек​

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

​ текст. Если разделитель​ двойные кавычки (»​ обрабатывать его как​Индекс + 4​Закрыть и загрузить​ нажатой клавишу​Вам когда-нибудь приходилось импортировать​получим 1.05.2002 в​ отображения чисел вызываются​ числовому. На той​

​100​Можно сделать так, чтобы​ ячейками, содержащими текст.​ и в меню​Заменить все​ нужно один столбец,​

​ виден маркер ошибки​​ пустую текстовую строку,​

• ​ «), или чисел​ текст.​,​​, и Excel вернет​​CTRL​​ или вводить в​​ текстовом формате, а​ функции.​​ же панели «Буфер​​128​ числа, хранящиеся как​ В этом примере​​ Правка — Специальная​​. Если в числе​​ так как если​​ (зелёный треугольник) и​ эта функция будет​ с помощью обратной​

• ​К началу страницы​​Номер телефона​ данные запроса на​.​ Excel данные, содержащие​ в​Еще один способ –​ обмена» нажмите «Вставить»​128​ текст, не помечались​ столбец E содержит​​ вставка — Умножить.​​ были обычные пробелы,​ столбцов несколько, то​ тег, то выделяем​

Примеры

​ эффективно объединять диапазоны.​ косой черты (\)​

​Примечание:​или​​ лист.​​В диалоговом окне​ начальные нули (например,​С1​ использование формул. Он​ / «Специальная вставка».​15​​ зелеными треугольниками. Выберите​​ числа, которые хранятся​ И все будет​ то этих действий​ действия придётся повторять​ ячейки, кликаем мышкой​TEXTJOIN​ в начале.​Мы стараемся как​Табельный номер​Если в дальнейшем ваши​Изменение типа столбца​ 00123) или большие​​получим уже обычную​​ значительно более громоздкий,​

support.office.com>

Преобразование в MS EXCEL ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами)

​ Такая пометка может​ над числовыми элементами.​ CTRL+C

Щелкните первую​Кнопка «столбцы» обычно применяется​ нужно вставить в​ внимание в ячейках​ Номер телефона: 2012-10-17​Пример 3. Самолет вылетел​

​ Подробнее рассмотрим это​​ заголовок, строку, ячейку,​​Преобразовать число в дату​ и точки, и​ же статьи, смотрите​Рассмотрим,​ и формат даты.​ добавляются в программу.​ «Операция» и нажмите​ быть на единичных​ Почему случаются такие​ ячейку в исходном​ для разделения столбцов,​ папку Microsoft Office\Office15​ B3 и B6​ отображается как «17.10.2012».​ согласно расписанию в​ в одном из​ ссылку, т.д.».​ в Excel.​ пробелы).​

​ перечень статей в​как преобразовать число в​ Введем в ячейку​ Затем для изменения​ «ОК» — текст​

​ полях, целом диапазоне,​ проблемы и что​ столбце. На вкладке​ но ее также​ или какой там​ числовые данные записаны​Иногда нужно записать формулу​ 13:40 и должен​ примеров.​Функция ЗНАЧЕН в Excel​Бывает, по разным​В строке «Число​ разделе «Другие статьи​ дату​А1​

​ отображения чисел вызываются​ Excel преобразуется в​ столбце или записи.​ с ними делать,​Главная​​ можно использовать для​​ у вас \Library.​ как текстовые через​​ обычным текстом.​​ был совершить посадку​​​ выполняет операцию преобразования​​ причинам, дата в​ знаков» написали цифру​​ по этой теме».​​Excel​текст «1.05.2002 продажа»,​ функции.​​ число.​​ Программа самостоятельно определяет​ читайте ниже.​щелкните стрелку рядом​ преобразования столбца текста​Затем, открыв лист​ апостроф «’».​

​Поэтому важно научиться управлять​ в конечном пункте​Пример 1. В таблицу​ строки в числовое​ ячейке написана одним​ 1, п.ч

стоит​В Excel можно​и​ в ячейку​Еще один способ –​Ту же операцию можно​ несоответствие между форматом​Ошибка возникает в случае,​ с кнопкой​ в числа. На​​ Excel, войти в​​В колонку D введите​ форматами ячеек.​ перелета в 17:20.​ Excel были автоматически​

​ значение в тех​

​ числом (например, так​​ одна буква. Если​ несколькими способами посчитать​дату в число.​B1​ использование формул. Он​ произвести с помощью​ ячейки и значением,​ если программа по​Вставить​ вкладке​ параметры — надстройки​ формулы, которые отображаются​Попробуйте заполнить табличку, так​ Во время полета​ выгружены некоторые данные​ случаях, где это​ — 20160112). нам​ бы было написано​ стаж, возраст. Про​

​Например, в ячейке​​формулу =ЛЕВСИМВ(A1;9), а​ значительно более громоздкий,​ контекстного меню. Выделив​ которое в нее​ каким-то причинам не​и выберите пункт​Данные​ и в управлении​ в колонке E​ как показано на​ произошел инцидент, в​ о продуктах, содержащихся​ возможно. Данная функция​ нужно придать дате​

excel2.ru>

Не работает ЛЕВСИМВ — причины и решения

Если ЛЕВСИМВ не работает на ваших листах должным образом, это, скорее всего, связано с одной из причин, которые мы перечислим ниже.

1. Аргумент «количество знаков» меньше нуля

Если ваша формула возвращает ошибку #ЗНАЧ!, то первое, что вам нужно проверить, — это значение аргумента количество_знаков. Если вы видите отрицательное число, просто удалите знак минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то намеренно поставит отрицательное число, но человек может ошибиться 🙂

Чаще всего ошибка #ЗНАЧ! возникает, когда этот аргумент получен в результате вычислений, а не записан вручную. В этом случае скопируйте это вычисление в другую ячейку или выберите его в строке формул и нажмите F9, чтобы увидеть результат ее работы. Если значение меньше 0, проверьте на наличие ошибок.

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

ЛЕВСИМВ(A2; ПОИСК(«-«; A2)-1)

Как вы помните, функция ПОИСК в наших примерах вычисляет позицию первого дефиса в исходной строке, из которой мы затем вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, Эксель выдаст ошибку #ЗНАЧ!, потому что нельзя извлечь отрицательное количество букв и цифр:

2. Начальные пробелы в исходном тексте

Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, довольно часто такие неприятные сюрпризы попадаются в самом начале текста. И вы вряд ли заметите, что они там есть, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:

Чтобы избавиться от ведущих пробелов на листах, воспользуйтесь СЖПРОБЕЛЫ (TRIM).

3. ЛЕВСИМВ не работает с датами.

Если вы попытаетесь использовать ЛЕВСИМВ для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся как числа, представляющие количество дней с 1 января 1900 года. То, что вы видите в ячейке, это просто визуальное представление даты. Ее отображение можно легко изменить, применив другой формат.

Например, если у вас есть дата 15 июля 2020 года в ячейке A1 и вы пытаетесь извлечь день с помощью выражения ЛЕВСИМВ(A1;2). Результатом будет 44, то есть первые 2 цифры числа 44027, которое представляет 15 июля 2020г. во внутренней системе Эксель.

Чтобы извлечь определенную часть даты, возьмите одну из следующих функций:  ДЕНЬ(),  МЕСЯЦ() или  ГОД().

Если же ваши даты вводятся в виде текстовых строк, то ЛЕВСИМВ будет работать без проблем, как показано в правой части скриншота:

Вот как можно использовать функцию ЛЕВСИМВ в Excel. 

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

Конвертация числа в текстовый вид

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

Форматирование через контекстное меню

Это самый часто используемый вариант конвертации, поэтому мы решили начать именно с него. Следуйте приведённой ниже инструкции, и у вас всё получится.

1. Первым делом выделите те значения на листе, которые вы хотите конвертировать в текст. На текущем этапе программа воспринимает эти данные как число. Об этом свидетельствует установленный параметр «Общий», который находится на вкладке «Главная».
2. Кликните правой кнопкой мыши по выделенному объекту и в появившемся меню выберите «Формат ячеек».
3. Перед вами появится окошко форматирования. Откройте подраздел «Число» и в списке «Числовые форматы» нажмите на пункт «Текстовый». Далее сохраните изменения клавишей «ОК».
4. По завершении этой процедуры вы можете убедиться в успешном преобразовании, посмотрев на подменю «Число», находящееся на панели инструментов. Если вы всё сделали верно, в специальном поле будет отображаться информация о том, что ячейки имеют текстовый вид.
5. Однако, на предыдущем шаге настройка не заканчивается. Excel ещё не полностью выполнил конвертацию. Например, если вы решите подсчитать автосумму, то чуть ниже высветится результат.
6. Для того чтобы завершить процесс форматирования, поочерёдно для каждого элемента выбранного диапазона проделайте следующие манипуляции: сделайте два клика левой кнопкой мыши по ячейке и нажмите на клавишу «Enter». Двойное нажатие также можно заменить функциональной кнопкой «F2».
7. Готово! Теперь приложение будет воспринимать числовую последовательность как текстовое выражения, а значит, и автосумма этой области данных будет равняться нулю. Ещё одним признаком того, что ваши действия привели к необходимому результату, является наличие зелёного треугольника внутри каждой ячейки. Единственное — эта пометка в ряде случаев может отсутствовать.

Функция МЕСЯЦ в Excel – синтаксис и использование

Microsoft Excel предоставляет специальную функцию МЕСЯЦ() для извлечения месяца из даты. Она возвращает его порядковый номер в диапазоне от 1 (январь) до 12 (декабрь).

Функцию МЕСЯЦ() можно использовать во всех версиях Excel, и ее синтаксис настолько прост, насколько это возможно:

МЕСЯЦ(дата)

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

Например, формула  =МЕСЯЦ(ДАТА(2021;2;5)) возвращает 2, поскольку ДАТА представляет 5 февраля 2021 года.

Формула =МЕСЯЦ(«1-Мар-2020») также работает нормально. Дело в том, что если вы введете такой текст в ячейку, то Excel сразу же распознает его как дату. То же самое происходит и в формуле.

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

=МЕСЯЦ(A1) — возвращает месяц из ячейки A1.

=МЕСЯЦ(СЕГОДНЯ()) — возвращает номер текущего месяца.

На первый взгляд функция МЕСЯЦ() в Excel может показаться простой. Но просмотрите приведенные ниже примеры, и вы удивитесь, узнав, сколько полезных вещей она может делать.

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

Перевести цифровые значения из текстового формата в числовой можно с помощью специальной формулы ЗНАЧЕН.

1. В данном случае нужно создать новый столбик справа от значений, которые будем переводить в другой формат.
2. В первой ячейке нового столбика вводим формулу «=ЗНАЧЕН(D5)». В скобках следует указать адрес ячейки.
3. После применения формулы в первой ячейке следует растянуть ее действие на всю длину столбца, нажав курсором мышки на нижний правый угол ячейки и потянув его вниз.

Использование функции ЗНАЧЕН

1. Преобразованные значения копируем и переносим в столбец с исходными данными. Выделяем столбец с новыми значениями и нажимаем комбинацию клавиш на клавиатуре «Ctrl+С». Таким образом значения сохранились в буфере обмена. Далее переходим в первую ячейку столбца с исходными значениями и, нажав на стрелочку под параметром «Вставка» на «Главной» вкладке, выбираем категорию «Вставить значения».

Вставка преобразованных ячеек

Используем специальные вставки

Не менее простым и эффективным способом преобразования чисел из текстового формата в числовой является использование специальных вставок Эксель. Так, чтобы узнать о том, в каком формате число отображено в данным момент, достаточно при активации ячейки посмотреть на блок инструментов на «Главной» вкладке. Здесь есть параметр, в котором отображается формат ячейки. При стандартных настройках – формат «Общий». При нажатии на стрелку слева появится меню для выбора других форматов.

Выбор формата ячеек на «Главной» вкладке

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