Как сохранить число как текст в excel

Как сохранить число как текст в excel

= Мир MS Excel/Статьи об Excel

• 1
• 2
• 3

Приёмы работы с книгами, листами, диапазонами, ячейками [6]

Приёмы работы с формулами [13]

Настройки Excel [3]

Инструменты Excel [4]

Интеграция Excel с другими приложениями [4]

Форматирование [1]

Выпадающие списки [2]

Примечания [1]

Сводные таблицы [1]

Гиперссылки [1]

Excel и интернет [1]

Excel для Windows и Excel для Mac OS [2]

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

В этой статье рассматриваются причины появления таких проблем и различные способы их устранения

Причина первая . Число сохранено как текст

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

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

• С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки ( зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число

• С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами — нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) — в поле Найти вводим , (запятую) — в поле Заменить на тоже вводим , (запятую) — Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 — Enter

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

• С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку — выполняем команду Копировать — выделяем диапазон с проблемными числами — Специальная вставка — Сложить — ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

• С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные — Текст по столбцам — Готово

• С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных

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

Приведу два примера макросов:

1) умножение на 1

2) текст по столбцам

Причина вторая . В записи числа присутствуют посторонние символы.

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

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;" ";"")

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);"")

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);"");" ";"")

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

В excel число сохранить как текст

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

​Смотрите также​: Работаю с 1С,​’номер последнего заполненого​ всегда можно открыть​ Формулы — Числа,​ End Sub​ неприемлемый.​ нажмите сочетание клавиш​ выше. Вместо этого​Столбец или строку целиком​Больше или меньше ячеек,​ клавишами со стрелками,​ формата в числовой,​ на​ относится пункты​

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

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

​Одной из частых задач,​ поэтому тоже иногда​ ряда в первом​ вкладку​ отформатированные как текст​2) текст по столбцам​Есть несколько способов решения​ CTRL+HOME.​ можно просто применить​Щелкните заголовок сроки или​ чем имеется в​ чтобы перейти к​ то имеется смысл​«-1»​«Общий»​ группе​ в текстовый вид.​ с которыми сталкиваются​ сталкиваюсь с этой​ столбце​Разрабочик — Макросы (Developer​ или с предшествующим​

​Sub conv1() Selection.TextToColumns Selection.NumberFormat​ данной проблемы​Чтобы выделить последнюю ячейку,​ числовой формат и​

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

​ столбца.​ активном выделении​ нужной ячейке.​ в этих целях​

​. Можно применять любое​и​«Буфер обмена»​ Находясь во вкладке​ пользователи программы Эксель,​​ проблемой. Она возникает​​iLastRow = Cells(Rows.Count,​ — Macros)​​ апострофом​​ = «#,##0.00» End​С помощью маркера ошибки​ содержащую данные или​ получить тот же​1. Заголовок строки​Удерживая нажатой клавишу SHIFT,​Диапазон ячеек​ записать специальный макрос,​

​Ячейки до последней используемой​​ как текста в​ первую ячейку, а​

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

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

​ сохранять в файл​Cells(iLastRow + 1,​- и моментально​ ввести данные, чтобы​​ символами являются пробелы.​​ тег, то выделяем​ ячейки листа (нижний​ левом верхнем углу​ затем нажав сочетание​ диапазон между активная​ клавишу SHIFT, нажимайте​​ своей версии Экселя​​ и т.д.)​

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

​ по ней правой​«Число»​ знает четкого алгоритма​ то всё нормально​End Sub​ полноценные.​​ в силу. Проще​​ как внутри числа​ по тегу и​Выделите первую ячейку, а​ зеленый индикатор ошибки.​ (СТРЕЛКА ВПРАВО или​ ячейкой.​ чтобы расширить выделение.​ панель разработчика, если​Как сделать автозаполнение в​ кнопку. Если вы​ кнопкой мыши. Происходит​.​ действий. Давайте разберемся,​ и цифры, как​В макросе мы​Также можно добавить этот​

​ всего это сделать,​ в качестве разделителя​ выбираем вариант​ затем нажмите клавиши​ В этом случае​​ СТРЕЛКА ВЛЕВО —​​Чтобы отменить выделение ячеек,​Кроме того, можно выделить​

​ для строк, СТРЕЛКА​

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

​ обе задачи различными​​ формате.​​ строки последней заполненной​ книгу макросов, чтобы​ и нажав последовательно​ до/после числа. В​С помощью операции Найти/Заменить​ выделенную область до​ формат.​ ВВЕРХ или СТРЕЛКА​

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

​ на предыдущий с​ части окна появится​«Специальная вставка»​​.​​Скачать последнюю версию​

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

​ между разрядами. После​.​

​ двойным щелчком левой​​ как ей рассматривать​

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

​ см. прикрепленный файл​ такую структуру:​ текст, поэтому не​Enter​. В поле​Ctrl+h​

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

​ CTRL+SHIFT+HOME, чтобы расширить​Выполните следующие действия​ будет выделена строка​. (Чтобы просто избавиться​ клавишу F8.​«Код»​ на листе вписываем​ того, как настройка​Если же пользователь желает​ кнопки мыши или​ то или иное​Sh_Alex​Cells(номер_ряда, номер_столбца)​ будет работать группировка​. Также вместо​Найти​(либо находим на​ выделение до начала​Отдельную ячейку​ или столбец до​ от индикатора ошибки​Большой диапазон ячеек​.​ цифру​ выполнена, жмем на​ заменить данные исходного​ нажатием клавиши​ выражение. Например, даже​: Дело может быть​Саша Уткин​ и сортировка. Решения​F2​вводим пробел, а​ вкладке​ листа.​Щелкните ячейку или воспользуйтесь​ последней заполненной ячейки.​ без преобразования, выберите​Щелкните первую ячейку диапазона,​

​Запускается стандартный редактор макросов.​«1»​ кнопку​ формата, то вместо​F2​

​ Повторное нажатие этого​ команду​ а затем, удерживая​ Вбиваем или копируем​. Затем выделяем её​«OK»​

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

​ и жмем на​.​ выделить именно его​

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

​ по клавише​ но формат выставлен​ целой и дробной​ всей последней +1​ для чисел, только​ кнопкой мыши по​

​оставляем пустым, далее​Правка​ активном выделении​ нужной ячейке.​ выделению строки или​​.)​​ щелкните последнюю ячейку​

​ а затем перетащите​Протащите указатель мыши по​ обратно в числовой.​ можно использовать полосу​

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

​Selection.Value = Selection.Value​Выделяем область на листе,​ нужно прощелкать все​В любом случае, в​

​ вариант.​ и не сможет​ стоит точка. Замените​​ сообщение. Там вставляются​​Проблема в том, когда​ способ, конечно, не​ то этих действий​) — в поле​​ в новую выделенную​​ указатель мыши на​​ заголовкам строк или​​После преобразования чисел из​​ прокрутки.​​End Sub​

​ будет достаточно. Но​Найти​ область. В эту​

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

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

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

​ с элементарной формулой:​ Такой пробел придётся​Заменить на​Чтобы отменить выделение ячеек,​Кроме того, можно выделить​​ последнюю строку или​​ формат или настроив​ также можно нажать​Выделяем фрагмент на листе,​«Специальная вставка»​После выполнения этих действий​ ячейки, содержащие формулы,​ТЕКСТ​ вписаны в элемент​

​: Воспользуйся фунуцией ЗНАЧЕН()​​ обязательно её выделять.​ после чего значение​

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

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

​ клавиши CTRL+A.​ который нужно преобразовать.​.​​ все значения выбранного​​ можно очистить. Для​. Данный способ подойдёт,​ листа с общим​​Микки​​ Если вы знаете​

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

​ нужный нам вид.​ кликаем правой кнопкой​ если вы хотите​Для начала рассмотрим различные​ единицу я додумался​​ можно сделать так​​ суммироваться. Но когда​ на -1 два​Найти​Заменить все​Главная​ расширения выделения с​ или столбца выделения,​ форматы.​ CTRL+A выделяется текущий​, который расположен на​ блоке​Перевести текстовые данные в​ мыши и выбираем​ перенести числа как​ варианты решения задачи​ но надо тогда​ (это как пример)​ таблица большая заходить​ раза. Минус на​диалогового окна​. Таким образом, делая​в группе​ помощью клавиш со​ а затем, удерживая​

​К началу страницы​ диапазон. Повторное нажатие​ вкладке​​«Операция»​​ числовые можно, воспользовавшись​ позицию​​ текст в отдельный​​ конвертации чисел в​​ формироваь массив единиц​​Range(«A1») = 5​

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

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

​ надписью​​Большой диапазон ячеек​​ выделение.​ текст в обычное​​ ячеек​​Открывается окно записанных в​«OK»​​ во вкладку​​Урок:​

​ ячеек не помогает.​
​ операции переключает формат​
​Alt+0160​
​ и с датами​

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

​ поэтому тоже иногда​​ и при этом​: Среди текстовых функций​

​ нам числовой.​ цифровой клавиатуре).​ отличием, что менять​В поле​ нажатой клавишу SHIFT,​ столбце​ 1, результат не​ затем, удерживая нажатой​ с наименованием​ значения выделенной области​ по полю с​ способами можно выполнить​ диапазоне, насчитывающем сотни​ преобразовать данные в​ сталкиваюсь с этой​ НЕ выделяли её.​​ есть ЗНАЧЕН /​​Само-собой, вместо умножения на​Пробелы можно удалить и​ нужно точку на​Категория​ щелкните последнюю ячейку​Выделите ячейку в строке​ меняется. Однако при​ клавишу CTRL, выделите​«Текст_в_число»​ будут преобразованы в​ выбором формата в​

​ обратную задачу, а​

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

​ или тысячи строк​ текст. Как видим,​ проблемой. Она возникает​Чтобы считать значение​ Предназначена для перевода​ 1 можно использовать​ формулой. Варианты:​ точку.​выберите нужный числовой​ диапазона. Чтобы перейти​ или столбце, а​ этом приложение Excel​ другие ячейки или​

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

В этой статье

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

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

​ фактически заменяет текст​ диапазоны.​ жмем на кнопку​

​ желании можно удалить​

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

​«Число»​ текст в число​ самый лучший выход.​«Главная»​ 1С в Excel​i = Range(«A1″)​ число. Я попробовал,​ математическую операцию: деление​=—ПОДСТАВИТЬ(B4;» «;»»)​ могут выгружать числа​Для успешного выполнения данной​ можно использовать полосу​ CTRL+СТРЕЛКА (ВПРАВО или​ на эквивалентные числа.​Вы можете также выбрать​«Выполнить»​

​ цифру​. Выбираем пункт​ в Excel.​Устанавливаем курсор в первый​на панели инструментов​

​ через «копировать» -​i = [A1]​ работает​ на 1 или​Для неразрывных пробелов:​

​ с точкой в​ процедуры числа, которые​

​ ВЛЕВО — для строк,​

​Выделите пустую ячейку и​

​ первую ячейку или​.​«1»​«Числовой»​

​Проще и быстрее всего​

​ элемент диапазона, в​ в блоке​ «вставить». Так?​i = Cells(1,1)​Sergey tsoy​ прибавление-вычитание нуля. Эффект​=—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)​ качестве десятичного разделителя,​

​ хранятся как текст,​Все ячейки листа​ ВВЕРХ или ВНИЗ —​ убедитесь в том,​ диапазон ячеек, а​Как видим, тут же​, которую мы использовали​или​ выполнить конвертацию текстового​ котором будет выводиться​

​А если отчет​Pavel55​: Снести Excel -​ будет тот же.​Сразу для тех и​ тогда поможет замена​ не должны содержать​Нажмите кнопку​

​ что она представлена​​ затем нажать клавиши​​ происходит преобразование текстового​

​ в целях конвертации.​«Общий»​ варианта с помощью​

​ результат преобразования. Щелкаем​в специальном поле​ сохранять в файл​: А зачем вам​ глючит. Это будет​Этот способ использовали еще​ других пробелов:​

​ точки на запятую.​ внутри или вне​

​Выделить все​Первую или последнюю ячейку​ в числовом формате​ SHIFT+F8 для включения​ выражения в числовой​Ещё одним вариантом, при​

​.​ специального значка, который​ по значку​ отображается информация о​ через «сохранить как. «,​ выделять всю строку?​ быстрее​ в старых версиях​=—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)​Аналогичную замену можно проделать​ себя лишние пробелы​

​.​ на листе или​ «Общий».​ в выделение других​

​ котором можно преобразовать​Далее прощелкиваем уже не​

​ сообщает об ошибке.​

​ том, что данные​ то всё нормально​ ) Вот код​Людмила дрючина​ Excel, когда современные​Иногда, чтобы добиться желаемого​ и формулой (см.​ или непечатаемые знаки.​Чтобы выделить весь лист,​ в таблице Microsoft​Проверка числового формата​ несмежных ячеек или​

​Урок:​ текст в числовой​ раз описанным нами​ Этот значок имеет​, который размещен около​ элементы имеют общий​ и цифры, как​ActiveCell.End(xlDown).Offset(1, 0).EntireRow.Select​: В Excel числа​ эффективные менеджеры под​ результата, приходится комбинировать​

​ ниже), используя функцию​

​ Лишние пробелы и​ также можно нажать​ Office Excel​На вкладке​ диапазонов. Чтобы остановить​Как создать макрос в​ вид, является применение​ способом каждую ячейку​ вид восклицательного знака,​

​ формат, а значит,​ положено, в числовом​Pavel55​ в таблицах работают​ стол ходили зеленого​ перечисленные способы. Например,​ ПОДСТАВИТЬ()​ непечатаемые знаки могут​

​ клавиши CTRL+A.​Чтобы выделить первую ячейку​Главная​

​ включение ячеек и​ Экселе​ инструмента​ преобразуемой области с​ вписанного в пиктограмму​Запускается окно​ цифры, вписанные в​

​ формате.​: Вот второй пример,​ по формулам и​ уголка-индикатора еще не​

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

​в группе​ диапазонов, снова нажмите​Как видим, существует довольно​«Текст столбцами»​ применением клавиш​ в виде ромба.​

​Мастера функций​ них, воспринимаются программой,​А вообще при​

​ как можно выделить​ быстрее сделать, что​ было в принципе​ а затем преобразовывать​. Этот способ более​ или импорте данных​ при нажатии клавиш​

​Число​ клавиши SHIFT+F8.​ много вариантов преобразования​. Его есть смысл​F2​

​ Он появляется при​. В категории​ как число.​

​ выделении таких текстовых​ всю первую пустую​ вы задумали, вам​ (он появился только​ формат ячеек​ универсальный, так как​ из базы данных​ CTRL+A выделяется текущий​ нажмите сочетание клавиш​

​нажмите стрелку в​Отменить выделение отдельных несмежных​ в Excel цифр,​

​ использовать тогда, когда​и​ выделении ячеек, которые​«Текстовые»​

​Кликаем правой кнопкой мыши​​ чисел, в углу​​ строку ниже таблицы.​ не удастся. Такое​ с 2003 года).​Если для каких-либо ячеек​​ работает и с​​ или другого внешнего​

​ диапазон. Повторное нажатие​ CTRL+HOME.​ поле​

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

Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»

​ дробными числами, и​ источника данных. Для​ клавиш CTRL+A приведет​Чтобы выделить последнюю ячейку,​Числовой формат​ без отмены всего​ числовом варианте, в​ качестве разделителя десятичных​.​ левом верхнем углу​«ТЕКСТ»​ в открывшемся меню​

​ можно указать «Преобразовать​ обязательно должны быть​ Worde.​в любую пустую ячейку​ установлен текстовый формат​

​ с целыми, а​

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

​ выбираем позицию​​ в число» -​​ любые данные​Саша Уткин​

​ введите 1​ (это мог сделать​ также с датами.​ из нескольких чисел,​

​ листа.​ форматирование, на листе​Общий​Столбец или строку целиком​

​ в обратном направлении.​ а в качестве​

​ преобразованы из текстовых​

​ нами ранее. Эта​

​«Формат ячеек…»​ см. прикрепленный файл​Rows(Cells(Rows.Count, 1).End(xlUp).Row +​: Уважаемые господа! Помогите​

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

​ разделителя разрядов вместо​ в числовые.​ пометка ещё не​«OK»​.​А лучше кстати​ 1).EntireRow.Select​ пожалуйста. Имеется столбец​выделите ячейки с числами​ при выгрузке данных​

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

​ свидетельствует о том,​

​.​​В открывшемся окне форматирования​​ сохранять как копию​

​Саша Уткин​ в котором числа​ в текстовом формате​

​ в Excel), то​ команду​ воспользоваться функцией СЖПРОБЕЛЫ​Выделите первую ячейку или​ клавиш CTRL+END.​1​1. Заголовок строки​

​ факторов. Прежде всего,​ Этот вариант воспринимается​

​ значений в числовые​ что данные находящиеся​Открывается окно аргументов оператора​ переходим во вкладку​ тогда и иерархия​: Спасибо! Всё получилось!​

​ сохранены как текст.​ и поменяйте у​ введенные потом в​Копировать​ или функцией ПЕЧСИМВ.​ диапазон ячеек, а​Ячейки до последней используемой​и нажмите клавишу​2. Заголовок столбца​ это поставленная задача.​ в англоязычном Экселе,​

​ можно использовать специальные​ в ячейке обязательно​ТЕКСТ​«Число»​

​ Благодарю Вас!​ Как в макросе​

​ эти ячейки числа​

​— выделяем диапазон​ Функция СЖПРОБЕЛЫ удаляет​ затем, удерживая нажатой​ ячейки листа (нижний​ ВВОД.​Вы можете также выделить​ Ведь, например, быстро​ как числовой, но​ формулы. Рассмотрим, как​ ошибочные. Но цифры,​. Данная функция имеет​, если оно было​

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

​ преобразовать текстовое выражение​

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

​ текстом. Иногда такие​

​ —​ за исключением одиночных​ другие ячейки или​Выделите первую ячейку, а​ сочетание клавиш CTRL+C,​ или столбце, выделив​ с иностранными разделителями​ этой программы все​

​ практике.​ имеющей текстовый вид,​=ТЕКСТ(значение;формат)​

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

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

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

​Открывшееся окно имеет два​ настроек​Микки​ строку?​Pavel55​ псевдочислами правой кнопкой​

​ индикатором, который вы,​—​ Функция ПЕЧСИМВ удаляет​

​Вы можете также выбрать​ CTRL+SHIFT+END, чтобы расширить​ в буфер обмена.​ затем нажав сочетание​ только использовав инструмент​ указанные выше знаки,​ параллельно первому элементу​

​ программы в том,​

​ поля, которые соответствуют​«Числовые форматы»​: Это как по​Или второй вариант:​: Вам макросом или​

​ мыши и выберите​ скорее всего, видели:​Сложить​

​ из текста все​ первую ячейку или​ выделенную область до​Выделите ячейки или диапазоны​ CTRL+SHIFT+клавиша со стрелкой​«Текст столбцами»​ воспринимаются как текст.​ диапазона, который следует​ что данные могут​

​ данным аргументам:​выбираем позицию​ файлу 400*60?​

​ Выделить всю таблицу​​ руками?​​ команду​​Причем иногда такой индикатор​​—​ непечатаемые знаки.​​ диапазон ячеек, а​​ последней используемой ячейки​​ ячеек, содержащие числа​​ (СТРЕЛКА ВПРАВО или​

​. Второй фактор, который​​ Конечно, можно перебить​​ преобразовать, ставим знак​​ быть внесены некорректно.​​«Значение»​​«Текстовый»​​ss_sany​

​ — минус последняя​Если макросом, то​Специальная вставка (Paste Special)​ не появляется (что​ОК​К началу страницы​

​ затем нажать клавиши​ листа (нижний правый​ в текстовом формате,​ СТРЕЛКА ВЛЕВО —​​ влияет на выбор​​ данные вручную, но​ «равно»​ Поэтому на всякий​и​. Для сохранения изменений​: если не помогает​ строка?​ выделяете мышкой нужный​или используйте сочетание​ гораздо хуже).​

​. Таким образом, мы​Если проверка ошибок в​ SHIFT+F8 для включения​ угол).​ которые необходимо преобразовать.​

​ для строк, СТРЕЛКА​

​ варианта – это​

Способ 3. Применение числового формата к числам в текстовом формате

​«Формат»​ жмем на кнопку​ эта функция, значит​Range(«A43:L43»).Select​ диапазон и запускаете​ клавиш​В общем и целом,​ к числам (или​ Excel включена, при​ в выделение других​Ячейки до начала листа​Выделение ячеек, диапазонов, строк​ ВВЕРХ или СТРЕЛКА​ объемы и частота​ это займет значительное​и двойной символ​ помечает, чтобы пользователь​.​ «​ есть «лишние знаки»​

​ появление в ваших​ датам) прибавляем 0,​

​ вводе числа в​

​ несмежных ячеек или​

​Выделите первую ячейку и​

​ и столбцов​ ВНИЗ — для​ выполняемых преобразований. Например,​ количество времени, тем​

​ обратил внимание. Но,​В поле​OK»​ — пробелы, точка(или​- эти параметры​Sub Макрос1()​в открывшемся окне выберите​ данных чисел-как-текст обычно​

​ что никак не​ ячейку с текстовым​ диапазонов. Чтобы остановить​ нажмите сочетание клавиш​Чтобы выделить​ столбцов).​ если вы часто​ более что существует​(—)​ к сожалению, такие​

​в нижней части​ запятая — зависит​ должны указывать не​[IV1].Copy​ вариант​ приводит к большому​ влияет на их​ форматом отображается маленький​

​ включение ячеек и​

​ CTRL+SHIFT+HOME, чтобы расширить​​Выполните следующие действия​​Если в строке или​

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

​ пометки Эксель выдает​нужно указать преобразовываемое​ окна.​ от разделителя знаков,​ конкретный адрес, а​Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd​Значения (Values)​

​ количеству весьма печальных​ значение, зато переводит​

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

​ быстрого решения проблемы.​ первого элемента трансформируемого​ не всегда даже​ число или ссылку​Как видим, после данных​ установленного в системе)​ именно последнюю не​End Sub​и​ последствий:​ в числовой формат​

​ отображать индикаторы ошибок​ клавиши SHIFT+F8.​ листа.​Щелкните ячейку или воспользуйтесь​

​ при нажатии сочетания​

​ запись макроса. И​Выделяем фрагмент листа, содержимое​

​ диапазона. Таким образом,​

​ тогда, когда цифры​

​ на ячейку, в​ манипуляций в специальном​ — просто от​ пустую строку. ​Если руками, то​Умножить (Multiply)​перестает нормально работать сортировка​Вариантом этого приёма может​ не требуется, их​Отменить выделение отдельных несмежных​Больше или меньше ячеек,​ клавишами со стрелками,​

​ CTRL+SHIFT+клавиша со стрелкой​ третий фактор –​ которого нужно преобразовать.​ происходит двойное умножение​ представлены в текстовом​ которой оно находится.​ поле высвечивается информация​ них избавляшься -​Selection.Borders(xlDiagonalDown).LineStyle = xlNone​1) Копируете любую​По-сути, мы выполняем то​

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

​ виде, поэтому ниже​

​ В нашем случае​ о том, что​ через формулы или​Selection.Borders(xlDiagonalUp).LineStyle = xlNone​ пустую ячейку с​ же самое, что​ вниз, а не​ на 1​

​Откройте вкладку​ без отмены всего​ активном выделении​

​ нужной ячейке.​ или столбец до​Автор: Максим Тютюшев​«Данные»​«-1»​ описанный способ подходит​ это будет ссылка​

​ ячейки были преобразованы​ найти-заменить. все достаточно​Selection.Borders(xlEdgeLeft).LineStyle = xlNone​ общим форматом (вобщем​

​ и в прошлом​ располагаются по-порядку как​С помощью инструмента Текст​Файл​ выделения невозможно.​

​Удерживая нажатой клавишу SHIFT,​Диапазон ячеек​ последней заполненной ячейки.​Иногда числа форматируются и​. На ленте инструментов​. Как известно, умножение​

​ не для всех​ на первый элемент​ в текстовый вид.​

​ просто​With Selection.Borders(xlEdgeTop)​ любую)​ способе — умножение​ положено:​ по столбцам​.​

​Столбец или строку целиком​

​ щелкните последнюю ячейку,​Щелкните первую ячейку диапазона,​ Повторное нажатие этого​ сохраняются в ячейках​ в блоке​

​ «минус» на «минус»​ случаев.​ обрабатываемого числового диапазона.​

​Но если мы попытаемся​ss_sany​. ​2) Выделяете нужный​ содержимого ячеек на​функции типа​. Этот приём удобно​В группе Справка нажмите​Щелкните заголовок сроки или​

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

​ как текст, что​​«Работа с данными»​​ дает «плюс». То​​Выделяем ячейку, в которой​​В поле​​ подсчитать автосумму, то​​: легко — делаешь​_Лена_​​ диапазон ячеек, который​​ единицу — но​

​ВПР (VLOOKUP)​​ использовать если преобразовать​​ кнопку​ столбца.​

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

​Private Sub Замена()​

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

​3) меню Правка​ напрямую из буфера.​ значения, потому как​ так как если​.​2. Заголовок столбца​ область войдет прямоугольный​ Или удерживая нажатой​Смежные строки или столбцы​

​ вычислениях или нарушению​​.​​ то же значение,​

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

​For Each c​ — Специальная вставка. ​​Если псеводчисла, которые надо​​ для них число​

​ столбцов несколько, то​​В диалоговом окне Параметры​​Вы можете также выделить​​ диапазон между активная​ клавишу SHIFT, нажимайте​Протащите указатель мыши по​​ порядка сортировки. Эта​

​Запускается​​ которое было изначально,​​ пиктограмме.​

Число сохранено как текст или Почему не считается сумма?

​ было совершено не​ слыбый, выключи автоматическицй​ In ActiveSheet.Range(«Ячейки»)​ — в области​ преобразовать, вдобавок еще​ и такое же​ действия придётся повторять​ Excel выберите категорию​ ячейки в строке​ ячейка и выделенной​ клавиши со стрелками,​ заголовкам строк или​

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

​«0»​​ полностью. В этом​ расчет формул. Результат​

​If c.Errors.Item(xlNumberAsText).Value =​ «Операция» выбераете «Сложить»​ и записаны с​ число-как-текст различаются:​ для каждого столбца​Формулы​ или столбце, выделив​ ячейкой.​ чтобы расширить выделение.​ столбцов. Либо выделите​ при импорте или​. В первом окне​ числовом виде. Даная​

​ в нем значение​, то текстовый вариант​ и заключается одна​ вычислений копируешь и​ True Then​4) Нажимаете ОК​ неправильными разделителями целой​при фильтрации псевдочисла отбираются​ отдельно. Итак, выделяем​.​ первую ячейку, а​Чтобы отменить выделение ячеек,​Кроме того, можно выделить​

​ первую строку или​ копировании данных из​

• ​ обратите внимание, чтобы​ процедура называется двойным​​ «​ на выходе будет​ из фишек Excel.​ вставляешь куда надо,​c.Value = c.Value​Саша Уткин​ и дробной части​ ошибочно​​ столбец с числами​

• ​Убедитесь, что в разделе​​ затем нажав сочетание​ щелкните любую ячейку​ первую ячейку диапазона,​ первый столбец, а​ базы данных или​ переключатель формата данных​​ бинарным отрицанием.​​Преобразовать в число»​ отображаться без десятичных​​ Программа не дает​​ но уже через​​ * 1​​: Подскажите пожалуйста как​ или тысяч, то​​многие другие функции Excel​​ или датами, сохраненными​​Правила поиска ошибок​​ CTRL+SHIFT+клавиша со стрелкой​​ на листе.​​ а затем нажать​ затем, удерживая нажатой​​ другого внешнего источника​​ стоял в позиции​​Жмем на клавишу​​.​​ знаков, даже если​​ завершить преобразование данных​ «Вставить значения».​c.NumberFormat = «#,##0.00″​ в макросе можно​​ можно использовать другой​

​ также перестают нормально​ как текст, устанавливаем​установлен флажок​ (СТРЕЛКА ВПРАВО или​На вкладке​ клавишу F8 для​

​ клавишу SHIFT, выделите​ данных.​«С разделителями»​Enter​В выделенном элементе данные​ в исходнике они​

​ наиболее интуитивно понятным​можно использовать макрос​End If​ реализовать возможность, чтобы​

• ​ подход. Выделите исходный​​ работать:​ формат ячейки​Числа, отформатированные как текст​ СТРЕЛКА ВЛЕВО —​Главная​ расширения выделения с​ последнюю строку или​Числа, отформатированные как текст,​. По умолчанию он​​, после чего получаем​​ тут же будут​ были. Если мы​ способом.​​ — по тому​​Next​​ курсор спустился на​​ диапазон с данными​​и т.д.​​Общий​ или с предшествующим​ для строк, СТРЕЛКА​в группе​ помощью клавиш со​ последний столбец.​ выравниваются в ячейках​

​ должен находиться в​ готовое преобразованное значение.​ преобразованы в числовой​

• ​ внесем​Чтобы завершить преобразование, нам​​ же принципу​End Sub​ последнюю запись таблицы.​ и нажмите кнопку​Особенно забавно, что естественное​(для чисел можно​ апострофом​ ВВЕРХ или СТРЕЛКА​Буфер обмена​ стрелками. Чтобы остановить​Несмежные строки или столбцы​ по левому, а​​ этой позиции, но​​ Для того, чтобы​ вид.​​«0,0»​​ нужно последовательно двойным​​Igor67​​Микки​​Заранее благодарен.​​Текст по столбцам (Text​​ желание просто изменить​​ установить, к примеру,​​.​

• ​ ВНИЗ — для​​щелкните стрелку под​ расширение выделенной области,​Щелкните заголовок первой строки​ не по правому​ проверить состояние будет​ применить данную формулу​Если подобных текстовых значений,​, то результат будет​ щелчком левой кнопки​: Про умножение на​: Отчет из 1-с​Pavel55​ to columns)​ формат ячейки на​Числовой​Нажмите кнопку​ столбцов).​ кнопкой​

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

​ числовой — не​или​ОК​Если в строке или​Вставить​ клавишу F8.​ а затем, удерживая​ часто обозначаются индикатором​ кликаем по кнопке​

​ ячеек диапазона, используем​

​ не одно, а​ десятичным знаком, если​ в каждый элемент​

​ с 0.​​ текст, можно ли​mazayZR​

​Данные (Data)​ помогает. Т.е. вы,​Финансовый​.​ столбце содержатся данные,​и выберите команду​Большой диапазон ячеек​ нажатой клавишу CTRL,​ ошибки.​

​«Далее»​ маркер заполнения, который​ множество, то в​​«0,00»​​ диапазона в отдельности​​Нужна только 1​​ макросом преобразовать их​: а если не​​. На самом деле​​ буквально, выделяете ячейки,​​). Далее выполняем команду​​К началу страницы​ при нажатии сочетания​Специальная вставка​Щелкните первую ячейку диапазона,​ щелкните заголовки столбцов​Способ 1. Преобразование чисел​.​ ранее был применен​ этом случае можно​, то с двумя,​ и нажать на​ свободная ячейка для​​ в числа, ручками​​ макросом, то вот​​ этот инструмент предназначен​​ щелкаете по ним​Данные​​Достаточно часто в Excel​​ CTRL+SHIFT+клавиша со стрелкой​​.​​ а затем, удерживая​ или строк, которые​

​ в текстовом формате​Во втором окне также​

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

​ и т.д.​​ клавишу​

​ ввода 1 или​ умею. Макрорекордером не​​ так:​

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

Преобразование чисел-как-текст в нормальные числа

​ нажатой клавишу SHIFT,​ требуется добавить в​ с помощью функции​ оставляем все без​ТЕКСТ​Выделяем весь диапазон, в​После того, как все​Enter​ 0. Потом копируете​ получилось.​Саша Уткин​ текста по столбцам,​ выбираете​Текст по столбцам​

​ сторонних программ для​ или столбец до​Операция​

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

• ​ требуемые параметры введены,​. Чтобы упростить задачу​ эту ячейку, выделяете​KOT​: Уважаемый Павел. Спасибо​
  
• ​ но, в данном​​Формат ячеек (Format Cells)​​—​ их дальнейшей обработки,​ последней заполненной ячейки.​выберите вариант​ диапазона. Чтобы перейти​
  
• ​Первую или последнюю ячейку​Способ 2. Преобразование чисел​
• ​ на кнопку​Теперь мы имеем диапазон,​ данные. Как видим,​
• ​ щелкаем по кнопке​

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

​«OK»​ можно использовать нажатие​ данными для преобразования​ 1 или прибавить​ Вы очень помогли,​ его с другой​Числовой (Number)​С помощью формул​ использование этих данных​

Способ 1. Зеленый уголок-индикатор

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

​. Если таблица позволяет​ в формулах даёт​ выделению строки или​

​ОК​ прокрутки.​Выделите ячейку в строке​ «Специальная вставка»​ третьего окна​ его и жмем​​ а не для​Как видим, значение первого​F2​ вставку в разделе​ :-)​​ наглость, ещё хочу​

Способ 2. Повторный ввод

​Пропустите первых два шага​ОК​ задействовать дополнительные столбцы,​ непредсказуемый результат: числа​ столбца полностью.​.​Все ячейки листа​ или столбце, а​Способ 3. Применение числового​Мастера текстов​ на кнопку​​ каждой ячейки в​​ элемента заданного диапазона​.​ ОПЕРАЦИИ (ОФ2003) умножить​Бухг​​ спросить. Вот я​​ нажатием на кнопку​​- и ничего​​ то для преобразования​ не суммируются, между​Смежные строки или столбцы​Чтобы удалить содержимое ячейки,​

​Нажмите кнопку​ затем нажмите клавиши​ формата к числам​нужно нажать на​

Способ 3. Формула

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

​: Микки, а почему​ добился, чтобы курсор​Далее (Next)​ не происходит! Совсем!​ в число можно​ датами невозможно рассчитать​Протащите указатель мыши по​ введенное на этапе​Выделить все​ CTRL+СТРЕЛКА (ВПРАВО или​ в текстовом формате​ кнопку​во вкладке​

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

Способ 4. Специальная вставка

​Возможно, «это не баг,​ использовать формулы. Чтобы​ количество дней и​ заголовкам строк или​ 2, после успешного​.​ ВЛЕВО — для строк,​Отключение проверки ошибок​«Подробнее»​«Главная»​

• ​Открывается уже знакомый нам​ ещё в первом​
• ​ области, данные в​
• ​ (цифровые естественно) будут​ как текст? Мне​ записи таблицы, а​ воспользуйтесь кнопкой​ а фича», конечно,​ перевести текстовое значение​
• ​ т.д.​ столбцов. Либо выделите​ преобразования всех чисел​Чтобы выделить весь лист,​​ ВВЕРХ или ВНИЗ —​​При импорте данных в​.​​или применяем сочетание​
• ​ список. Как и​ пункте данного руководства.​​ них будут восприниматься​​ преобразованы в число. ​​ постоянно приходится сохранять​

​ очень нужно, чтобы​Дополнительно (Advanced)​ но нам от​ в число, можно​В этой статье рассматриваются​ первую строку или​ выделите ячейку и​ также можно нажать​

Способ 5. Текст по столбцам

​ для столбцов).​ Excel из другого​Открывается окно дополнительной настройки​ клавиш​ в прошлый раз,​ Для того, чтобы​ программой, как текстовые​Артем​ отчёты из 1С​ он спустился на​​. Откроется диалоговое окно,​ этого не легче.​​ использовать двойной минус,​​ причины появления таких​​ первый столбец, а​ нажмите клавишу DEL.​ клавиши CTRL+A.​Первую или последнюю ячейку​ источника, а также​ импорта текста. В​Ctrl+C​ выбираем позицию​

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

​Некоторые программы бухгалтерского учета​​Если лист содержит данные,​​ на листе или​ при вводе чисел​ поле​

Способ 6. Макрос

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

​Выделяем исходную область и​.​ формулу в смежные​ равна нулю. Кроме​

​ работает, а макрос​ сам выставляется:​ в макросе реализовать?​​ нашем тексте символы-разделители:​ исправить ситуацию -​​ функции ЗНАЧЕН(), ПОДСТАВИТЬ().​Причина первая​ последнюю строку или​​ как текст со​​ CTRL+A выделяется текущий​​ Office Excel​ ранее были отформатированы​ части»​

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

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

В Excel числа сохранены как текст.

​ преобразованы в указанный​ курсор в нижний​ левый верхний угол​ работатет. ​ ##0,00;[Красный]-# ##0,00 и​Pavel55​Готово​ вам обязательно поможет.​ почитать здесь. После​ текст​Несмежные строки или столбцы​-​ клавиш CTRL+A приведет​ на листе или​ левом верхнем углу​ в поле​
​ мыши. В активировавшемся​ вид.​

​ правый угол элемента,​​ ячеек будет окрашен​методом тыка придумал​ всё прекрасно считается.​: ActiveCell.End(xlDown).Offset(1, 0).Select​Excel преобразует наш​Если на ячейке с​

​ преобразования полученный столбец​​В этом случае видно,​Щелкните заголовок первой строки​) справа от значения.​

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

Число сохранено как текст

​Артем​​P.S. сейчас ниже​ текст в нормальные​ числом с текстовом​ можно скопировать и​ что числа или​ или столбца выделения,​ Чтобы преобразовать эти​
​ листа.​

​ нажмите сочетание клавиш​​ маленький зеленый треугольник.​— апостроф. Затем​

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

​Sub xz()​
​: У вас там​
​ (в другом сообщении)​
​ числа.​

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

​For Each cell​​ пробелы в числах.​ напишу, как более​Если подобные преобразования вам​ зеленый уголок-индикатор, то​ на место исходных​
​ левому краю ячейки​

​ нажатой клавишу CTRL,​​ значения, необходимо с​

​ ячеек​​Чтобы выделить последнюю ячейку,​ указывает на то,​ по кнопке​

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

​«OK»​​«Значения и форматы чисел»​

​ возможность обратного конвертирования​ Зажимаем левую кнопку​ которых находятся цифры,​cell.Formula = cell.Value​ нужному дипазону с​ в таблицу (ниже​

​ то имеет смысл​​ повезло. Можно просто​
​С помощью макросов​
​ как правило, в​ или строк, которые​ все знаки текстовой​ диапазон ячеек, а​ форматирование, на листе​

​ в текстовом виде,​​.​.​ через окно форматирования.​ мыши и протаскиваем​ преобразованы в текстовый​ + 0​

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

​ вариант отображения. Хотя​Next​ на «», запишите​Саша Уткин​ при помощи несложного​ с данными и​ перечисленных способов можно​ ячейки имеется маркер​

​ выделение.​ правого (знака минус)​

​ клавишу CTRL, выделите​

​ Excel, нажмите сочетание​​ данном примере.​Мастера текстов​ нужном нам виде.​ в текстовом варианте.​

​ параллельно диапазону, в​​ этот признак не​End Sub​ операцию макроредактором​: Павел спасибо. Методика​ макроса. Нажмите сочетание​ нажать на всплывающий​ выполнить макросом. Если​ ошибки (зелёный треугольник)​Первую или последнюю ячейку​ и умножить результат​ другие ячейки или​ клавиш CTRL+END.​Если это нежелательно, выполните​

​и жмем на​
​ Теперь можно удалить​
​ Кликаем правой кнопкой​

​ котором находятся исходные​ всегда является обязательным​вроде работает​Микки​ActiveCell.End(xlDown).Offset(1, 0).Select​ клавиш Alt+F11 или​

​ желтый значок с​ Вам приходится часто​

​ и тег, который​
​ в строке или​
​ на -1.​

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

​ транзитный диапазон, в​

​ мыши. В контекстном​​ данные.​ и в некоторых​Микки​: Микки, а почему​сработала успешно, но​ откройте вкладку​ восклицательным знаком, а​

​ выполнять подобное преобразование,​ при наведении мышки​

​ столбце​​Например, если в ячейке​Вы можете также выбрать​

​ ячейки листа (нижний​​ чтобы преобразовать число​«Готово»​ котором находится формула​
​ меню выбираем позицию​Теперь весь ряд заполнен​ случаях такая пометка​:​

​ он сохраняет его​
​ почему то не​
​Разработчик (Developer)​ затем выбрать команду​ то имеет смысл​ поясняет, что число​Выделите ячейку в строке​

​ A2 содержится значение​
​ первую ячейку или​
​ правый угол)​
​ в текстовом формате​
​.​

​ двойного бинарного отрицания.​​«Формат ячеек…»​

​ требуемыми данными. Но​

​ отсутствует.​Guest​
​ как текст? Мне​ выделяется эта последняя​
​и нажмите кнопку​Преобразовать в число (Convert​
​ написать макрос и​
​ сохранено как текст.​
​ или столбце, а​

​ «156-«, приведенная ниже​

Число как текст

​ диапазон ячеек, а​​Выделите первую ячейку, а​ в обычное число.​Как видим, после выполнения​ Для этого выделяем​.​ и это ещё​Урок:​

​: У меня почему-то​​ постоянно приходится сохранять​ запись. Наверно это​Visual Basic​ to number)​

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

​ это тоже не​​ отчёты из 1С​ глюк программы?​
​. В появившемся окне​:​ мере необходимости.​ Числовой, Общий или​ CTRL+СТРЕЛКА (ВПРАВО или​

​ в значение «-156».​​ SHIFT+F8 для включения​ CTRL+SHIFT+END, чтобы расширить​ диапазон смежных ячеек​ приняли привычный для​ правой кнопки мыши​ Как и в​ сути, все элементы​ Excel​
​ работает. Когда ручками​ в Excel’е и​Pavel55​
​ редактора добавьте новый​Все числа в выделенном​Приведу два примера макросов:​

​ Дата ситуацию не​​ ВЛЕВО — для строк,​Данные​ в выделение других​ выделенную область до​ с индикатором ошибки​ русскоязычной версии формат,​ вызываем контекстное меню​ предыдущий раз, переходим​
​ нового диапазона содержат​Преобразовать число в текстовый​ делаешь- все ок!​ формат при сохранении​: Допустим у вас​ модуль через меню​ диапазоне будут преобразованы​

​1) умножение на 1​ исправляют, но если​ ВВЕРХ или ВНИЗ —​Формула​ несмежных ячеек или​ последней используемой ячейки​ в верхнем левом​

​ а это значит,​​ и выбираем в​ во вкладку​ в себе формулы.​ вид можно также​ Пишешь макро с​ сам выставляется:​ есть какая-то таблица​Insert — Module​ в полноценные.​Sub conv() Dim c​
​ кликнуть в строку​ для столбцов).​
​156-​

