0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро убрать тире с ячеек в Excel?

Excel works!

menu

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

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

Имеется номер A 777 AA 01, из которого следует образовать номер A 777 AA 01 согласно формуле.

Функция ПОДСТАВИТЬ в Excel

На первом рисунке мы видим, как работает формула. Давайте посмотрим на состав ниже:

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

И вы заканчиваете A 777AA 01.

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

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

ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel

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

R EPLACE выполняет большинство тех же задач, что и substitute, но делает это от определенного символа. Аргументы следующие:

Пример. Первая буква номера автомобиля должна быть заменена на другую букву. С A777AA01 на B777AA01

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

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

Подставить3

Во многом это похоже на BTR:)

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

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

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

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

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

Как быстро убрать тире с ячеек в Excel?

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

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

Важно: Если вы не можете вводить или изменять данные на рабочем листе, вы можете защитить его от случайного изменения данных. В защищенном рабочем листе можно выбирать ячейки для отображения данных, но нельзя вводить данные в заблокированные ячейки. В большинстве случаев не следует снимать защиту с электронной таблицы, пока не будет получено разрешение от пользователя, который ее создал. Чтобы снять защиту с рабочего листа, нажмите кнопку Снять защиту рабочего листа в группе Изменить на вкладке Рецензирование. Если при включении защиты вы указали пароль, то для снятия защиты рабочего листа его необходимо ввести.

Выберите ячейку листа.

Введите цифры или текст, который нужно ввести, затем нажмите Enter или TAB.

Вы можете ввести данные в новую строку в ячейке, нажав ALT+ENTER после ввода переноса строки.

На вкладке Файл выберите Параметры.

Только для Excel 2007: нажмите кнопку Microsoft Office и выберите настройки Excel.

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

В поле Количество десятичных знаков введите положительное число для количества цифр справа от запятой или отрицательное число для количества цифр слева от запятой.

Значение будет равно 2,834, если вы введете 3 в Число десятичных знаков, а затем 2834 в ячейку. При использовании -3 в Число десятичных знаков и -283, вы получите 283000.

Введите нужное число в ячейку.

Параметр Фиксированный десятичный формат не оказывает влияния на уже введенные данные.

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

Выделите ячейку в рабочем листе.

Читайте так же:
Как быстро преобразовать время в текст или количество часов / минут / секунд в Excel?

Введите дату или время в форме ниже.

Вы можете отделить дату дефисом или точкой, например. 09/05/2002 или 5-Sen-2002.

Чтобы ввести время в 12-часовом формате, введите a или p, отделенные пробелом от значения времени, например, 9:00 p. В противном случае значение будет введено как время первой половины дня.

Текущие дату и время можно ввести, нажав CTRL+SHIFT+; (точка с запятой).

Вы можете использовать функции TODAY и DATE для ввода даты или времени, которые останутся в силе при повторном открытии листа.

Когда вы вводите дату или время в ячейку, она отображается в формате по умолчанию компьютера или в формате, примененном к ячейке до ввода даты или времени. Формат даты или времени по умолчанию зависит от настроек даты и времени в диалоговом окне «Параметры языка и региона» (Панель управления, Часы, Язык и регион). Если вы измените эти настройки на компьютере, значения даты или времени, которые не были отформатированы с помощью команды Формат ячеек, будут отображаться в соответствии с этими настройками.

Чтобы применить формат даты или времени по умолчанию, щелкните ячейку даты или времени и нажмите CTRL+SHIFT+# или CTRL+SHIFT+@.

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

Введите данные в активной ячейке и нажмите CTRL+ENTER.

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

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

Щелкните на ярлыке первого рабочего листа, содержимое которого вы хотите изменить. Затем, удерживая клавишу CTRL, щелкните на ярлыках других рабочих листов, с которыми вы хотите синхронизировать данные.

Кнопки прокрутки листов

Использование кнопок прокрутки листа позволит вам найти ярлык, а затем щелкнуть его. Если окно документа нельзя развернуть, метки листов не будут видны.

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

В активной ячейке введите новые данные или отредактируйте существующие, затем нажмите ENTER или TAB, чтобы переместить выделение в следующую ячейку.

Это изменение вступит в силу на всех выбранных листах.

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

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

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

Щелкните Новое окно в группе Окно на вкладке Вид.

Читайте так же:
Как автоматически изменить все изображения для перемещения и размера с ячейками в Excel?

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

Повторите шаги 1 и 2 для каждого листа, который необходимо просмотреть.

Опция Упорядочить все доступна в группе Окно на вкладке Вид.

Если вы хотите просматривать листы только в активной книге, вы можете выбрать Только окна текущей книги в диалоговом окне Расположение окон.

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

Изменение направления нажатия клавиши ENTER

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

Нажатие клавиши Enter перемещает курсор на одну ячейку вниз, а нажатие клавиши TAB — на одну ячейку вправо. Направление движения клавиши TAB нельзя изменить, но его можно изменить для клавиши Enter. Изменение этого параметра влияет на всю электронную таблицу, все другие открытые электронные таблицы и книги, а также на новые книги.

Перейдите на вкладку Файл и выберите Параметры.

Только Excel 2007: нажмите кнопку Microsoft Office и выберите Excel параметры.

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

