Как в excel заменить число на другое. Изменение заголовков столбцов в Excel с цифр на буквы

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

Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).

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

Поиск информации

Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:

• Выберите место поиска в выпадающем меню Искать (на листе, в книге)
• В выпадающем меню Просматривать, укажите Excel вариант просмотра по строкам или по столбцам.
• Задайте Область поиска в формулах, значениях или примечаниях
• С помощью опций Учитывать регистр и Ячейка целиком, вы можете указать, следует ли производить поиск с учетом регистра символов и ищется ли все содержимое ячейки или только фрагмент.

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

Замена информации

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

Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки,щелкните Заменить все. Если замещениепошлоне по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.

Поиск форматов

Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.

В появившемся диалоговом окне, перейдите во вкладку Шрифт и выберите Полужирное начертание. Щелкните ОК.

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

Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.

Хитрости использования Найти и заменить Excel

Замена переноса строки (Alt+Enter) в ячейке

Предположим, у вас имеется таблица с ячейками, данные в которых перенесены по строкам с помощью сочетания клавиш Alt+Enter и требуется заменить символ переноса строк (каретку) на пробел.

Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).

Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!

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

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

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

Выделите и скопируйте данные из ниже приведенной таблицы:

Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.

Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.

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

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

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



Способ 1 замена точки на запятую в Excel через Блокнот

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

Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.

Это весьма простой, но очень эффективный способ.

Способ 2 временно меняем настройки Excel

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

Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.

После выполнения вычислений настоятельно рекомендуется вернуть настройки по умолчанию.

Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.

Способ 3 временно меняем системные настройки Windows

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

Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» - вводим нужное нам значение. Дальше ОК и ОК.

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

Способ 4 используем функцию найти и заменить в Excel.

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

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

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

Все точки сменились на запятые. А текст автоматически преобразился в число.

Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:

Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.

Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.

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

Инструкция

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

Параметры Excel

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

Стиль ссылок

Для того чтобы решить вопрос, как поменять в Excel цифры на буквы, выбираем пункт «Формулы». Он находится слева в открывшемся окне настроек. Отыскиваем параметр, который отвечает за работу с формулами. Именно первый пункт в указанной секции, отмеченный как «Стиль ссылок», определяет, как будут обозначаться колонки на всех страницах редактора. Для того чтобы заменить числа на буквы, убираем соответствующую отметку из описанного поля. Манипуляцию такого типа можно произвести при помощи и мыши, и клавиатуры. В последнем случае используем сочетания клавиш ALT + 1. После всех проделанных действий нажимаем кнопку «OK». Таким образом будут зафиксированы внесенные в настройки изменения. В более ранних версиях данного программного обеспечения кнопка доступа в главное меню имеет другой внешний вид. Если используется издание редактора «2003», используем в меню раздел «Параметры». Далее переходим к вкладке «Общие» и изменяем настройку «R1C1». Вот мы и разобрались, как поменять в Excel цифры на буквы.

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

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

Для вызова окна «Найти и заменить» необходимо выделить некий , в котором будет производиться подбор значений (в случае, когда выделение диапазона не будет, поиск произойдет на всем рабочем листе). Потом в панели управления, закладка «Главная» в блоке «Редактирование», нажимаете иконку с выпадающим меню «Найти и выделить» и становится доступным варианты «Найти» и «Заменить». Также эта возможность доступна при нажатии на сочетание Ctrl + F. В окне «Найти и заменить» доступно две вкладки: «Найти», если вы ищите, какую-либо информацию и «Заменить», используете в случаях, когда текст требует замены. Кроме всего можно использовать и расширенный функционал поиска, кликнув на иконку «Параметры».

Ищем в документе

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

• В выпадающем меню «Искать» записываете место поиска: рабочий лист или целая книга;
• В меню «Просматривать» указываем, какой вариант поиска вам нужен: по столбикам или по строкам;
• Задаем в строке «Область поиска», в каких местах нужно искать: в значениях, примечаниях или формулах;
• Пунктами «Ячейка целиком» или «Учитывать регистр» указываете на необходимость учитывать регистр поисковых символов, и будет ли искаться содержимое в области фрагментно или полностью.

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

Замена найденной информации

Для замены аргумента необходимым, вызываете окно «Найти и заменить» в Excel и выбираете вкладку «Заменить». В поле «Найти» вносите то значение, который требует замены, а уже в поле «Заменить» записываете, на что же собственно нужно поменять. При желании или необходимости, можно изменить параметры с расширенным функционалом, который я описывал выше.
Для аккуратной и детальной замены необходимо нажимать иконку «Найти далее», тогда Excel выделяет первое совпадение и тогда, если нужна замена, нажимаете «Заменить» если нет снова кликаете «Найти далее». Если же вы уверены в замене значений тогда просто кликаете на «Заменить все», система только выдаст вам сообщение о количестве произведенных замен. В случае, когда произошла в замещении можно отменить действия, с помощью панели быстрого доступа нажав значок «Отменить» или просто сочетанием горячих клавиш Ctrl + Z.

Ищем по форматам данных

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

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

Хитрости и примеры использования возможностей «Найти и заменить» в Excel

Я использую для примера собственный опыт применения. Очень часто я произвожу анализ посещаемости своих статей, для их и улучшения поведенческих факторов и читаемости. Для этого с Яндекс.Метрики я выгружаю отчет, имеющий следующий вид (статьи выбраны произвольно):
Как видите, сервис прикрепляет к названию статьи еще и название сайта, а мне для анализа это совсем не надо. Поэтому часть строки « | Понятный Excel» я найду и удалю с заменой, подставив для замены только пробел « ».
Теперь кликаем на иконку «Заменить все» и видим диалоговое о произведённых заменах. Вот и всё сделано, как и нужно! Кроме встроенных возможностей поиска и замены в панель управления Excel, есть еще возможность производить эти операции с помощью

Мы уже разбирали как быстро данные в Excel по группе ячеек — в столбце или массиве данных. Но как это сделать в одной ячейке и желательно формулой? К примеру у вас есть список машин, и каждый день вам присылают файл с новым списком машин, нужно проверить есть ли такие номера в вашем основном списке. Но как обычно в номера машин добавляют пробелы или слеши, а самый худший вариант букву О вместо нуля Если вы регулярно выполняете одну и ту же операцию, то вручную нажимать Ctrl + H каждый раз неудобно, плюс придется запоминать какие именно символы нужно заменять. Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel это отличный выход из ситуации.

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

Есть номер А 777 АА 01 из него надо формулой сделать А777АА01 формулой.

Как работает формула показано на первой картинке. Состав разберем ниже:

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])

• Текст — адрес ячейки или сам текст в котором надо сделать замену символов, в примере А 777 АА 01
• Стар_текст — что нам нужно заменить? Пробел.
• Нов_текст — на что нужно заменить? Меняем на ничего, т.е. на пусто в формуле, это будет «»
• Номер_вхождения — заполнять необязательно, но при этом реквизит очень полезный, в официальной справке говорится о «вхождении символа», но объясню простым языком. Здесь вы можете указать номер символа в строке (пробела в нашем случае). Т.е. если нужно заменить только пробел между 777 и АА (это второй по счету пробел в номере), формула будет выглядеть как

=ПОДСТАВИТЬ(A:A;" ";"";2 )

И в итоге вы получите А 777АА 01

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

ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A:A;" ";"");".";"");"/";"")

В данном случае мы убираем пробел, точку, и слэш в номере «А 777 АА/01.»

Функция ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Excel

Функция ЗАМЕНИТЬ делает почти все тоже самое, что и подставить, но делает это с определенного символа. Аргументы такие:

ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)

Пример. Необходимо первую букву номера машины заменить на другую. Из А777АА01 сделать Б777АА01

Сначала описание аргументов:

• Стар_текст — как и в ПОДСТАВИТЬ, текст в котором нужно заменить т.е. А777АА01
• Начальная_позиция — с какого символа производить замену, с первого.
• Число_знаков сколько знаков меняем — один
• Нов_текст — и на что меняем? На букву Б

Очень похоже на , честно говоря:)

ЗАМЕНИТЬБ — это маленькая загадка Excel, даже в иностранных интернетах мало информации о ней. Если вкратце, то просто ЗАМЕНИТЬ меняет любой символ (даже если он размером 2 байта), ЗАМЕНИТЬБ меняет только однобайтные символы (аргумент число_знаков меняется на). Практического применения я для себя не нашел:) Если кто-нибудь подскажет, буду благодарен!

UPD. Как мне подсказали, к двухбайтовым символам относятся большинство языков с ироглифами!

ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Что еще?

Обе функции учитывают регистр! Т.е. если вы указали большую букву А, маленькая буква заменяться не будет!

Функция ПОДСТАВИТЬ доступна только в версиях 2007 и выше, будьте аккуратны если конечный пользователь будет использовать более ранние версии!

Поделитесь нашей статьей в ваших соцсетях: