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

Как в excel сделать разделитель разрядов пробелом?

Как в excel сделать разделитель разрядов пробелом?

Простая казалось бы задача — по макросу вставить буфер обмена (табличные данные на несколько столбцов) в конкретную ячейку.

При ручном исполнении всё проходит как надо, все цифры типа 100,000000 (шесть нулей) встают как 100! Но, если всё ТОЖЕСАМОЕ делать через макрос

код:
, то по непонятной причине пропускается запятая и цифры встают уже как миллионы!

Если тип ячейки на листе задать как «текстовый», то записывается 100, текстом соответственно (нельзя суммировать и так далее).

код:
Это какой-то очень странный глюк — как его победить?

цитата: Это какой-то очень странный глюк — как его победить? Локаль ? Это не глюк, а поддержка культурно-языковых предпочтений. Запятая принимается как разделитель дробной части в русской локали.
В POSIX-совместимой локали разделитель дробной части — точка (и это является «православно-правильным»).

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

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

Дядя, может вы не поняли вопроса? Это баг преобразования данных.

Из буфера цифра 100,000000 в макросе превращается в 100000000 а при ручной вставке делается правильная 100.

В POSIX-совместимой локали разделитель дробной части — точка

И что? Вы ещё раз вопрос перечитайте.

цитата (mod_lord): Это баг преобразования данных. И я вам обьяснил почему он происходит. Дополнительно вы можете прочитать про символы-разделители груп разрядов и символы-разделители дробной части.

Ручную вставку вы делаете через GUI для которого, я подозреваю, у вас используется русская локаль, в которой запятая считается разделителем дробной части. Скрипт очевидно выполняется в другом контексте где (по умолчанию или явно) используется другая локаль (я подозреваю что это POSIX совместимая локаль как наиболее общая), где запятая является разделителем груп разрядов. Все тривиально просто.

vertur
где запятая является разделителем груп разрядов. Все тривиально просто.

Теперь стало чуточку понятнее.

В Excel есть настройка — Использовать системные разделители. Проверил — это настройка только для ОТОБРАЖЕНИЯ в Excel. VBA для преобразования использует чтото своё.

Раз это классическая проблема, то и в гугле её можно отыскать, но у меня не получилось.

Добавление от 28.03.2018 23:29:

Можно и без вставки тестировть Cells(6, 2).Value = «100,000000»

проблема в том, что после запятой слишком много знаков! Это чтото с Long.

В буфере данные представлены скорее всего 0d0a перевод строки и 09 это разбивка на столбы.

Добавление от 29.03.2018 00:05:

