Замена точек на запятые в excel vba

Обновлено: 04.07.2024

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

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

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

Если нет, то как может выглядеть макрос, который для данного столбца будет налету (при вводе) заменять запятые на точки?

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

может есть какое-то иное решение?

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

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

может есть какое-то иное решение?

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

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

может есть какое-то иное решение?

спасибо Автор - карандаш
Дата добавления - 01.12.2014 в 16:06

Есть ли в экселе возможность настроить эксел для устранения этой проблемы?
Да, как вариант - в настройках XL снять галку с системного разделителя и дать/указать свой - "." Есть ли в экселе возможность настроить эксел для устранения этой проблемы?
Да, как вариант - в настройках XL снять галку с системного разделителя и дать/указать свой - "." TimSha Есть ли в экселе возможность настроить эксел для устранения этой проблемы?
Да, как вариант - в настройках XL снять галку с системного разделителя и дать/указать свой - "." Автор - TimSha
Дата добавления - 01.12.2014 в 16:21

Pelena, buchlotnik
Спасибо большое (чой-то сам не догадался как-то))). Придется так и делать))

но всё же, может быть всё таки есть какой-нить простенький макрос?
Сергей как-то ваял макросы, но его сейчас не видно.

Pelena, buchlotnik
Спасибо большое (чой-то сам не догадался как-то))). Придется так и делать))

но всё же, может быть всё таки есть какой-нить простенький макрос?
Сергей как-то ваял макросы, но его сейчас не видно. карандаш

но всё же, может быть всё таки есть какой-нить простенький макрос?
Сергей как-то ваял макросы, но его сейчас не видно. Автор - карандаш
Дата добавления - 01.12.2014 в 16:28

TimSha, не проходит
он тогда цифры, которые с запятой, неправильно воспринимает TimSha, не проходит
он тогда цифры, которые с запятой, неправильно воспринимает карандаш
Он будет заменять даты. при вводе руками (например 11,12,14 в 11.12.14)
Он будет заменять даты. при вводе руками (например 11,12,14 в 11.12.14) SLAVICK

Иногда все проще чем кажется с первого взгляда.

Он будет заменять даты. при вводе руками (например 11,12,14 в 11.12.14) Автор - SLAVICK
Дата добавления - 01.12.2014 в 17:03

большое спасибо за макрос!
к сожалению, не смог его дополнить-модифицировать

1. Что нужно добавить, чтобы короткий формат даты преобразовывался в длинный автоматически? Чтобы из 12,12,14 получалось 12.12.2014. Сейчас получается 12.12.14 и эксел не знает 2014 или 1914 год.
Это не прихоть - просто при большом объёме ввода и привычке лишние цифирки упрощают жизнь человеку и тем самым уменьшают кол-во ошибок

*** не сочтите на наглость, но очень интересно, насколько сложно реализовать следующее?:

2. Написано "Target.Count > 1". А как указать список столбцов, где необходимо преобразование?
Чтобы в этих "избранных" столбцах не дописывать год, но чтобы он добавлялся/проставлялся автоматически
(или брался из текущей даты или из какой-то определённой ячеки, например $E$1). Но если введены все три числа, то год не проставлялся бы.

большое спасибо за макрос!
к сожалению, не смог его дополнить-модифицировать

*** не сочтите на наглость, но очень интересно, насколько сложно реализовать следующее?:

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

но, при выполнении этого макроса точка заменилась на пробел!

Я решил вот так попробовать

Но, и это не помогло.

ничто не помогло!
(хотя, если сделать частично Left(s, InStr(s, ".") - 1) + "," то видно, что точку находит правильно, и еще после неё добавляет запятую.)

Пробовал в начале проставлять формат:
Columns("G:G").NumberFormat = "General"
Или
Columns("G:G").NumberFormat = "@"

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Замена символов точки на запятую
Здравствуйте всем! Суть: меняю точку на запятую, при этом макрорекодером пишется код. Замена.

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

Замена точки на запятую
Как в строке заменить все символы точки на запятую (если таковые имеются)?var CurrentVersion .

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

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

Добавлено через 9 минут

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

там надо выбрать "Click here to start download from sendspace"

Добавлено через 22 секунды

нет. Там всё равно ж нет запятой.
Чую, там какое-то автоматическое приведение формата. Но так спереди добавляется "'", пробелы впереди остаются. wp2, у вас, видимо, какая-то нестандартная ситуация. Нужно заново переделать, я так думаю.

ага, рабочий вариант. Спасибо

Интересно, почему в лоб не работает.

Добавлено через 55 секунд

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

Просто думаю у wp2 разделителем тысячных стоит пробел - вот Эксель пробел и ставит вместо запятой. А у меня запятая - и я её вижу в ячейке, но её нет в строке формул, т.е. в самом числе (а это уже число, а не строка).

Это если теми первыми кодами делать.

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

документ создается путём открытия текстового файла. Кодировка 866, разделитель "|"

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

SoftIce, у меня так не работает, на выходе шестизначные числа.
Думаю тут влияет десятичный разделитель - у меня точка.

Странно. У меня работает. Только числа выравниваются вправо.

Выделяешь нужные ячейки - запускаешь макрос.

Да, проверил на домашней системе, где разделитель запятая - работает код
SoftIce, а мой первый так же выдаёт шестизначные числа, но с видимым пробелом посередине, как у wp2.
Т.е. если нужно получить числа и десятичный разделитель запятая - используйте код SoftIce.
А пропавшие нули в конце можно добавить форматом ячейки.
Если же нужен текст, и разделитель точка, то такой вариант (чуть подправил):

Замена точки на запятую в TextBox
Как заменить в, допустим, текстбоксе запись 0.4 на 0,4, то есть заменить точку на запятую?

Калькулятор из одного действия - замена точки на запятую
Люди кто знает как в этом примере разобраться? void __fastcall TForm2::LabeledEdit1Change(TObject.

Ограничение ввода символов в StringGrid и замена точки на запятую
Вопрос таков: как ограничить ввод символов в String Grid т.е, чтоб можно было вводить только.

Запись цифр, которые содержатся в одном столбце через дефис и запятую
Здравствуйте! Помогите решить задачу посредством VB в EXCEL 2003. В столбце "A" содержатся.

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

P.S. Если в таком поле дважды кликнуть мышкой (иил F2) и ENTER, все становится на свои места.

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Замена точки на запятую в столбце
записывался макрос, который во всём столбце заменяет точку на запятую: Columns("J:J").Select.

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

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

Nevsky,
Такая замена обычно осуществляется абсолютно по-другому, т.к. затрагивает глубинную суть представления не только чисел, но и дат, например. Делать это можно вне кода. Иди в Settings/Control Panel/Regional and Language Options/Regional Options/. Далее идешь либо в Customize, либо выбираешь страну, твоего формата.
У тебя нестандартная ситуация?
VladConn

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

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

Подходящим вариантом стало домножение на единицу.

>>>Иди в Settings/Control Panel/Regional and Language Options/Regional Options/. Далее идешь либо в Customize, либо выбираешь страну, твоего формата.

У меня ОС XP, Office 2003 все на русском, часовой пояс по Киеву.

Спасибо за поддержку.

Замена точки на запятую
Как в строке заменить все символы точки на запятую (если таковые имеются)?var CurrentVersion .

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

При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:

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

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

Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:

В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:

Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:

Лечится вся эта история тремя принципиально разными способами.

Способ 1. Заранее в настройках

Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced) :

Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .

После этого можно смело импортировать данные - проблем не будет.

Способ 2. Формулой

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

=--ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )

В английской версии это будет:

=--IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))

Логика здесь простая:

Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT) .
Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.

Способ 3. Макросом

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

Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer - Macros) . Все испорченные числа будут немедленно исправлены.

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

Метод 1: использование инструмента “Найти и заменить”

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

Переходим во вкладку “Главная”, и кликаем по кнопке “Найти и выделить” (значок в виде лупы) в блоке “Редактирование”. Откроется список, где выбираем команду “Заменить”. Или же можно просто нажать комбинацию клавиш Ctrl+H.

Примечание: чтобы не выбирать диапазон ячеек во время настройки параметров в окне “Найти и заменить”, можно сделать это заранее, т.е. сначала выделить ячейки, и затем запустить соответствующий инструмент через кнопки на ленте программы или с помощью сочетания клавиш Ctrl+H.

Метод 2: функция “ПОДСТАВИТЬ”

Давайте теперь рассмотрим функцию “ПОДСТАВИТЬ”, которая также позволяет выполнять замену точек на запятые. Но в отличие от метода, который мы рассмотрели выше, замена значений выполняется не в начальных, а выводится в отдельные ячейки.

в значении аргумента “Текст” указываем координаты первой ячейки столбца, в которой нужно заменить точки на запятые. Сделать это можно вручную, прописав адрес с помощью клавиш на клавиатуре. Или можно сначала кликнуть мышью внутри поля для ввода информации, после чего – по нужной ячейке в таблице.
в значении аргумента “Стар_текст” пишем символ “.” (точка).
для аргумента “Нов_текст” указываем в качестве значения символ “,” (запятая).
значение для аргумента “Номер_вхождения” можно не заполнять.
по готовности щелкаем OK.

Метод 3: использование макроса

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

Метод 4: применение Блокнота

Данный метод реализуется через копирование данных во встроенный в операционную систему Windows редактор Блокнот для последующего редактирования. Порядок действий прдеставлен ниже:

Метод 5: настройка параметров Эксель

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

Метод 6: системные настройки

И напоследок, рассмотрим еще один метод, который схож с тем, который описан выше, но предполагает изменение настроек не Эксель, а операционной системы Windows.

Заключение

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

Читайте также: