В некоторых случаях вам может потребоваться разделить значения выбранных ячеек, разделенных запятыми, на строки или столбцы. Здесь мы познакомим вас с тремя способами детального решения этой проблемы.
Разделить значения, разделенные запятыми, на столбцы с помощью функции Text to Columns
Разделить значения, разделенные запятыми, на строки с кодом VBA
Быстро разделяйте значения, разделенные запятыми, на строки или столбцы с Kutools for Excel
Разделить значения, разделенные запятыми, на столбцы с помощью функции Text to Columns
Вы можете применить функцию «Текст в столбцы» для разделения значений выбранных ячеек, разделенных запятыми, на столбцы. Пожалуйста, сделайте следующее.
1. Выберите диапазон ячеек, значения которого вы хотите разделить на столбцы, а затем щелкните Данные > Текст в столбцы. Смотрите скриншот:
2. В первом Мастер преобразования текста в столбцы в диалоговом окне выберите разграниченный вариант, а затем щелкните Download кнопку.
3. В секунду Мастер преобразования текста в столбцы диалоговое окно, только отметьте Запятая коробка в Разделители и нажмите Download кнопку.
4. В последний Мастер преобразования текста в столбцы диалоговом окне выберите ячейку для размещения значений разделения в Направление поле и, наконец, щелкните Завершить кнопка. Смотрите скриншот:
Теперь все значения в выбранных ячейках, которые были разделены запятыми, разделены на разные столбцы, как показано ниже.
Как правило, Текст в столбцы функция только разбивает ячейки на отдельные столбцы, если вы хотите разбить ячейки на строки, как вы можете это сделать?
Здесь Kutools for Excel‘s Разделить клетки Утилита может помочь вам быстро разделить ячейки по пробелу, запятой, новой строке, другим разделителям или определенной ширине не только на отдельные строки, но и на столбцы в Excel. Скачать Kutools for Excel сейчас! (30— дневная бесплатная трасса)
Разделить значения, разделенные запятыми, на строки с кодом VBA
Для разделения значений, разделенных запятыми, на строки можно применить следующий код VBA.
1. нажмите другой + F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модули. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.
Код VBA: разделить значения, разделенные запятыми, на строки
Sub SplitAll()
Dim xRg As Range
Dim xRg1 As Range
Dim xCell As Range
Dim I As Long
Dim xAddress As String
Dim xUpdate As Boolean
Dim xRet As Variant
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count > 1 Then
MsgBox "You can't select multiple columns", , "Kutools for Excel"
Exit Sub
End If
Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
Set xRg1 = xRg1.Range("A1")
If xRg1 Is Nothing Then Exit Sub
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each xCell In xRg
xRet = Split(xCell.Value, ",")
xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
I = I + UBound(xRet, 1) + 1
Next
Application.ScreenUpdating = xUpdate
End Sub3. нажмите F5 ключ для запуска кода. В всплывающем Kutools for Excel диалоговом окне выберите ячейки, которые хотите разделить, а затем щелкните значок OK кнопку.
4. Во втором выскакивающем Kutools for Excel диалоговом окне выберите ячейку для размещения значений разделения, затем щелкните OK.
Затем вы можете увидеть, что значения, разделенные запятыми, в выбранных ячейках разделены на строки, как показано ниже.
Быстро разделяйте значения, разделенные запятыми, на строки или столбцы с Kutools for Excel
The Разделить клетки полезности Kutools for Excel может помочь вам легко разделить значения, разделенные запятыми, на строки или столбцы.
1. Выберите ячейки, которые необходимо разделить, и нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:
2. в Разделить клетки диалоговое окно, выберите Разделить на строки or Разделить на столбцы в Тип раздел как вам нужно. И в Укажите разделитель выберите Другое вариант, введите символ запятой в текстовое поле, а затем щелкните OK кнопка. Смотрите скриншот:
3. И затем появится другое окно с подсказкой, чтобы напомнить вам выбрать ячейку для вывода результата, см. Снимок экрана:
4. Нажмите OK кнопку, вы можете увидеть результаты, как показано на скриншотах ниже.
Разделить на столбцы
Разделить на строки
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Демонстрация: быстрое разделение значений, разделенных запятыми, на строки или столбцы с помощью Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Содержание
- 0.1 Меняем точку на запятую в системных настройках Windows
- 0.2 Как поменять точки на запятые в Excel в определенном месте
- 0.3 Меняем точки на запятые в Excel с помощью формул
- 1 Как в Excel заменить точку, на запятую?
- 1.1 Способ 1 замена точки на запятую в Excel через Блокнот
- 1.2 Способ 2 временно меняем настройки Excel
- 1.3 Способ 3 временно меняем системные настройки Windows
- 1.4 Способ 4 используем функцию найти и заменить в Excel.
- 1.4.1 Как привести дробные числа в Excel к одному виду
- 1.4.2 Временная перенастройка Windows
- 1.4.3 Разделитель в Excel. Автопоиск с автозаменой
- 2 Способы смены точки на запятую
- 2.1 Способ 1: инструмент «Найти и заменить»
- 2.2 Способ 2: использование функции ПОДСТАВИТЬ
- 2.3 Способ 3: применение макроса
- 2.4 Способ 4: использование Блокнота
- 2.5 Способ 5: изменение настроек Excel
- 2.6 Способ 6: изменение системных настроек
- 2.7 Помогла ли вам эта статья?
В дробных числах в Excel могут использоваться в качестве разделителя точки, либо запятые. Во многих англоязычных странах используют точку, в России же чаще используется запятая. Из-за этого часто возникают различные сложности и требуется менять точки на запятые или наоборот.
Если в настройках у вас стоит использование точки в качестве разделителя, то дробные цифры будут восприниматься программой как текст, и с ними невозможно будет производить различные математические действия.
Настройки использования точки или запятой в Excel 2013 меняются следующим образом. Необходимо войти в Файл | Параметры, далее в открывшемся окне выберите пункт Дополнительно и обратите внимание на пункт “Использовать системные разделители”
Если стоит галочка в данном пункте, то Excel будет ориентироваться на системные настройки. Как их изменить мы рассмотрим чуть позже. Если вам необходимо именно сейчас поменять настройки, то необходимо убрать галочку и поставить в пункте “Разделитель целой и дробной части” вместо точки запятую или наоборот.
Важно! Данные которые уже вставлены в Excel автоматически не преобразуются в число после изменения вышеуказанных настроек. Чтобы их преобразовать в число есть различные способы.
1 способ.Выделить и скопировать эти данные, открыть блокнот на вашем компьютере и вставить эти данные в блокнот и заново скопировать, вернуться в Excel и вставить эти данные обратно обычной вставкой.
2 способ. Просто скопировать данные и заново вставить. В этих ячейках должны появится в углу зеленные треугольники, в данном случае Excel нам подсказывает, что числа сохранены как текст.
Чтобы преобразовать текст в числа, вам необходимо выделить эти числа, после этого должен появиться желтый ромбик с восклицательным знаком, нажмите на него и выберете пункт “Преобразовать в число”
Меняем точку на запятую в системных настройках Windows
Очень часто встречается следующая ситуация. Например, вы работаете в каком-нибудь отчете Excel, где используется запятая в качестве разделителя. Периодически вам необходимо выгружать данные из какой-нибудь корпоративной базы, CRM или 1С и вставлять в этот отчет. Но данные из этих систем выгружаются с точками и у вас возникают сложности из-за этого, либо обратная ситуация – вы работаете с точками, а программа выгружает отчет с запятыми.
Так вот в данной ситуации нужно либо настроить программу, если она это позволяет, чтобы данные выгружались с нужным вам разделителем, либо самостоятельно перед выгрузкой поменять у себя на компьютере системные настройки. Те самые которые Excel использует по умолчанию, если стоит галочка напротив этого пункта (смотрите на первый рисунок данной статьи).
Для того, чтобы изменить системные настройки разделителя вам необходимо зайти в меню Пуск | Панель управления | Часы, язык и регион, далее в разделе Язык и региональные стандарты выбрать пункт Изменение форматов даты, времени и чисел
Откроется окно Язык и региональные настройки, перейдите во вкладку Форматы | Дополнительные настройки, откроется окно Настройка формата, во вкладке Числа в поле Разделитель целой и дробной части: поменяйте точку на запятую либо наоборот и нажмите ОК
После этого можете запускать выгрузку отчета из корпоративной программы, CRM или 1C и ваш отчет будет выгружен с нужным вам знаком. После выгрузки отчета не забудьте поменять настройки обратно, иначе у вас некорректно может работать сам исходный отчет.
Как поменять точки на запятые в Excel в определенном месте
Нередки ситуации, когда работая с отчетом Excel, где используются в качестве разделителя, например, запятые, а вам присылают данные с точками и стоит задача поменять запятые на точки или наоборот. Самый простой способ – это выделить все данные с точками, нажать одновременно Ctrl+H (либо необходимо перейти (Excel 2007—2013) во вкладку Главная, раздел Редактирование, выбрать бинокль и в выпадающем меню выбрать пункт Заменить
Откроется окно замены, необходимо в поле найти ввести точку, а в Заменить на – запятую либо наоборот в зависимости от ваших нужд и нажать заменить все. Напоминаем, что перед этим желательно выделить те ячейки с данными в которых необходимо поменять точки на запятые, иначе они поменяются во всех данных на листе, в том числе, если они встречаются в тексте или других данных.
Если вам требуется делать это часто, то можно записать простенький макрос для автоматизации этих действий. Привязать этот макрос к горячим клавишам или кнопке и менять точки на запятые и обратно одним щелчком.
Макрос замены точки на запятую в выделенных ячейках
Sub Макрос_замены_точки_на_запятую() Selection.Replace What:=".", Replacement:="," End Sub
Макрос замены запятой на точку в выделенных ячейках
Sub Макрос_замены_запятых_на_точки() Selection.Replace What:=",", Replacement:="." End Sub
Принцип работы с данными макросами будет прост, просто выделяете нужные ячейки, строки или столбцы с данными, где необходимо поменять точки на запятые или наоборот и запустите соответствующий макрос любым удобным для вас способом.
Важно! При работе с макросами невозможно будет отменить последние действия, поэтому будьте внимательны.
Меняем точки на запятые в Excel с помощью формул
Бывают ситуации, когда в отчете мы работает с точками, но где-то необходимо отразить данные с помощью запятых, например, в диаграммах. Либо как и с ситуацией выше нам присылают данные с другими разделителями и мы хотим эти разделители менять автоматически или у вас есть отчет, в Excel используются запятые, есть сотрудники которые заполняют данные, например, показания каких-нибудь приборов. Сколько вы не бьетесь с ними, они все равно указывают данные то с точками, то с запятыми. В данном случае, можно просто сделать еще один столбец в котором с помощью формулы точки будут меняться на запятые или наоборот.
Для этих целей мы можем использовать функцию ПОДСТАВИТЬ. Данная функция позволяет менять один текст на другой в нужном нам тексте.
Синтаксис данной функции:
ПОДСТАВИТЬ(текст; стар_текст; нов_текст; )
Рассмотрим пример, у нас есть в столбце A данные с запятыми, а мы работает с точками, поэтому эти данные воспринимаются как текст и наша задача состоит в изменении запятых на точки с помощью формул Excel.
В ячейке B1 прописываем формулу
=ПОДСТАВИТЬ(A1;",";".";1)
A1 – это текст, в нашем примере это текст в нашего числа в ячейке A1
«, » – это стар_текст, то есть тот текст который нам необходимо заменить, в нашем случае это запятая.
«. » – это нов_текст то есть тот текст на который нам необходимо заменить старый, в нашем случае это точка.
Последний аргумент мы указали 1 – это необязательный аргумент, по умолчанию Excel подставляет 1, означает, что поиск старого текста происходит с 1 знака. Это может пригодится, если, например, в тексте будет 2 запятые, а нам необходимо заменять вторую запятую, а не первую.
Далее мы протягиваем формулу и получаем список и цифр с точкой, но это еще не всё, преобразованные числа так и остались текстом, поэтому их необходимо конвертировать в числа. Для этого мы будем использовать функцию ЗНАЧЕН
Достаточно предыдущую функцию добавить в ЗНАЧЕН и текстовые данные преобразуются в числовые. Функция будет выглядеть следующим образом.
=ЗНАЧЕН(ПОДСТАВИТЬ(A1;",";".";1))
Обратите внимание! В данном примере мы работает с точками, поэтому функция будет работать корректно, если же в такой же ситуации мы будем менять точки на запятые, а числовые разряды в настройках у нас разделяются точками, то функция будет выдавать ошибку.
Точка вместо запятой дробных чисел может привести к существенным последствиям при расчетах в программе Excel. Чаще всего такие ошибки случаются, когда импортируются данные в таблицу из других источников.
Если в дробных числах вместо запятой стоит точка, то программа автоматически воспринимает их как текстовый тип данных. Поэтому перед тем как выполнять математические расчеты и вычисления следует отформатировать и подготовить импортированные данные.
Выделите и скопируйте данные из ниже приведенной таблицы:
| № п/п | Валютная пара | Пересчет | Продажа | Покупка | Спрэд |
| 1 | EUR/USD | 1 | 1.1347 | 1.1350 | 0.0003 |
| 2 | GBP/USD | 1 | 1.5438 | 1.5442 | 0.0004 |
| 3 | USD/CHF | 1 | 0.9543 | 0.9547 | 0.0004 |
| 4 | USD/JPY | 100 | 1.1948 | 1.1945 | -0.0003 |
Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.
Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.
Примечание. Если копировать данные из других источников без специальной вставки, то вместе с данными копируется и формат. В таком случаи формат ячеек «Общий» (по умолчанию) может быть изменен. Тогда нельзя визуально отличить, где распознано число, а где текст.
Все последующие действия необходимо выполнять с чистого листа. Удалите все, что есть на листе или откройте новый для дальнейшей работы.
Чтобы заменить точку, на запятую в импортированных данных можно использовать 4 способа:
Способ 1 замена точки на запятую в Excel через Блокнот
Программа Блокнот Windows не требует использования сложных настроек и функций, а лишь выступает посредником в копировании и предварительной подготовки данных.
- Скопируйте данные из исходной таблички на этой странице. Откройте программу Блокнот Windows («Пуск»-«Все программы»-«Стандартные»-«Блокнот») и вставьте в него скопированные данные для подготовки.
- Выберите в меню «Правка» опцию «Заменить» (или комбинацию горячих клавиш CTRL+H). В появившимся диалоговом окне, введите в поле: «Что» точку (.), а в поле «Чем» запятую (,). И нажмите кнопку «Заменить все».
Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.
Это весьма простой, но очень эффективный способ.
Способ 2 временно меняем настройки Excel
Перед тем как поменять точку на запятую в Excel правильно оцените поставленную задачу. Возможно, лучше заставить программу временно воспринимать точку, как разделитель десятых в дробных числах. Просто в настройках мы указываем, что в дробных числах разделитель точка вместо запятой.
Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.
После выполнения вычислений настоятельно рекомендуется вернуть настройки по умолчанию.
Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.
Способ 3 временно меняем системные настройки Windows
Принцип этого способа похож на предыдущий. Только здесь мы меняем похожие настройки в Windows. В настройках региональных стандартов операционной системы нужно заменить запятую, на точку. Теперь подробнее о том, как это сделать.
Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» — вводим нужное нам значение. Дальше ОК и ОК.
Внимание! Если вы откроете этот файл на другом компьютере, где установлены другие системные параметры региональных стандартов, то могут возникнуть проблемы с вычислениями.
Способ 4 используем функцию найти и заменить в Excel.
Данный способ похож на первый. Только здесь мы используем ту же функцию из Блокнота, но уже в самом Excel.
В этом способе, в отличие от выше приведенных, мы сначала вставляем скопированную табличку на чистый лист, а потом подготавливаем ее к выполнению вычислений и расчетов.
Важным недостатком данного способа является сложность его реализации, если некоторые дробные числа с точкой после вставки были распознаны как дата, а не как текст. Поэтому мы сначала избавимся от дат, а потом разберемся с текстом и точками.
- Предварительно выделите столбцы где будут находится дробные числа с точкой в качестве разделителя. В данном случаи это 3 столбца D:F.
- Установите для выделенного диапазона текстовый формат ячеек, чтобы заранее избежать автоматического преобразования в формат даты некоторых чисел. Для этого выберите текстовый формат из выпадающего списка на закладке «Главная» в разделе «Число». Или нажмите CTRL+1 , в появившимся окне «Формат ячеек» выберите закладку «Число», а в разделе «Числовые форматы» укажите «Текстовый».
- Скопируйте таблицу и щелкните правой кнопкой мышки по ячейке A1. Из контекстного меню выберите опцию «Специальная вставка». Выберите «Текст в кодировке Unicode» и нажмите ОК. Обратите внимание, как теперь отображаются значения в ячейках: D3, D5, E3, E5, в отличии от самого первого копирования таблицы.
- Нажмите на инструмент «Главная»-«Найти и выделить»-«Заменить» (или нажмите комбинацию CTRL+H).
- В появившемся окне введите в поле «Найти» — точку, а в во второе поле введите запятую. И нажмите «Заменить все».
- Снова выделите 3 столбца D:F и измените формат ячеек на «Числовой» CTRL+SHIFT+1. Не забудьте увеличить разрядность до 4-х, как описано в пункте №2.
Все точки сменились на запятые. А текст автоматически преобразился в число.
Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:
Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.
Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.
Разное написание десятичных дробей (через точку или запятую) при составлении отчетов или формул приводит к неверным расчетам, поскольку компьютер распознает только однотипные данные. Исторически сложилось, что англоязычные страны применяют в дробях точку для отделения целого числа, а на всех остальных языках, включая искусственные (как языки программирования), эту роль выполняет запятая. И именно програмисткий вариант использует за основу запятую как разделитель в Excel.
Вместе с тем в быту мы одинаково используем либо точку либо запятую. Поэтому зачастую, если импортировать в экселевский документ числовые сведения из разных источников, из управленческих программ, почты, текстовых файлов и так далее, разделить в Excel может быть даже слешем (!). Чтобы формулы считались исправить несколько видимых несовпадений несложно и вручную. С большими объемами данных, у табличного редактора есть вполне достойные возможности.
Как привести дробные числа в Excel к одному виду
Скорректировать числовой разделитель в Excel получится 3 способами:
1. Изменив настройки самого редактора.
2. Изменив настройки Windows.
3. Применив специальную Excel-функцию.
В первом случае привязка Эксель к системным параметрам убирается и устанавливается свое обозначение делителя дроби. В русскоязычных версиях ОС по умолчанию стоит разделитель-запятая. Следовательно, способ нужно выбирать тогда, когда все дроби удобнее снабдить точками, вместо запятых (проанализируйте свой документ, возможно, это именно тот случай).
Чтобы заменить все «дробные» запятые на точки в документе Excel-2007, следует пройти в меню редактора по адресу: Значок в левом верхнем углу — Параметры Excel — Дополнительно (в более ранних Excel версиях: Файл-Параметры-Дополнительно), а затем убрать флажок на «Использовать системные разделители». А в окне «Разделитель целой и дробной части» поставьте тот символ, который необходим.
У способа 2 важных нюанса. Во-первых, после завершения расчетов лучше вернуть базовые настройки. А во-вторых, коррекция произойдет, если применить действие до передачи или копирования сторонних данных, а не после.
Временная перенастройка Windows
Этот алгоритм удобен тогда, когда ведется регулярная выборка статистик для какого-то одного отчета из разных автономных баз данных 1С, SAP, CRM и так далее. То есть текущий (стандартный) разделитель системы нужно оставить, а точечный заменить. Или наоборот.
Некоторые тематические служебные программы содержат функцию автоматической настройки нужного разделителя. Когда это невозможно, для корректной работы достаточно сменить системные установки операционной системы компьютера. А именно: активировать Панель управления через меню Пуск, в ней – Часы, язык и регион. Из открывшегося списка выбрать «Язык и региональные стандарты», пункт «Изменение форматов даты, времени и чисел».
В открывшемся окне есть кнопку «Дополнительные параметры…». Перейдя по ней можно установить нужный вид делителя из контекстного меню строки «Разделитель целой и дробной части». Подтвердите выбор нажатием «Ок» или Ввод.
Теперь выгрузка из нескольких ваших систем произойдет с корректными символами дробных показателей.
Разделитель в Excel. Автопоиск с автозаменой
Если нет желания возиться с системными настраиваниями, есть возможность открыть сначала стороннюю таблицу на отдельном листе Excel, подогнать ее там под нужный формат, а потом присоединить к основному отчету.
На начальном этапе здесь могут подстерегать сюрпризы. Например, некоторые числа с точкой между значениями могут автоматически стать датами. Чтобы этого избежать, перед вставкой отчетного фрагмента подготовьте проблемные зоны:
1. Выделите столбцы, где будут размещены дробные данные.
2. Горячей клавишей Ctrl+1 вызовите «Формат ячеек» (либо через вкладку «Главная», подраздел «Число»).
3. В формате ячеек среди перечня меню «Число, числовые форматы» отыщите режим «Текстовый» и активируйте его.
4. Вставьте таблицу.
5. Выделив ячейку А1, вызовите контекст-меню, а там – «Специальную вставку».
6. Следующий шаг – установите «Текст в кодировке Юникод».
7. Еще раз выделите проблемные столбцы (из пункта 1), отформатируйте ячейки, как «Общие» или «Числовые» по алгоритму из пункта 2. Попутно проставьте разрядность.
8. Горячей клавишей Ctrl+H откройте меню поиска и замены (или: «Главная», «Найти и выделить», «Заменить»).
9. Проставьте нужные значения в полях «Найти» и «Заменить», запустите операцию кнопкой «Заменить все».
Последние 2 пункта выполняет также формула =ЗНАЧЕН(ЗАМЕНИТЬ(*;НАЙТИ(«.»;*;1);1;»,»)), где * — первая ячейка изменяемого сегмента. Формулу следует применить на отдельной области, после чего подставить полученные значения в нужный диапазон.
Поделитесь нашей статьей в ваших соцсетях:
(Visited 10 421 times, 1 visits today)
Многие пользователи программы Эксель сталкиваются с вопросом замены точек на запятые в таблице. Это чаще всего связано с тем, что в англоязычных странах принято отделять десятичные дроби от целого числа точкой, а у нас – запятой. Хуже всего то, что числа с точкой не воспринимаются в русскоязычных версиях Excel, как числовой формат. Поэтому, именно данное направление замены настолько актуально. Давайте разберемся, как поменять точки на запятые в программе Microsoft Excel различными способами.
Способы смены точки на запятую
Существует несколько проверенных способов смены точки на запятую в программе Эксель. Одни из них полностью решаются с помощью функционала данного приложения, а для применения других требуется использование сторонних программ.
Способ 1: инструмент «Найти и заменить»
Самый простой способ замены точек на запятые — это воспользоваться возможностями, которые предоставляет инструмент «Найти и заменить». Но, и с ним нужно вести себя осторожно. Ведь, при неправильном его использовании будут заменены все точки на листе, даже в тех местах, где они действительно нужны, например, в датах. Поэтому, этим способом нужно пользоваться осторожно.
- Находясь во вкладке «Главная», в группе инструментов «Редактирование» на ленте жмем на кнопку «Найти и выделить». В появившемся меню переходим по пункту «Заменить».
- Открывается окно «Найти и заменить». В поле «Найти» вставляем знак точки (.). В поле «Заменить» — знак запятой (,). Кликаем по кнопке «Параметры».
- Открываются дополнительные настройки поиска и замены. Напротив параметра «Заменить на…» кликаем по кнопке «Формат».
- Открывается окно, в котором мы сразу можем установить формат изменяемой ячейки, какой бы она до этого не была. В нашем случае главное установить числовой формат данных. Во вкладке «Число» среди наборов числовых форматов выделяем пункт «Числовой». Жмем на кнопку «OK».
- После того, как мы вернулись в окно «Найти и заменить», выделяем весь диапазон ячеек на листе, где нужно будет выполнить замену точки на запятую. Это очень важно, ведь если вы не выделите диапазон, то замена произойдет по всему листу, что не всегда нужно. Затем, жмем на кнопку «Заменить все».
Как видим, замена выполнена успешно.
Урок: замена символов в Excel
Способ 2: использование функции ПОДСТАВИТЬ
Ещё одним вариантом замены точки на запятую является использование функции ПОДСТАВИТЬ. Правда, при использовании этой функции замена происходит не в исходных ячейках, а отображается в отдельной колонке.
- Выделяем ячейку, которая станет самой первой в колонке для вывода измененных данных. Кликаем по кнопке «Вставить функцию», которая располагается слева от места нахождения строки функций.
- Запускается Мастер функций. В списке, представленном в открытом окне, ищем функцию ПОДСТАВИТЬ. Выделяем её и жмем на кнопку «OK».
- Активируется окно аргументов функции. В поле «Текст» нужно ввести координаты первой ячейки столбца, где располагаются числа с точками. Это можно сделать, просто выделив эту ячейку на листе с помощью мыши. В поле «Стар_текст» вставляем точку (.). В поле «Нов_текст» ставим запятую (,). Поле «Номер_вхождения» заполнять не нужно. Сама функция будет иметь такой шаблон: «=ПОДСТАВИТЬ(адрес_ячейки;».»;»,»)». Жмем на кнопку «OK».
- Как видим, в новой ячейке у числа уже вместо точки запятая. Теперь нам нужно сделать подобную операцию для всех других ячеек столбца. Конечно, вводить для каждого числа функцию не нужно, есть намного более быстрый способ выполнить преобразование. Становимся на правый нижний край ячейки, которая содержит преобразованные данные. Появляется маркер заполнения. Удерживая левую кнопку мыши, тянем его вниз до нижней границы области, содержащей преобразуемые данные.
- Теперь нам нужно присвоить ячейкам числовой формат. Выделяем всю область преобразованных данных. На ленте во вкладке «Главная» ищем блок инструментов «Число». В выпадающем списке меняем формат на числовой.
На этом преобразование данных закончено.
Способ 3: применение макроса
Заменить точку на запятую в Экселе можно также при помощи макроса.
- Прежде всего, нужно включить макросы и вкладку «Разработчик», если они у вас не включены.
- Переходим во вкладку «Разработчик».
- Жмем на кнопку «Visual Basic».
- В открывшееся окно редактора вставляем следующий код:
Sub Макрос_замены_точки_на_запятую()
Selection.Replace What:=".", Replacement:=","
End SubЗакрываем редактор.
- Выделяем область ячеек на листе, которую нужно преобразовать. Во вкладке «Разработчик» жмем на кнопку «Макросы».
- В открывшемся окне представлен список макросов. Выбираем из списка «Макрос замены запятых на точки». Жмем на кнопку «Выполнить».
После этого, выполняется преобразование точек на запятые в выделенном диапазоне ячеек.
Внимание! Очень внимательно применяйте данный способ. Последствия действия этого макроса необратимы, поэтому выделяйте только те ячейки, к которым желаете его применить.
Урок: как создать макрос в Microsoft Excel
Способ 4: использование Блокнота
Следующий способ предполагает копирование данных в стандартный текстовый редактор Windows Блокнот, и изменение их в этой программе.
- Выделяем в Excel область ячеек, в которых нужно заменить точку на запятую. Кликаем правой кнопкой мыши. В контекстном меню выбираем пункт «Копировать».
- Открываем Блокнот. Делаем клик правой кнопкой мыши, и в появившемся списке кликаем по пункту «Вставить».
- Кликаем по пункту меню «Правка». В появившемся списке выбираем пункт «Заменить». Или же, можно просто набрать на клавиатуре комбинацию клавиш Ctrl+H.
- Открывается окно поиска и замены. В поле «Что» ставим точку. В поле «Чем» — запятую. Жмем на кнопку «Заменить все».
- Выделяем измененные данные в Блокноте. Кликаем правой кнопкой мыши, и в списке выбираем пункт «Копировать». Или жмем на клавиатуре сочетание клавиш Ctrl+C.
- Возвращаемся в Эксель. Выделяем диапазон ячеек, где следует заменить значения. Кликаем по нему правой кнопкой. В появившемся меню в разделе «Параметры вставки» кликаем по кнопке «Сохранить только текст». Либо, жмем сочетание клавиш Ctrl+V.
- На весь диапазон ячеек устанавливаем числовой формат тем же способом, как делали это ранее.
Способ 5: изменение настроек Excel
Как один из способов преобразования точки в запятые, можно использовать изменение настроек программы Excel.
- Переходим во вкладку «Файл».
- Выбираем раздел «Параметры».
- Переходим в пункт «Дополнительно».
- В разделе настроек «Параметры правки» снимаем галочку с пункта «Использовать системные разделители». В активировавшееся поле «Разделитель целой и дробной части» ставим точку. Жмем на кнопку «OK».
- Но, данные сами собой не изменятся. Копируем их в Блокнот, а после вставляем на то же место обычным способом.
- После завершения операции рекомендуется вернуть настройки Эксель по умолчанию.
Способ 6: изменение системных настроек
Этот способ похож на предыдущий. Только на этот раз мы меняем не настройки Excel. А системные настройки Windows.
- Через меню «Пуск» входим в «Панель управления».
- В Панели управления переходим в раздел «Часы, язык и регион».
- Переходим в подраздел «Язык и региональные стандарты».
- В открывшемся окне во вкладке «Форматы» жмем на кнопку «Дополнительные настройки».
- В поле «Разделитель целой и дробной части» меняем запятую на точку. Жмем на кнопку «OK».
- Копируем данные через Блокнот в Excel.
- Возвращаем прежние настройки Windows.
Последний пункт очень важен. Если его не выполнить, то вы не сможете проводить привычные арифметические действия с преобразованными данными. Кроме того, возможно некорректно будут работать и другие программы, установленные на компьютере.
Как видим, существует несколько способов замены точки на запятую в программе Microsoft Excel. Конечно, большинство пользователей предпочитают для данной процедуры использовать наиболее легкий и удобный инструмент «Найти и заменить». Но, к сожалению, в некоторых случаях с его помощью не удаётся корректно преобразовать данные. Вот тогда на помощь могут прийти и другие варианты решения проблемы.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
Разделение текста на столбцы с помощью мастера распределения текста по столбцам
С помощью мастера распределения текста по столбцам текст, содержащийся в одной ячейке, можно разделить на несколько.
Проверьте, как это работает!
-
Выделите ячейку или столбец с текстом, который вы хотите разделить.
-
На вкладке Данные нажмите кнопку Текст по столбцам.
-
В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
-
Выберите разделители для своих данных. Например, запятую и пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.
-
Нажмите кнопку Далее.
-
В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.
-
Нажмите кнопку Готово.
См. также
Разделение текста по столбцам с помощью функций
Нужна дополнительная помощь?
Если ячейка содержит текст, разделенный запятой или любым другим знаком, и вы хотите разбить их на несколько столбцов, вам следует следовать этому руководству. Эта статья поможет вам разделить текст, разделенный запятыми в Excel и Google Таблицах без использования стороннего программного обеспечения.
Предположим, у вас есть электронная таблица, содержащая полное имя некоторых людей, и вы хотите разделить имя и фамилию на два столбца. Вместо имен он может включать в себя что угодно, например, список продуктов, ваши услуги и цену или что-то еще. Если рядов мало, можно сделать это вручную. Проблема возникает, когда вам нужно сделать то же самое с сотнями столбцов в Microsoft Excel или Google Sheets. Чтобы избавиться от этой трудоемкой работы, вы можете ознакомиться с этим пошаговым руководством.
Чтобы разделить текст, разделенный запятыми, в Excel, выполните следующие действия:
- Откройте электронную таблицу и выберите все ячейки.
- Перейдите на вкладку «Данные».
- Нажмите на опцию Text to Columns.
- Используйте параметр с разделителями и выберите разделитель.
- Выберите ячейку, в которой вы хотите отобразить результат.
- Сохраните ваш файл.
Для начала вам нужно открыть электронную таблицу в Microsoft Excel и выбрать все ячейки, содержащие текст, разделенный командами. После этого перейдите в Данные вкладку и нажмите Текст в столбцы вариант.
У вас будет два варианта — С разделителями и Фиксированная ширина. Вам нужно выбрать С разделителями вариант и щелкните Следующий кнопка.
После этого выберите разделитель. Поскольку в вашей электронной таблице есть запятая, вам нужно выбрать ее из списка. Однако можно выбрать что-то еще, например табуляцию, точку с запятой, пробел или что-нибудь другое. Наконец, нажмите на Следующий кнопка.
Теперь вам нужно выбрать ячейку, которая будет первым столбцом.
Возвращаясь к руководству, вам нужно удалить default Пункт назначения значение и выберите ячейку, в которой вы хотите отобразить результат. Наконец, нажмите кнопку Заканчивать кнопка.
Какую бы ячейку или столбец вы ни выбрали, в следующей следующей будет последняя часть текста, разделенного запятыми.
Например, если-
- У вас есть такой текст — AA, BB
- Выберите B1 для AA
Часть BB будет сохранена в ячейке C1. Если исходный текст содержит две запятые, самая последняя часть будет отображаться в ячейке D1.
Теперь вы можете сразу увидеть результат.
Как разделить текст через запятую в Google Таблицах
В этом случае Google Таблицы не так умны, как Microsoft Excel. Хотя вы можете разделить все тексты через запятую, вы не можете сохранить исходный столбец. Если вас это устраивает, выполните следующие действия.
- Выделите все ячейки в вашей электронной таблице.
- Перейдите в Данные> Разделить тексты на столбцы.
- Если хотите, измените разделитель.
Сначала вам нужно выделить все ячейки, содержащие значения, разделенные запятыми. После этого разверните Данные меню и выберите Разделить текст на столбцы вариант.
По умолчанию он автоматически определяет разделитель или ограничитель. Если вы используете что-то уникальное или оно не выполняет свою работу безупречно, вы можете расширить Разделитель раскрывающийся список и выберите что-нибудь еще в соответствии с вашими требованиями.
Вот и все!
Данные, импортированные из внешних источников, могут потребовать некоторой очистки. С помощью разделителей вы можете разделить такие данные на разные ячейки, чтобы лучше организовать их и сделать более значимыми.
Разделитель разделяет каждую часть данных в этой строке. В качестве разделителя может использоваться любой символ, например запятая, пробел, табуляция или точка с запятой. Однако разделить данные по ячейкам в Excel не так просто. Итак, давайте посмотрим, как управлять данными с помощью разделителей в электронной таблице.
Выберите ваши данные
Прежде чем начать, вам нужно выбрать все данные, которые вы хотите использовать. Вот как это сделать:
- Откройте новый лист Excel. Введите данные образца в первый столбец листа.
- Введите список имен нескольких певцов и попробуйте разделить эти имена на имена и фамилии в отдельные ячейки.
- Выберите данные, которые вы хотите разделить, с помощью мыши или клавиш Ctrl и стрелки вниз.
Использование параметра «Текст в столбцы»
После выбора данных вы можете разделить имена на имя и фамилию. Посмотрим как!
- Щелкните параметр « Данные» на ленте Excel.
- Щелкните значок « Текст в столбцы» в разделе « Инструменты для работы с данными » на ленте.
- Когда вы выбираете текст в столбцы , на экране открывается мастер преобразования текста в столбцы . Мастер поможет разбить текст на три этапа.
- Выберите вариант с разделителями и нажмите кнопку Далее .
- На следующем экране выберите Пробел в качестве разделителя и снимите флажки с других разделителей.
- После этого нажмите кнопку « Далее» . Окно предварительного просмотра данных покажет, как Excel разделит данные.
- Вы можете выбрать пункт назначения, в котором вы хотите сохранить выходные данные. MS Excel принимает первую ячейку выбранных данных в качестве пункта назначения . Однако для заполненного листа вы должны не забыть выбрать ячейку назначения . В противном случае Excel перезапишет ваши исходные данные. Обратите внимание, что если ваши данные изменятся, вам нужно будет снова сделать разграничение текста.
- После этого нажмите кнопку « Готово» . Excel разделит данные на два столбца с именами и фамилиями.
Разделитель помогает установить точку, в которой можно разделить любой текст. «Пробел» – это всего лишь один из видов простого разделителя. Для имени, отчества и фамилии данные можно разделить на три столбца.
Точно так же другие типы разделителей могут помочь вам разделить данные, которые могут не иметь пробелов. Давайте посмотрим на них в следующем разделе.
Типы разделителей
Другими разделителями, помимо разделителя пробелов, которые можно использовать в строках данных, являются запятая , точка с запятой , табуляция и прочее . Мы уже видели , разделитель пространства , когда мы научились использовать текст для опции столбцов. Здесь мы увидим, как работать с разделителями запятая , точка с запятой и другие .
Запятая
Допустим, у нас есть список данных о пяти друзьях и стране, в которой они живут. Мы можем разделить их имена и страну на разные столбцы, используя разделитель-запятую.
- Выберите все данные и нажмите на опцию « Данные» в верхней части экрана.
- Щелкните параметр « Текст в столбцы» в группе « Инструменты для работы с данными » в верхней части листа Excel. На экране появится окно мастера.
- Выберите вариант с разделителями и нажмите кнопку Далее .
- На этот раз выберите запятую в качестве разделителя и нажмите на указанную опцию « Далее» .
- В окне предварительного просмотра данных показано, как Excel разделит данные. Вы можете выбрать пункт назначения, в котором вы хотите сохранить выходные данные.
- После этого нажмите кнопку « Готово» , чтобы разделить данные на столбцы.
Точка с запятой
Вот небольшой набор данных с регистрационными номерами студентов и их возрастом. Вы можете разделить регистрационные номера учащихся и их возраст в разные столбцы, используя разделители точкой с запятой .
На втором шаге мастера выберите в качестве разделителя точку с запятой .
Как вы можете видеть на экране ниже, разделитель разделяет регистрационный номер и возраст учащегося на отдельные столбцы.
Другой
Параметр «Текст в столбцы» также позволяет вам выбрать разделитель, чтобы разделить любую текстовую строку через разделитель « Другой» . Например, вы можете использовать его для разделения адресов электронной почты на имена пользователей и доменные имена, разделенные знаком @ .
Для нашего образца у нас есть список идентификаторов электронной почты. Вы можете разделить имена пользователей и доменные имена на разные столбцы, используя параметр Другие разделители.
Здесь снова вам нужно выбрать опцию Other в качестве разделителя на второй странице мастера настройки.
- Выберите « Другое» в качестве параметра-разделителя.
- Снимите флажки с других разделителей.
- Введите знак @ в поле рядом с параметром Другое .
- По завершении нажмите кнопку « Далее» .
- Используйте окно предварительного просмотра данных, чтобы увидеть, как Excel разделит данные.
- Выберите место назначения для сохранения выходных данных и нажмите кнопку « Готово» . Это разделит адрес электронной почты на имена пользователей и имена доменов в отдельных ячейках.
Настройте текст в столбцы, чтобы упростить преобразование
Вы можете настроить функцию Text to Columns в соответствии с данными, которые вы хотите разделить.
Классификатор текста
Вы можете сообщить Excel, где строка текста начинается и заканчивается символом квалификатора текста . Квалификатор текста обрабатывает данные между двумя квалификаторами текста как одно значение независимо от каких-либо разделителей в квалификаторах текста. Итак, если значение ячейки – «Нил, Армстронг», 12 , и вы указываете апостроф в качестве квалификатора текста и запятую в качестве разделителя, тогда Excel буду относиться Нил Армстронг как одно значение и 12 как другое значение. Excel проигнорирует запятую между «Нил» и «Армстронг».
Если в данных не указан квалификатор текста , все значения будут разделены на разные ячейки с запятой в качестве разделителя.
Используя квалификатор текста, вы можете указать Excel, какие символы отмечают начало и конец информации, которую вы хотите импортировать в Excel.
Предварительный просмотр данных
В поле предварительного просмотра данных отображается окончательный результат, поскольку он будет разделен на разные столбцы на листе.
Вы можете использовать это поле, чтобы проверить вывод, прежде чем закончить преобразование текста.
Относитесь к последовательным разделителям как к одному
Этот вариант полезен, если ваши данные содержат разделитель из более чем одного символа или если ваши данные содержат несколько разделителей. Например, если значение ячейки Neil ,; Армстронг,; 12 , вы можете выбрать « Обрабатывать последовательные разделители как один».
Если вы не отметите этот параметр, MS Excel будет рассматривать точку с запятой и запятую как два разных разделителя и разбивать ячейки на пять столбцов.
Таким образом, эта функция избавит вас от множества проблем при наличии нескольких разделителей.
Квалификаторы текста жизненно важны, когда вы хотите анализировать громоздкие или сложные данные. Разделители помогают нам отделить данные из одной ячейки.
После импорта данных и расстановки их с помощью разделителей вы можетеразделить данные на отдельные листы .
Организация данных в MS Excel – фундаментальный навык. В аналитике данных первым шагом к осмыслению данных является их организация. И разделение данных на отдельные ячейки – первый шаг к этому.