​ диапазонов. Чтобы остановить​​ листа (нижний правый​

​ углу .​​ что они одновременно​ нем позицию​«Число»​ Выделяем эту область​ воспользовавшись инструментами на​ теми же самыми​

​(все форматы) #​​ и вам надо​

​и скопируйте туда​​Если зеленых уголков нет​ As Range For​ формул (или нажать​Первую или последнюю ячейку​=ЛЕВСИМВ(A2,ДЛСТР(A2)-1)*-1​ включение ячеек и​ угол).​Выделение ячеек, диапазонов, строк​
​ были преобразованы из​«Очистить содержимое»​. В группе​ и жмем на​ ленте, в частности,​ действиями — все​ ##0,00;[Красный]-# ##0,00 и​

​ вставить число 5​ следующий код:​ совсем, то проверьте​ Each c In​ F2), а затем​ на листе или​К началу страницы​

​ диапазонов, снова нажмите​Ячейки до начала листа​ и столбцов​ текстовых данных в​
​.​«Числовые форматы»​ значок​

​ использовав поле для​​ как и было​ всё прекрасно считается.​

​ ниже этой таблицы​​Sub Convert_Text_to_Numbers() Selection.NumberFormat​ — не выключены​ Selection If IsNumeric(c.Value)​ Enter, то число​ в таблице Microsoft​В некоторых случаях не​ клавиши SHIFT+F8.​Выделите первую ячейку и​Чтобы выделить​ числовые.​Кстати, для преобразования значений​нам нужно выбрать​

​«Копировать»​​ показа формата, о​ раньше — зеленые​Ну не знаю​ в встолбце А.​ = «General» Selection.Value​ ли они в​ Then c.Value =​ становится числом, а​ Office Excel​ нужно преобразовывать числа​
​Отменить выделение отдельных несмежных​ нажмите сочетание клавиш​Выполните следующие действия​

​Если вам часто приходится​​ данным методом совсем​ значения, которые позволят​, который расположен во​
​ котором шел разговор​ треугольники в уголках. ​ у меня не​Sub Макрос2()​ = Selection.Value End​ настройках вашего Excel​ c.Value * 1​ дата датой. При​Чтобы выделить первую ячейку​ из текстового формата​ ячеек или диапазонов​
​ CTRL+SHIFT+HOME, чтобы расширить​Отдельную ячейку​ преобразовывать большие области​

​ не обязательно использовать​​ преобразовать текст в​ вкладке​ выше.​ Можт кто-нибудь поборол​ считается. ​

​Dim iLastRow As​ Sub​
​ (​

​ c.NumberFormat = «#,##0.00″​ большом количестве таких​
​ на листе или​ обратно в числовой,​
​ без отмены всего​
​ выделение до начала​

​Щелкните ячейку или воспользуйтесь​

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

​ число. К ним​​«Главная»​Выделяем элементы, данные в​ этот глюк макросом?​Nonick​ Long​Теперь после выделения диапазона​Файл — Параметры -​ End If Next​ чисел вариант, согласитесь,​ в списке Excel,​ как было показано​ выделения невозможно.​

Преобразование чисел-как-текст в нормальные числа

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

Причем иногда такой индикатор не появляется (что гораздо хуже).

В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

• перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
  
• функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
  
• при фильтрации псевдочисла отбираются ошибочно
• многие другие функции Excel также перестают нормально работать:
• и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

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

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

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

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

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

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

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

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

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

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

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Вводим в ячейку число как текст

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

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

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

Вводить числа как текст в Excel приходится достаточно часто. Например:

1. Номер фактуры или договора состоит из более 12-ти чисел. Допустим номер: «1234567891012» Excel округляет большие числа в такой формат «1,23457E+12».
2. Некоторые номера телефонов автоматически конвертируются в дату. Номер телефона: 2012-10-17 отображается как «17.10.2012».
3. Иногда нужно записать формулу обычным текстом.

Поэтому важно научиться управлять форматами ячеек.

Попробуйте заполнить табличку, так как показано на рисунке:

Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:

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

Решение 1 . В ячейке D2 введите перед текстовым значением символ апострофа «’» ( апостроф следует ввести с английской раскладки клавиатуры ). Теперь число преобразовано в текстовое значение.

Решение 2 . Задайте в ячейке D2 текстовый формат. Для этого откройте окно «Формат ячеек» CTRL+1. На вкладке «Число» выберите из списка «Числовые формат» опцию «Текстовый» и нажмите ОК. Теперь можно вводить любые числа как текст без апострофа.

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

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

Математические операции с текстом в Excel

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

1. Заполните диапазон ячеек A1:D6 так как показано на рисунке:
2. Колонку E не нужно заполнять, так как в ней отображается содержимое ячеек в колонке D. Обратите внимание в ячейках B3 и B6 числовые данные записаны как текстовые через апостроф «’».
3. В колонку D введите формулы, которые отображаются в колонке E как на рисунке.

Теперь обратите внимание на результаты вычислений функций и формул в колонке D.

В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.

Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.

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