Нашел костыль (http://forum.ixbt.com/topic.cgi?id=23:19911) тупо эмулируем вставку руками.

Но это не решение! Мне попрежнему интересно как вставлять в ячейку цифры с тысячными изпод VBA.

mod_lord
Если тип ячейки на листе задать как «текстовый», то записывается 100, текстом соответственно (нельзя суммировать и так далее)

Ну, это легко поправить код:

Ted the Mechanic
Ну, это легко поправить
Это вы о чем? макросом бегать по всем ячейкам и задавать формат? мне текстовый не нужен. это просто был пример преобразования данных.

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

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

О вставке в активную ячейку числа 100, если в буфере находится конкретно «100,000000»
Числовой формат ячейки (например с двумя знаками после запятой) будет сохранен.
Разумеется, вместо ActiveCell можно использовать любую ячейку.

Ted the Mechanic

Повторю ещё раз — проблема в том что в буфере 100,000000 , а VBA вставляет из буфера 100000000. Понимаете? Количество нулей после запятой меня не интересует — мне нужно получить 100 в ячейки числом.

Как это сделать через VBA без тупой эмуляции ручной вставки. вроде всё уже описано — так где же решение?

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

Добрый день уважаемый читатель!

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

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

Мастер разбора текстов

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

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

  1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:
  • С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
  • Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.
  1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
  2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:
  • Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
  • Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
  • Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

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

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

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

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

  • Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
  • Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

  1. Выдергиваем отдельные слова по разделителю;
  2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

Источник:
http://svrecord.ru/excel/kak-v-excel-sdelat-razdelitel-razryadov-probelom.html

Удаление лишних пробелов в Microsoft Excel

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

Технология удаления пробелов

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

Способ 1: использование инструмента «Заменить»

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

    Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая размещена в блоке инструментов «Редактирование» на ленте. В выпадающем списке выбираем пункт «Заменить». Можно также вместо вышеперечисленных действий просто набрать сочетание клавиш на клавиатуре Ctrl+H.

В любом из вариантов открывается окно «Найти и заменить» во вкладке «Заменить». В поле «Найти» устанавливаем курсор и дважды кликаем по кнопке «Пробел» на клавиатуре. В поле «Заменить на» вставляем один пробел. Затем жмем по кнопке «Заменить все».

  • Далее опять появляется окно «Найти и заменить». Выполняем в этом окне точно такие же действия, как описаны во втором пункте данной инструкции до тех пор, пока не появится сообщение о том, что искомые данные не найдены.
  • Читайте также  Автоматическая нумерация строк

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

    Способ 2: удаление пробелов между разрядами

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

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

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

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

    Способ 3: удаление разделителей между разрядами путем форматирования

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

    Так как пробелы были сделаны с помощью инструментов форматирования, то только при помощи этих же инструментов их можно убрать.

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

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

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

    Инструмент «Найти и заменить» отлично подходит для удаления лишних пробелов между символами. Но что делать, если их нужно убрать в начале или в конце выражения? В этом случае на помощь придет функция из текстовой группы операторов СЖПРОБЕЛЫ.

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

    Синтаксис этого оператора довольно прост и имеет всего один аргумент:

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

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

    Запускается окно Мастера функций. В категории «Полный алфавитный перечень» или «Текстовые» ищем элемент «СЖПРОБЕЛЫ». Выделяем его и жмем на кнопку «OK».

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

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

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

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

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

    Как видим, после вышеописанных действий значения с лишними пробелами были заменены идентичными данными без них. То есть, поставленная задача выполнена. Теперь можно удалить транзитную область, которая была использована для трансформации. Выделяем диапазон ячеек, в которых содержится формула СЖПРОБЕЛЫ. Кликаем по нему правой кнопкой мыши. В активировавшемся меню выбираем пункт «Очистить содержимое».

  • После этого лишние данные будут убраны с листа. Если в таблице существуют другие диапазоны, которые содержат лишние пробелы, то с ними нужно поступить по точно такому же алгоритму, какой был описан выше.
  • Как видим, существует целый ряд способов быстрого удаления лишних пробелов в Экселе. Но все эти варианты реализуются при помощи всего двух инструментов – окна «Найти и заменить» и оператора СЖПРОБЕЛЫ. В отдельном случае можно использовать также форматирование. Не существует универсального способа, который было бы максимально удобно использовать во всех ситуациях. В одном случае будет оптимальным использовать один вариант, а во втором – другой и т.д. Например, с удалением двойного пробела между словами быстрее всего справится инструмент «Найти и заменить», а вот убрать пробелы в начале и в конце ячейки корректно сможет только функция СЖПРОБЕЛЫ. Поэтому о применении конкретного способа пользователь должен принимать решение самостоятельно с учетом ситуации.

    Источник:
    http://lumpics.ru/how-remove-extra-spaces-in-excel/

    2 способа удалить пробелы между словами или числами в ячейках Excel

    Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.

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

    Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как “John Doe“. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.

    Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:

    Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы

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

    Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов

    В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:

    1. Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
    2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:

  • Скопируйте эту формулу в остальные ячейки столбца. Вы можете использовать советы из статьи Как вставить одинаковую формулу сразу во все выбранные ячейки .
  • Замените исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl+C, чтобы скопировать данные в буфер обмена.Далее выделите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift+F10 или клавишу вызова контекстного меню, а затем клавишу V (З).
  • Удалите вспомогательный столбец.
  • Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.

    Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.

    Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами

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

    1. Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
    2. Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
    3. Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
    4. Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
    5. Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).

    Удаляем все пробелы между числами

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

    Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).

    • Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
    • Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
    • В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
    • Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.

    Удаляем все пробелы при помощи формулы

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

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

    Источник:
    http://office-guru.ru/excel/2-sposoba-udalit-probely-mezhdu-slovami-ili-chislami-v-jacheikah-excel-294.html

    Три способа, как убрать пробелы в числах в Excel

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

    Способ 1: инструмент «Заменить»

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

    1. Откройте нужную таблицу в программе.
    2. Перейдите во вкладку «Главная».
    3. На ленте инструментов отыщите и кликните левой кнопкой мыши по пункту «Найти и выделить».
    4. В появившемся меню выберите опцию «Заменить».
    5. В появившемся окне введите в графу «Найти» два пробела, просто дважды нажав по соответствующей клавише на клавиатуре.
    6. В графу «Заменить на» впишите один пробел.
    7. Нажмите кнопку «Заменить все».

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

    Способ 2: удаление пробелов между разделителями

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

    1. Выделите часть таблицы с числовыми данными, в которых находятся лишние пробелы.
    2. На ленте инструментов опять же кликните по кнопке «Найти и выделить» и выберите опцию «Заменить». Кстати, это действие можно произвести путем использования горячих клавиш Ctrl+H.
    3. Появится уже знакомое окно, где нужно ввести две переменные.
    4. В поле «Найти» введите одинарный пробел.
    5. Поле «Заменить на» не трогайте, в нем ничего не должно находиться.
    6. Нажмите кнопку «Заменить все».

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

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

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

    1. Откройте таблицу в программе.
    2. Выделите ячейки, в которых стоит пробел между числами.
    3. Нажмите правой кнопкой мыши по выделению.
    4. Выберите в появившемся контекстном меню опцию «Формат ячеек».
    5. В появившемся окне перейдите на вкладку «Число».
    6. Откройте раздел «Числовой», находящийся на боковой панели.
    7. В правой части окна снимите отметку напротив строки «Разделитель групп разрядов».
    8. Нажмите «ОК».

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

    Источник:
    http://fb.ru/article/413738/tri-sposoba-kak-ubrat-probelyi-v-chislah-v-excel

    Как в excel сделать разделитель разрядов пробелом?

    Как в excel сделать разделитель разрядов пробелом?

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

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

    Установка разделителя групп разрядов

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

    Выделите ячейку или диапазон ячеек.

    .Нажмите кнопку Формат с разделителем группы Число вкладки Главная

    В этом случае автоматически установится финансовый формат. Для установки разделителя групп разрядов при числовом формате надо использовать вкладку Число окна диалога Формат ячейки. . Надо поставить флажок в поле Разделитель групп разрядов.

    Форматы отрицательных чисел

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

    Форматы дробных чисел

    Изменение разрядности десятичных дробей

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

    Выделив ячейку или диапазон ячеек, нажмите кнопку Увеличить разрядность или Уменьшить разрядность группы Число вкладки Главная. Одно нажатие соответствующей кнопки увеличивает или уменьшает на один знак количество отображаемых знаков после запятой. Либо изменить значение в поле Число десятичных знаков вкладки Число окна диалога Формат ячейки. .

    Форматы простых дробей

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

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

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

    Процентный формат

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

    Для выделенной ячейки или диапазон ячеек нажмите кнопку Процентный формат группы Число вкладки Главная.

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

    Для чисел, введенных с клавиатуры, процентный формат изменяет отображение не только в ячейке, но и в строке формул. Например, число 0,3 после применения процентного формата будет отображаться и в ячейке, и в строке формул как 30%. На результатах вычислений это никак не сказывается.

    Экспоненциальный формат

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

    Например, число 62500000000 будет отображена как 6,25E+11, где 6,25 — это мантисса числа, Е — признак десятичной системы счисления, а +11 — степень, в которую возводится основание системы счисления.

    То есть эта запись означает 6,25 умноженное на 10 11 (десять в одиннадцатой степени), а число 0,0000000000000002345 будет отображена как 2,345E-16, что означает 2,345 умноженное на 10 -16 (десять в минус шестнадцатой степени). Для установки экспоненциального формата проще всего воспользоваться раскрывающимся списком Числовой формат.

    Финансовый и денежный формат

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

    Если требуемой денежной единицы нет в списке, выберите команду Другие финансовые форматы.

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

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

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

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

    Для установки финансового формата с указанием российской денежной единицы (р.) достаточно выделить ячейки и нажать кнопку Финансовый числовой формат.

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

    Форматирование дат и времени

    Формат даты

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

    1.Выделите ячейку или диапазон ячеек.

    2.Откройте вкладку Число диалогового окна Формат ячейки.

    3.В списке Числовые форматы выберите Дата, в списке Тип выберите необходимый формат. При необходимости предварительно в раскрывающемся списке Язык выберите язык, в стандартах которого будет отображена дата.

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

    Формат времени

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

    1. Выделите ячейку или диапазон ячеек.
    2. Откройте вкладку Число диалогового окна Формат ячейки.
    3. В списке Числовые форматы выберите Время, в списке Тип выберите необходимый формат. При необходимости предварительно в раскрывающемся списке Язык выберите язык, в стандартах которого будет отображено время.

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

    Текстовый формат

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

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

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

    Дополнительные форматы

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

    1. Выделите ячейку или диапазон ячеек.
    2. Отобразите вкладку Число диалогового окна Формат ячейки.
    3. В списке Числовые форматы выберите Дополнительно, в списке Тип выберите необходимый формат. При необходимости предварительно в раскрывающемся списке Язык выберите язык.

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

    Источник:
    http://ksp102.ru/excel/kak-v-excel-sdelat-razdelitel-razryadov-probelom.html

    Как в excel сделать разделитель разрядов пробелом?

    Как в excel сделать разделитель разрядов пробелом?

    Простая казалось бы задача — по макросу вставить буфер обмена (табличные данные на несколько столбцов) в конкретную ячейку.

    При ручном исполнении всё проходит как надо, все цифры типа 100,000000 (шесть нулей) встают как 100! Но, если всё ТОЖЕСАМОЕ делать через макрос

    код:
    , то по непонятной причине пропускается запятая и цифры встают уже как миллионы!

    Если тип ячейки на листе задать как «текстовый», то записывается 100, текстом соответственно (нельзя суммировать и так далее).

    код:
    Это какой-то очень странный глюк — как его победить?

    цитата: Это какой-то очень странный глюк — как его победить? Локаль ? Это не глюк, а поддержка культурно-языковых предпочтений. Запятая принимается как разделитель дробной части в русской локали.
    В POSIX-совместимой локали разделитель дробной части — точка (и это является «православно-правильным»).

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

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

    Дядя, может вы не поняли вопроса? Это баг преобразования данных.

    Из буфера цифра 100,000000 в макросе превращается в 100000000 а при ручной вставке делается правильная 100.

    В POSIX-совместимой локали разделитель дробной части — точка

    И что? Вы ещё раз вопрос перечитайте.

    цитата (mod_lord): Это баг преобразования данных. И я вам обьяснил почему он происходит. Дополнительно вы можете прочитать про символы-разделители груп разрядов и символы-разделители дробной части.

    Ручную вставку вы делаете через GUI для которого, я подозреваю, у вас используется русская локаль, в которой запятая считается разделителем дробной части. Скрипт очевидно выполняется в другом контексте где (по умолчанию или явно) используется другая локаль (я подозреваю что это POSIX совместимая локаль как наиболее общая), где запятая является разделителем груп разрядов. Все тривиально просто.

    vertur
    где запятая является разделителем груп разрядов. Все тривиально просто.

    Теперь стало чуточку понятнее.

    В Excel есть настройка — Использовать системные разделители. Проверил — это настройка только для ОТОБРАЖЕНИЯ в Excel. VBA для преобразования использует чтото своё.

    Раз это классическая проблема, то и в гугле её можно отыскать, но у меня не получилось.

    Добавление от 28.03.2018 23:29:

    Можно и без вставки тестировть Cells(6, 2).Value = «100,000000»

    проблема в том, что после запятой слишком много знаков! Это чтото с Long.

    В буфере данные представлены скорее всего 0d0a перевод строки и 09 это разбивка на столбы.

    Добавление от 29.03.2018 00:05:

    Нашел костыль (http://forum.ixbt.com/topic.cgi?id=23:19911) тупо эмулируем вставку руками.

    Но это не решение! Мне попрежнему интересно как вставлять в ячейку цифры с тысячными изпод VBA.

    mod_lord
    Если тип ячейки на листе задать как «текстовый», то записывается 100, текстом соответственно (нельзя суммировать и так далее)

    Ну, это легко поправить код:

    Ted the Mechanic
    Ну, это легко поправить
    Это вы о чем? макросом бегать по всем ячейкам и задавать формат? мне текстовый не нужен. это просто был пример преобразования данных.

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

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

    О вставке в активную ячейку числа 100, если в буфере находится конкретно «100,000000»
    Числовой формат ячейки (например с двумя знаками после запятой) будет сохранен.
    Разумеется, вместо ActiveCell можно использовать любую ячейку.

    Ted the Mechanic

    Повторю ещё раз — проблема в том что в буфере 100,000000 , а VBA вставляет из буфера 100000000. Понимаете? Количество нулей после запятой меня не интересует — мне нужно получить 100 в ячейки числом.

    Как это сделать через VBA без тупой эмуляции ручной вставки. вроде всё уже описано — так где же решение?

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

    Добрый день уважаемый читатель!

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

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

    Мастер разбора текстов

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

    Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

    1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:
    • С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
    • Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.
    1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
    2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:
    • Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
    • Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
    • Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

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

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

    И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

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

    • Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
    • Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

    В результате мы разделили ФИО на три слова, что позволит с ними эффективно работать.

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

    Выдергиваем слова с помощью макросов VBA

    Рассмотрим два способа разделить текст в ячейке:

    1. Выдергиваем отдельные слова по разделителю;
    2. Делим текст без пробелов.

    Способ №1.

    Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

    Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

    Источник:
    http://t-tservice.ru/excel/kak-v-excel-sdelat-razdelitel-razryadov-probelom.html