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

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

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

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.

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



2. Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».



3. В открывшемся окне форматирования переходим во вкладку «Число», если оно было открыто в другом месте. В блоке настроек «Числовые форматы» выбираем позицию «Текстовый». Для сохранения изменений жмем на кнопку «OK» в нижней части окна.



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



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



6. Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.



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

Урок: Как изменить формат в Excel

Способ 2: инструменты на ленте

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

1. Выделяем элементы, данные в которых нужно преобразовать в текстовый вид. Находясь во вкладке «Главная» кликаем по пиктограмме в виде треугольника справа от поля, в котором отображается формат. Оно расположено в блоке инструментов «Число».





2. В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».



3. Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

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

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



2. Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».



3. Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:

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

   Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».

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

   В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.

   После того, как все требуемые параметры введены, щелкаем по кнопке «OK».



4. Как видим, значение первого элемента заданного диапазона отобразилось в ячейке, которую мы выделили ещё в первом пункте данного руководства. Для того, чтобы перенести и другие значения, нужно скопировать формулу в смежные элементы листа. Устанавливаем курсор в нижний правый угол элемента, который содержит формулу. Курсор преобразуется в маркер заполнения, имеющий вид небольшого крестика. Зажимаем левую кнопку мыши и протаскиваем по пустым ячейкам параллельно диапазону, в котором находятся исходные данные.



5. Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».



6. Далее, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимаем выделение с области, которая содержит формулы. Кликаем по ней правой кнопкой мыши. Происходит запуск контекстного списка действий. Выбираем в нем позицию «Специальная вставка». Среди вариантов действий в открывшемся списке выбираем «Значения и форматы чисел».
   

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



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

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

Урок: Мастер функций в Excel

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

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

Способ 1: преобразование с помощью значка об ошибке

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

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



2. Открывается список действий. Выбираем в нем значение «Преобразовать в число».



3. В выделенном элементе данные тут же будут преобразованы в числовой вид.

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

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



2. Открывается уже знакомый нам список. Как и в прошлый раз, выбираем позицию «Преобразовать в число».

Все данные массива будут преобразованы в указанный вид.

Способ 2: конвертация при помощи окна форматирования

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

1. Выделяем диапазон, содержащий цифры в текстовом варианте. Кликаем правой кнопкой мыши. В контекстном меню выбираем позицию «Формат ячеек…».



2. Выполняется запуск окна форматирования. Как и в предыдущий раз, переходим во вкладку «Число». В группе «Числовые форматы» нам нужно выбрать значения, которые позволят преобразовать текст в число. К ним относится пункты «Общий» и «Числовой». Какой бы из них вы не выбрали, программа будет расценивать цифры, введенные в ячейку, как числа. Производим выбор и жмем на кнопку. Если вы выбрали значение «Числовой», то в правой части окна появится возможность отрегулировать представление числа: выставить количество десятичных знаков после запятой, установить разделителями между разрядами. После того, как настройка выполнена, жмем на кнопку «OK».



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

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

Способ 3: конвертация посредством инструментов на ленте

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

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



2. Далее прощелкиваем уже не раз описанным нами способом каждую ячейку преобразуемой области с применением клавиш F2 и Enter.

Значения в диапазоне будут преобразованы из текстовых в числовые.

Способ 4: применение формулы

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

1. В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.



2. Жмем на клавишу Enter, после чего получаем готовое преобразованное значение. Для того, чтобы применить данную формулу для всех других ячеек диапазона, используем маркер заполнения, который ранее был применен нами для функции ТЕКСТ.



3. Теперь мы имеем диапазон, который заполнен значениями с формулами. Выделяем его и жмем на кнопку «Копировать» во вкладке «Главная» или применяем сочетание клавиш Ctrl+C.



4. Выделяем исходную область и производим щелчок по ней правой кнопкой мыши. В активировавшемся контекстном списке переходим по пунктам «Специальная вставка» и «Значения и форматы чисел».



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

Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1». Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)

Урок: Как сделать автозаполнение в Excel

Способ 5: применение специальной вставки

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

