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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать чтобы столбцы в excel обозначались цифрами?

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

Добавление от 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 предоставляет несколько возможностей по нормализации данных и у каждого из них есть свои плюсы и минуса.

Читать еще:  Как сделать константу в excel?

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

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

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

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

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

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

Читать еще:  Как сделать раскрывающийся список в excel 2003?

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

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

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 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» и переносим в него нижеприведенный код:

Ссылка на основную публикацию
Adblock
detector