Изменение ширины колонок

Бывают случаи, когда в ячейке отображается строка типа #####. Число или дата могут отображаться в ячейке неправильно, если ширина столбца не позволяет отобразить все символы. Представьте, например, что дата 31.12.2015 находится в столбце даты с форматом «дд.мм.гггг». Однако ширина столбца достаточна только для отображения шести символов. Он отображает строку #####. Чтобы увидеть все содержимое ячейки с текущим форматом, необходимо увеличить ширину столбца.

Щелкните ячейку, для которой необходимо изменить ширину столбца.

На вкладке Главная в группе Ячейки нажмите на кнопку Формат.

Группа "Ячейки" на вкладке "Главная"

Выделите элемент Размер ячейки и сделайте одно из следующих действий

Если размер ячеек недостаточно велик, выберите команду Автоширина столбцов.

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

Столбец или отдельную ячейку также можно сделать шире, изменив формат. Например, вы можете изменить формат даты, чтобы отображать только день и месяц (формат дд.мм), например 31.12, или представить число в экспоненциальном формате, например 4E+08.

Перемещение текста в ячейки

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

Просто щелкните ячейку, в которой должен появиться текст.

На вкладке Главная в группе Выравнивание выберите команду Переместить текст.

Группа "Выравнивание" на вкладке "Главная"

Примечание: Если текст представляет собой одно длинное слово, символы не будут перемещаться; вы можете расширить колонку или уменьшить размер шрифта, чтобы сделать весь текст видимым. Если после включения переноса текста не весь текст виден, возможно, необходимо изменить высоту строки. На вкладке Главная в группе Ячейки нажмите Формат, а затем в разделе Размер ячеек выберите Автоматически регулировать высоту строки.

Читайте так же:
Как быстро распечатать скрытые листы в Excel?

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

Изменение формата числа

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

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

Выберите ячейку с нужными вам цифрами.

На вкладке Главная в группе Числовые нажмите на стрелку рядом с полем Формат цифр и выберите нужный формат.

Список "Числовой формат" на вкладке "Главная"

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

Форматирование цифр как текста

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

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

На вкладке Главная в группе Число нажмите стрелку рядом с полем Формат числа и выберите Текст.

Список "Числовой формат" на вкладке "Главная"

Пожалуйста, введите нужные вам числа в отформатированной ячейке.

Числа, которые были введены до применения форматирования текста к ячейкам, должны быть повторно введены в отформатированные ячейки. Чтобы ускорить повторный ввод чисел при форматировании текста, нажмите клавишу F2, а затем клавишу Enter.

Дополнительные сведения

Вы всегда можете задать вопрос техническому сообществу Excel или попросить о помощи в сообществе «Ответы».

Как быстро убрать тире с ячеек в Excel?

Приходилось ли вам когда-нибудь импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть также номера социального страхования, номера счетов, номера телефонов, коды товаров или почтовые индексы. В Excel нули из чисел удаляются автоматически, а большие числа (например, 1,23E+15) преобразуются в экспоненциальные представления, чтобы их можно было использовать в формулах и математических операциях. Как сохранить данные в Excel в виде текста, который Excel считает исходным форматом, объясняется в этой статье.

Преобразование чисел в текст при импорте текстовых данных

Чтобы отформатировать отдельные столбцы в виде текста при импорте данных в Excel, используйте функцию Получить и преобразовать (Power Query). Этот метод импортирует текстовый файл, но данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Web, JSON и т.д.

Читайте так же:
Как быстро найти и удалить все строки с ошибками в Excel?

Откройте вкладку Данные, нажмите Получить данные и выберите Из текстового/CSV-файла. Если кнопка Получить данные не отображается, выберите Создать запрос > Из файла > Из текста, найдите нужный файл и нажмите Импорт.

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

Если столбец необходимо преобразовать в текст, выберите его, щелкнув по заголовку, затем выберите Главная > Преобразовать > Тип данных > текст.

Power Query: данные после преобразования в текст

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

В диалоговом окне Изменить тип столбцов выберите Заменить текущие столбцы, и Excel преобразует столбцы в текст.

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

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

В Excel 2010 и 2013 можно импортировать текстовые файлы и преобразовывать числа в текст двумя методами. Мы рекомендуем использовать Power Query (для этого необходимо загрузить дополнение Power Query). При использовании мастера импорта текста можно импортировать данные, даже если вы не можете загрузить надстройку Power Query. Как в примере выше, импортируется текстовый файл, но данные преобразуются так же, как и при импорте данных из других источников, таких как Интернет, XML, JSON и т.д.

На ленте откройте вкладку Power Query и выберите Extract External Data > From Text (Извлечение внешних данных > Из текста).

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

Если есть столбцы, которые нужно преобразовать в текст, выберите их, щелкнув заголовок, а затем выберите Пуск > Преобразование > Тип данных > Текст.

Power Query: данные после преобразования в текст

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

В диалоговом окне Изменение типа столбцов выберите Заменить текущий, и Excel преобразует выделенные столбцы в текст.

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

Вкладка Данные позволяет автоматически обновлять и преобразовывать данные в случае их изменения. На вкладке Данные нажмите Обновить.

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector