Как заменить точку на запятую в Excel и наоборот

Если вы попали на эту статью, значит тоже столкнулись с проблемой, что в русской локали Excel числа по умолчанию удобнее разделять запятой. А если к вам перекочевал документ из англоязычной версии или из Google Таблиц (а у меня еще и через импорты программ), то их числа с точками могут открываться как обычные строки, а все формулы будут поломаны. Ну ничего страшного, в этой статье мы обсудим рабочие варианты, как можно правильно массово заменить точку на запятую в Excel. Попробую сделать все как можно проще и без воды. Поехали!

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

Перед массовой заменой сделайте копию файла: даты, IP-адреса и ссылки тоже содержат точки.

Способ 1 – Простая замена

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

1. Итак, имеем стандартную таблицу, но замечаем в ней, что числа в некотором столбце отображаются как простые строки (если определять на глаз – то правильные числа по умолчанию выравниваются вправо, а вот строки влево). С такими «числами» в формулах не поработать. Нужно исправлять. Если рядом появляется зеленый уголок ошибки, отдельно посмотрите, как преобразовать текст в число в Excel.

2. Выделяем эти данные. Делайте это как удобнее вам – выделю целый столбец щелчком по его заголовку (здесь буква «I»). Вы можете просто выделить их мышкой, как и любые другие ячейки в Windows. Можно щелкнуть в начале, зажать Shift и щелкнуть по последней ячейке – так будет выделен весь диапазон. А вообще на нашем сайте есть отдельная статья по профессиональному выделению ячеек в Excel. Переходим и читаем. А к этому моменту я буду считать, что вы все успешно выделили.

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

4. В появившемся окошке в поле «Найти» вводим точку, а в поле «Заменить на» запятую. Все остальное можно оставить как есть (а можно и пробежаться глазами, вдруг чего пригодится) и нажимаем на кнопку «Заменить все».

5. После успешной замены строки автоматически становятся числами:

А еще обратите внимание: в окне замены напротив поля «Заменить на» есть кнопка «Формат». Там можно вручную задать числовой формат, если запятая уже заменилась, но ячейка осталась текстовой. Подробнее – про формат ячеек в Excel.

Способ 2 – Формула (для продвинутых)

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

Можете пользоваться мастером функций, но я сразу здесь пишу верный вариант (на примере ячейки А2, вы подставляете свои):

=ПОДСТАВИТЬ(A2;”.”;”,”)

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

Если нужно сразу получить число, используйте: =–ПОДСТАВИТЬ(A2;”.”;”,”).

Способ 3 – Макрос (для очень продвинутых)

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

Sub my_replacer()
Selection.Replace What:=”.”, Replacement:=”,”
End Sub

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

Способ 4 – Блокнот и CSV

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

1. Щелкаю правой кнопкой мыши по файлу CSV, выбираю «Открыть с помощью» и открываю его через «Блокнот» или аналог вроде «NotePad++». Результат уже открытого файла CSV:

2. Выделяю весь текст (Ctrl + A) и делаю привычную нам замену точки на запятую через пункт «Изменить – Заменить». После замены не забываем сохранить файл.

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

Способ 5 – Меняем настройки

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

1. Переходим в «Файл – Параметры».
2. В открывшихся параметрах слева выбираем «Дополнительно», ищем раздел «Параметры правки». И тут снимаем галочку с «Использовать системные разделители», а в поле «Разделитель целой и дробной части» указываем точку. В поле «Разделитель групп разрядов» лучше поставить другой знак, например пробел. Пользуемся.

Такое же можно провернуть на уровне самой Windows (в настройках форматов «Язык и региональные стандарты»), но это уж точно выходит за пределы статьи. Надеюсь, до такого никому не захочется идти. Суть метода – делаем в Windows забугорный разделитель, а Excel сам подхватит правильную настройку, тогда и не нужно будет менять точку на запятую.

После всех замен выделите несколько ячеек и посмотрите, считает ли Excel сумму внизу окна. Если нет, читаем, почему Excel не считает сумму выделенных ячеек.

Видео по теме

YouTube