1. В любую пустую ячейку на листе вписываем цифру «1». Затем выделяем её и жмем на знакомый значок «Копировать» на ленте.



2. Выделяем область на листе, которую следует преобразовать. Кликаем по ней правой кнопкой мыши. В открывшемся меню дважды переходим по пункту «Специальная вставка».



3. В окне специальной вставки выставляем переключатель в блоке «Операция» в позицию «Умножить». Вслед за этим жмем на кнопку «OK».



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

Способ 6: использование инструмента «Текст столбцами»

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

1. Выделяем фрагмент листа, содержимое которого нужно преобразовать. Переходим во вкладку «Данные». На ленте инструментов в блоке «Работа с данными» кликаем по значку «Текст по столбцам».



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



3. Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».



4. А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».



5. Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».



6. Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».



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

Способ 7: применение макросов

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

1. Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».



2. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:

   
Sub Текст_в_число()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub


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



3. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».



4. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».



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

Урок: Как создать макрос в Экселе

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

Преобразование чисел из текстового формата в числовой

​Смотрите также​ числу 1. Если​ начальная_позиция функции ПСТР.​ с текстовым представлением​Sub Conv() With​​ невидимых символов. Удалим​ ячейках отформатированы как​ вставили формулу функцией​ цифру «Количество». Это​ in cells, see​

1. Выделите столбец

​ случае подойдет вариант​ выбрать, но можно​.​ он предоставляет возможность​ узнать количество именно​Главная​ в виде текста.​Числа, хранящиеся как текст,​ данный аргумент принимает​ Для упрощения, вместо​ дат, из которых​ ActiveSheet.UsedRange .Replace «,»,».»​ их с помощью​ числа или как​ «Автосумма». В этой​

2. Нажмите эту кнопку

​ число заполненных выделенных​ Count characters in​ с функцией, а​ и оставить его​Открывается окно аргументов функции.​ посчитать только те​ строк, выделяем только​​щелкните​​ Столбец F является​​ могут приводить к​​ дробное число из​

3. Нажмите кнопку «Готово»

​ поиска номера последней​ будут выделены номера​ arr = .Value​ формулы, которую введем​ текст. Числовые форматы​ формуле указан диапазон​​ ячеек. Пустые ячейки​​ cells.​ если задача стоит​

4. Задайте формат

​ по умолчанию. После​​ Ставим курсор в​ строки, в которых​

​ один столбец в​​Вставить​ новым столбцом.​ непредвиденным результатам. Выделите​ диапазона допустимых значений,​​ позиции (например, с​​ дней;​​ .NumberFormat = «General»​​ во вспомогательный столбец.​​ выравниваются по правому​​ до первой пустой​

Другие способы преобразования

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

​ так Excel не​If you want to​ подсчитать строки, отвечающие​

1. Вставьте новый столбец

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

2. Примените функцию ЗНАЧЕН

​ помощью функции ДЛСТР)​1 – номер начальной​​ .Value = arr​​ Функция ПЕЧСИМВ удаляет​ краю, текстовые –​ ячейке столбца. =СЧЁТ(A4:A6)​ считает. Но, если​ know how many​ определенному условию, то​ условия завершена, жмем​​«Массив»​​ в некоторых случаях​

3. Наведите указатель мыши

​ строке состояния после​Специальная вставка​ нового столбца введите​ кнопку​ усечена;​ указано число 100,​ позиции символа извлекаемой​ End With End​

4. Щелкните и перетащите вниз

​ непечатаемые знаки. СЖПРОБЕЛЫ​ по левому.​ Мы адрес ячейки​ нужно посчитать строку,​ cells have data,​ тут на помощь​ на кнопку​. Выделяем на листе​ нужно произвести подсчет​ слова​.​=ЗНАЧЕН()​, чтобы выбрать​число_знаков – обязательный аргумент,​ которое в данном​​ подстроки (первый символ​​ Sub​ – лишние пробелы.​​Когда при импорте файлов​​ А4 исправляем на​​ в которой в​​ see Use COUNTA​​ придет условное форматирование​​«OK»​

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

​ тот диапазон, количество​ всех элементов в​«Количество»​Щелкните​и укажите в​ вариант преобразования. Если​ принимающий значение из​

1. ​ примере гарантированно превышает​ в исходной строке);​Читайте так же: как​ Функция ЗНАЧЕН преобразует​​ или сбое в​​ адрес ячейки А1.​ столбце А нет​

2. ​ to count cells​ с последующей фильтрацией.​

3. ​.​ строк в котором​ совокупности, включая и​

4. ​слева от кнопок​​умножить​​ скобках ссылку на​​ эта кнопка недоступна,​​ диапазона неотрицательных чисел,​​ количество знаков в​​2 – номер последней​

5. ​ перевести число и​​ текстовый формат в​​ Excel числовой формат​​ Получится так.​​ значений, а есть​ that aren’t blank.​Автор: Максим Тютюшев​Как видим, после этих​

6. ​ нужно подсчитать. После​​ пустые. В этом​ переключения режимов отображения​

Отключение зеленых треугольников

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

См. также:

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

support.office.com

Подсчет числа строк в Microsoft Excel

​ОК​ которые хранятся в​ действия.​ в символах возвращаемой​В результате расчетов получим:​ подстроки.​ Excel.​Применение инструмента «Текст по​

​ левом верхнем углу​Посчитать, если выделено цветом​

Определение количества строк

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

Способ 1: указатель в строке состояния

​ виде текста. В​Выделите столбец с такими​ подстроки. Если в​Пример 3. В таблице​Аналогичным способом выделим номера​Скачать примеры преобразования текста​ столбцам». Выделяем столбец​ ячеек появляется зеленый​ – формула Excel​ столбца В. Главное,​ состояния, щелкните строку​ можно оперативнее обеспечивать​ выбранным цветом. Выделяем​ в поле окна​ЧСТРОК​ в выделенном диапазоне.​ ячейку на 1,​ данном примере это​ ячейками. Если вы​ качестве этого аргумента​ содержатся данные о​​ месяца и годы​​ в число.​ с текстовыми аргументами,​ треугольничек. Это знак​.​ чтобы были выделены​

​ состояния правой кнопкой​ вас актуальными справочными​ весь диапазон значений.​ аргументов, жмем на​. Её синтаксис выглядит​Правда, случается и такое,​ при этом преобразовав​ ячейка​ не хотите преобразовывать​ передано число 0​ сотрудниках в столбцах​ для заполнения соответствующих​Таким образом, возникающую при​ которые нужно преобразовать​ ошибки. Ошибка также​Можно написать такую​ ячейки всех нужных​​ мыши и выберите​​ материалами на вашем​ Находясь во все​ кнопку​ следующим образом:​ когда в таблице​ текст в числа.​E23​ весь столбец, можно​ (нуль), функция ПСТР​ ФИО и дата​ столбцов с учетом,​ импорте или копировании​ в числа. На​ возникает, если перед​

​ формулу. =СЧЁТ(A1:A6) Эта​ строк. Например, так.​ элемент, который необходимо​ языке. Эта страница​ в той же​«OK»​=ЧСТРОК(массив)​ нет полностью заполненных​Нажмите клавиши CTRL+1 (или​.​ выбрать одну или​ вернет пустую строку.​ рождения. Создать столбец,​ что номер месяца​ числовых данных ошибку​​ вкладке «Данные» находим​​ числом поставить апостроф.​ формула считает только​

Способ 2: использование функции

​Здесь выделили четыре строки.​ просмотреть или удалить.​ переведена автоматически, поэтому​ вкладке​.​Её можно вбить в​ столбцов, при этом​+1 на Mac).​Теперь нужно заполнить формулу​ несколько ячеек. Ячейки​ Если аргумент задан​ в котором будет​ начинается с 4-го​ легко устранить. Преобразовать​ кнопку «Текст по​Способов преобразования текста в​​ ячейки с числами,​​Третий способ​Извлечем число из начала​

​ ее текст может​

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

​ отображаться фамилия сотрудника​ символа в каждой​ текст в число​​ столбцам». Откроется окно​​ число существует несколько.​

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

​ одном и том​ символов в строке,​ и его возраст​ строке, а год​ можно разными способами​​ «Мастера». Нажимаем «Далее».​​ Рассмотрим самые простые​

1. ​ – не считает.​Можно в таблице​ из строки «123Филатова»​ грамматические ошибки. Для​«Сортировка и фильтр»​​ строк в предварительно​​ в качестве аргумента​ этом случае, если​ числа, хранящиеся как​

   

2. ​ указатель на правый​​ же столбце, иначе​​ будет возвращена вся​​ в формате «Иванов​​ – с 7-го.​​ (с помощью макросов​​ На третьем шаге​​ и удобные.​​Шестой способ.​​ фильтром отфильтровать нужные​​ получим «123».​ нас важно, чтобы​​в группе инструментов​​ указанную ячейку. Теперь​

   

3. ​«Массив»​ мы выделим только​ текст, не помечались​​ нижний угол ячейки,​​ этот процесс не​ часть строки начиная​ – 27».​ Используем следующие формулы:​ и без них).​ обращаем внимание на​Использовать меню кнопки «Ошибка».​Чтобы посчитать ячейки​ данные. Затем выделить​​Пусть текстовая строка 123Филатова​​ эта статья была​

   

4. ​«Редактирование»​ этот итог будет​подставить координаты диапазона,​ один столбец, то​ зелеными треугольниками. Выберите​ чтобы он принял​ будет работать. (Если​ с указанной вторым​Вид исходной таблицы:​

​=ПСТР(A2:A10;4;2)​​ Сделать это просто​

Способ 3: применение фильтра и условного форматирования

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

1. ​Файл​ вид знака плюс​ такие ячейки есть​

   

2. ​ аргументом позиции. В​​Для возврата строки с​​=ПСТР(A2:A10;7;4)​ и быстро. А​​Последний способ подходит в​​ ячейки с ошибкой​​ условию, используем функцию​​ столбце. В строке​​A1​​ вас уделить пару​ выбираем пункт​ области постоянно, если​ произвести подсчет.​​ которых именно в​​>​ (+).​ в нескольких столбцах,​ дробных числах, используемых​

   

3. ​ фамилией и текущим​Вид заполненной таблицы данных:​ впоследствии с числовыми​ том случае, если​ слева появляется соответствующий​ «СЧЁТЕСЛИ». Примеры формул​ состояния будет видно​. Чтобы извлечь число​ секунд и сообщить,​«Фильтр»​ вы не решите​Для вывода результата на​ той колонке нет​Параметры​Щелкните и перетащите вниз,​​ см. раздел «Другие​​ в качестве данного​

   

4. ​ возрастом используем следующую​Таким образом нам удалось​ аргументами производятся все​ значения находятся в​ значок. Это и​ смотрите в статье​ количество строк. Помним,​ 123, расположенное слева,​​ помогла ли она​​.​​ удалить его вручную.​​ экран достаточно будет​​ значений, не попадут​​>​ чтобы добавить формулу​​ способы преобразования» ниже.)​​ аргумента, дробная часть​

   

5. ​ формулу:​ разрезать на части​ необходимые операции.​ одном столбце.​ есть кнопка «Ошибка».​ «Функция «СЧЁТЕСЛИ» в​ что пустые ячейки​ запишем формулу массива:​​ вам, с помощью​​После этого в заглавиях​Урок:​ нажать кнопку​ в расчет. Поэтому​

   

6. ​Формулы​ в другие ячейки.​Кнопка «столбцы» обычно применяется​ усекается.​Функция ПСТР возвращает часть​ текст в ячейках​Функция ПСТР в Excel​​​​ Если навести на​ Excel» здесь.​ не считаются в​=1*ПСТР(A1;1;ПОИСКПОЗ(ИСТИНА;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0)-1)​ кнопок внизу страницы.​ столбцов появляется значок​Мастер функций в Экселе​Enter​

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

​ После этого вы​​ для разделения столбцов,​Функция ПСТРБ имеет схожий​

​ строки до символа​ столбца A. Удалось​ предназначена для выделения​Преобразовать числа, сохраненные как​ нее курсор, появится​Седьмой способ​ строке состояния. Получится​Если число расположено в​ Для удобства также​ фильтра. Кликаем по​Но бывают случаи, когда​.​ конкретный столбец, а​Числа в текстовом формате​ можете использовать новый​ но ее также​ синтаксис:​

​ пробела, позиция которого​

lumpics.ru

Подсчет числа строк или столбцов

​ отдельно каждую дату​​ подстроки из строки​ текст, в числа​ знак раскрывающегося меню​.​ так.​ конце или середине​ приводим ссылку на​ нему в том​ нужно подсчитать не​Причем подсчитываться будут даже​ затем, зажав кнопку​.​ столбец или скопировать​ можно использовать для​=ПСТРБ(текст;начальная_позиция;число_байтов)​ определяется функцией НАЙТИ.​ разделить на несколько​ текста, переданной в​ можно с помощью​ (черный треугольник). Выделяем​Посчитать строки, выделенные условным​

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

​ качестве первого аргумента,​ макроса.​ столбец с числами​ форматированием​Быстро выделить столбец​ работать не будет.​

​ языке) .​

​ проведено форматирование. В​ а только те,​ диапазона. Стоит заметить,​кликаем по заполненным​Удаление пробелов и​ значения в исходный​ в числа. На​число_байтов – обязательный аргумент,​ сотрудника используется функция​ день, месяц и​ и возвращает требуемое​Есть набор значений, сохраненных​

​ в текстовом формате.​​или​

​ можно так. Выделяем​

• ​Более подробно об извлечении​Если вам нужно быстро​ открывшемся меню выбираем​ которые отвечают определенному​ что в отличие​

• ​ ячейкам, в тех​ непечатаемых знаков из​ столбец. Вот как​ вкладке​ принимающий целые числа​ ДОЛЯГОДА, полученное значение​

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

support.office.com

Извлекаем в MS EXCEL число из начала текстовой строки

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

​ строчках, которые оказались​ текста​​ это сделать: Выделите​​Данные​ из диапазона от​ которой усекается до​
​​

​ с заданной позиции.​Чтобы вставить макрос, на​ «Ошибка». Нажимаем «Преобразовать​ Excel​

​ Нажимаем на клавишу​ в статье Извлекаем​ с данными, выберите​«Фильтр по цвету»​ этом случае на​ если вы выделите​ пустыми в выделенной​

excel2.ru

Количество выделенных строк в Excel.

​ПЕЧСИМВ (функция ПЕЧСИМВ)​ ячейки с новой​нажмите кнопку​​ 1 до N,​ ближайшего меньшего целого,​​Пример 2. В столбце​Один символ в языках​ вкладке «Разработчик» находим​ в число».​.​ F8 и ставим​
​ число из конца​​ все ячейки в​. Далее кликаем по​ помощь придет условное​ область, включающую несколько​ колонке. При этом​При работе в Excel​ формулой. Нажмите клавиши​Текст по столбцам​ где N –​
​ чтобы получить число​​ таблицы хранятся текстовые​​ с однобайтовой кодировкой​ редактор Visual Basic.​​Применить любые математические действия.​
​Можно применить фильтр​ курсор в нижнюю​ текстовой строки. Там​ первом столбце с​ тому цвету, которым​ форматирование и последующая​ столбцов, то оператор​ выделяем не более​ иногда нужно подсчитать​ CTRL+C. Щелкните первую​.​ число байтов в​ полных лет. Функция​ записи с наименованием​ соответствует 1 байту.​ Открывается окно редактора.​ Подойдут простейшие операции,​ по цвету.​ ячейку столбца. Нажимаем​​ же можно скачать​
​ этими данными (это​​ залиты отформатированные ячейки,​
​ фильтрация​ будет считать исключительно​ одной ячейки на​ количество строк определенного​ ячейку в исходном​Остальные шаги мастера нужны​ исходной строке, характеризующий​ ТЕКСТ преобразует полученное​ и маркой товаров.​ При работе с​ Для добавления кода​​ которые не изменяют​
​Затем выделить ячейки и​ левую мышку.​ файл примера.​ не обязательно будет​ удовлетворяющие условию.​Выделяем диапазон, по которому​ строчки.​ строку. Таким образом,​
​ диапазона. Сделать это​ столбце. На вкладке​ для разделения текста​ количество байт в​ значение в текстовую​
​ Разделить имеющиеся строки​ такими языками результаты​ нажимаем F7. Вставляем​ результат (умножение /​ посмотреть количество в​
​Или нажимаем сочетание​​Говорят, что нет​
​ столбец A). Просто​Как видим, не отмеченные​​ будет производиться проверка​​Пользователям, у которых небольшой​ в строке состояния​ можно несколькими способами.​Главная​ на столбцы. Так​ возвращаемой подстроке.​ строку.​ на подстроки с​​ функций ПСТР и​ следующий код:​ деление на единицу,​ строке состояния.​ клавиш «Ctrl» +​ специальной функции без​ щелкните заголовок столбца.​ цветом ячейки после​ на выполнение условия.​ опыт работы с​ будет отображено количество​ Разберем алгоритм выполнения​щелкните стрелку рядом​ как нам нужно​Примечания:​Для соединения (конкатенации) полученных​
​ наименованием и маркой​
​ ПСТРБ (возвращает подстроку​Sub Conv() With ActiveSheet.UsedRange​​ прибавление / отнимание​
​Здесь ячейки выделены условным​ «А». Активная ячейка,​ макросов​ В строке состояния​ данных действий были​Переходим во вкладку​
​ формулами в Экселе,​
​ всех строчек в​ этой процедуры, используя​ с кнопкой​ только преобразовать текст,​Функция ПСТР вернет пустую​ строк используются символы​ соответственно и записать​
​ из строки на​​ arr = .Value​
​ нуля, возведение в​ форматированием. Отфильтровали по​​ при этом, находится​​посчитать количество выделенных строк​ в нижнем правом​​ спрятаны. Просто выделяем​
​«Главная»​ проще работать с​
​ выделенном диапазоне, в​ различные варианты.​Вставить​
​ нажмите кнопку​ строку, если в​ «&». В результате​ полученные значения в​ основе количества заданных​ .NumberFormat = «General»​
​ первую степень и​ цвету. В строке​ в таблице.​ в Excel​ углу окна Excel​ оставшийся диапазон ячеек​. На ленте в​ данным оператором через​

excel-office.ru

Преобразование текста в число в ячейке Excel

​ которых хотя бы​Скачать последнюю версию​и выберите пункт​Готово​ качестве аргумента начальная_позиция​ вычислений получим:​ соответствующие столбцы таблицы.​ байт) не отличаются.​ .Value = arr​ т.д.).​ состояния написано, что​О других сочетаниях​

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

​. Неправда. Есть много​ отобразится количество строк.​ и смотрим на​ блоке инструментов​Мастер функций​ одна ячейка заполнена.​ Excel​Специальная вставка​

​, и Excel преобразует​ было передано число,​Функция имеет следующую синтаксическую​Вид таблицы данных:​ Если на компьютере​ End With End​Добавить специальную вставку. Здесь​ выделено 3 строки​ клавиш, чтобы выделить​ других приемов, чтобы​

​Так же вычисляется количество​ показатель​«Стили»​.​

1. ​Но бывают и ситуации,​Существует довольно большое количество​>​ ячейки.​ превышающее количество символов​ запись:​Для заполнения столбца «Наименование»​ используется двухбайтовый язык,​ Sub​ также применяется простое​ (ячейки).​ ячейки, смотрите в​ посчитать выделенные строки,​ столбцов, но в​«Количество»​
2. ​жмем на кнопку​Выделяем ячейку, в которую​ когда вы выделяете​ способов определения количества​Значения​Нажмите клавиши CTRL+1 (или​ в исходной строке.​=ПСТР(текст;начальная_позиция;число_знаков)​ используем следующую формулу:​
3. ​ каждый символ при​Чтобы он «заработал», нужно​ арифметическое действие. Но​Как преобразовать формат​ статье «Сочетание клавиш​ ячейки Excel. Было​ этом случае щелкните​в строке состояния,​«Условное форматирование»​ будет производиться вывод​ заполненные ячейки в​ строк. При их​.​+1 на Mac).​Если в качестве аргумента​Описание аргументов:​=ПСТР(A2;1;НАЙТИ(» «;A2))​ использовании ПСТРБ будет​ сохранить. Но книга​ вспомогательный столбец создавать​ данных из других​
4. ​ в Excel».​ бы желание, а​ область выделения строки,​ как и при​. Выбираем пункт​ готового итога подсчета​ строках, а отображение​ использовании применяются различные​Если указанные выше действия​ Выберите нужный формат.​ начальная_позиция было передано​текст – обязательный для​Функция НАЙТИ возвращает номер​
5. ​ считаться за два.​ Excel должна быть​ не нужно. В​ программ в формат​Четвертый способ​ способы найдем. )))​ которая находится слева​ решении проблемы первым​«Правила выделения ячеек»​ элементов. Жмем на​ количества на панели​ инструменты. Поэтому нужно​

​ не дали результатов,​Примечание:​ значение 1, а​ заполнения аргумент, принимающий​

​ позиции символа пробела​

Макрос «Текст – число»

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

​.​Один из способов​

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

​ Если вы по-прежнему видите​ аргумент число_знаков определен​ ссылку на ячейку​ « » в​ корейский, японский и​ с поддержкой макросов.​

​ цифру 1. Скопировать​ формулы считали такие​Посчитать количество строк​узнать количество выделенных​Пример результата:​

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

​ просматриваемой строке, который​

​ китайский.​Теперь возвращаемся на страницу​

​ ячейку в буфер​ числа, смотрите в​ можно с помощью​ строк – это​Если вы выберете всю​

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

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

​ с цифрами. Выделяем​ обмена (с помощью​

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

exceltable.com

Пример функция ПСТР для разделения текста на части в Excel

​ аргумента число_знаков функции​ таблицы содержатся даты,​ столбец с данными.​ кнопки «Копировать» или​ в число Excel».​. Выделяем ячейку, в​ строк после фильтра.​ Excel посчитает только​

Примеры использования функции ПСТР в Excel

​ конкретному условию.​«Больше…»​ формул.​ Для её включения​ поставленную задачу в​ текста.​Показать формулы​ исходной строке, функция​ которой будет извлечена​ ПСТР. В результате​ записанные в виде​ Нажимаем кнопку «Макросы».​ сочетания клавиш Ctrl​При импорте файлов или​ которой будет стоять​ Смотрите об этом​ ячейки, которые содержат​Урок:​

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

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

​ текстовых строк. Записать​

​ В открывшемся окне​ + C). Выделить​ копировании данных с​ цифра количества строк.​

​ способе статью «Порядковый​

• ​ данные. При выборе​Условное форматирование в Эксель​ случаев выбор может​Мастера функций​ мыши по панели​
• ​ это посмотреть количество​ которой нет этой​Формулы​ строку целиком.​
• ​ начиная с указанной​Для заполнения столбца «Марка»​ отдельно в соседних​

​ – список доступных​ столбец с редактируемыми​ числовыми значениями часто​ Нажимаем стрелку у​ номер строк по​ блока ячеек подсчитывается​Урок:​ быть остановлен и​. В поле​ состояния и в​

​ в строке состояния.​

​ проблемы, введите в​

​и отключите параметр​

​Если аргумент начальная_позиция был​ позиции первого символа;​ используем следующую формулу​ столбцах номер дня,​ для данной книги​ числами. В контекстном​ возникает проблема: число​ кнопки «Автосумма» на​ порядку после фильтра​

​ количество выбранных ячеек.​

Как вырезать часть текста ячейки в Excel?

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

​ месяца и год,​

​ макросов. Выбираем нужный.​ меню кнопки «Вставить»​

​ преобразуется в текст.​

​ закладке Excel «Главная»​ в Excel»​ Если выбранная строка​ в Excel​Открывается окно, в котором​устанавливаем позицию​ галочку напротив значения​ выделяем нужный диапазон.​

​1​.​ диапазона отрицательных чисел​

​ принимающий целые числа​

​=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)​ выделенные из представленных​ Жмем «Выполнить».​ нажать «Специальная вставка».​ В результате формулы​ и выбираем функцию​Второй способ​ или столбец содержит​Как видим, существует несколько​ задается условие. В​«Ссылки и массивы»​«Количество»​ При этом важно​и нажмите клавишу​С помощью функции ЗНАЧЕН​ или 0 (нулем),​ из диапазона от​Функция НАЙТИ возвращает позицию​

​ дат.​

Как посчитать возраст по дате рождения в Excel?

​Цифры переместились вправо.​ В открывшемся окне​ не работают, вычисления​ «Число».​–​ только одну ячейку​ способов узнать количество​ левом поле укажем​или​. Теперь численность выделенных​

​ учесть, что система​

​ ВВОД.​ можно возвращать числовое​ функция ПСТР вернет​ 1 до N,​

​ символа пробела. К​Вид исходной таблицы данных:​Следовательно, значения в ячейках​ установить галочку напротив​ становятся невозможными. Как​Эта функция считает только​посчитать количество выделенных ячеек​ с данными, строка​ строчек в выделенном​ число, ячейки, включающие​«Полный алфавитный перечень»​ строк будет отображаться.​ считает каждую ячейку​Нажмите клавиши CTRL+C, чтобы​

​ значение текста.​ код ошибки #ЗНАЧ!.​ где N –​ полученному числу прибавляется​

Особенности использования функции ПСТР в Excel

​Для заполнения номера дня​ «стали» числами.​

​ «Умножить». После нажатия​

​ это быстро исправить?​

• ​ заполненные строки. Если​ в столбце Excel​ состояния останется пустой.​ фрагменте. Каждый из​ в себя значение​. Ищем значение​Но, вышеуказанный способ не​ с данными за​ скопировать ячейку.​Вставьте столбец рядом с​
• ​Если аргумент число_знаков задан​ длина строки, из​ единица для нахождения​ используем следующую формулу​Если в столбце встречаются​ ОК текстовый формат​ Сначала посмотрим, как​ есть пустые ячейки​.​Примечание:​ этих способов уместно​ больше которого, окрасятся​«ЧСТРОК»​ позволяет зафиксировать результаты​ отдельную единицу. Поэтому,​Выделите ячейки с числами,​
• ​ ячейками, содержащими текст.​ отрицательным числом, результатом​ которой требуется извлечь​ позиции первого символа​ (использовать в качестве​ аргументы с определенным​ преобразуется в числовой.​ исправить ошибку без​ в столбце, то​Выделяем ячейки в​ ​ применять для определенных​ определенным цветом. В​, выделяем его и​ подсчета в конкретной​ чтобы не произошло​ которые сохранены как​ В этом примере​ выполнения функции ПСТР​ подстроку заданного размера.​ названия марки продукта.​

​ формулы массива):​ числом десятичных знаков​

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

​ макросов.​

• ​ в формуле исправляем​ столбце и смотрим​.​ целей. Например, если​ правом поле существует​ жмем на кнопку​ области на листе.​ двойного подсчета, так​ текст.​

​ столбец E содержит​

1. ​ будет код ошибки​ Начальная позиция символа​ Итоговое значение используется​Описание аргументов:​ (например, 3,45), можно​ числовой формат не​
2. ​Excel помогает пользователю сразу​ адрес первой ячейки​ внизу листа Excel​If you need to​ нужно зафиксировать результат,​ возможность этот цвет​«OK»​ К тому же,​ как нам нужно​На вкладке​
3. ​ числа, которые хранятся​ #ЗНАЧ!.​ в строке соответствует​ в качестве аргумента​A2:A10 – диапазон ячеек​ использовать другой макрос.​
4. ​ распознается программой из-за​ определить, значения в​ диапазона. Например, мы​ в строке состояния​ count the characters​

exceltable.com

​ то в этом​