Как убрать в номере телефона в эксель
Когда меня спрашивают, как удалить в Excel лишние символы, я не могу не задать ряд встречных вопросов:
- Что послужило причиной называть их лишними и избавиться от них?
- Что конкретно подразумевает процедура удаления? Мы будем непременно удалять их, или заменим символы на какие-то другие, или, может быть, перенесем в другой столбец?
- Точно ли имеет смысл удалять сами символы? Может быть, стоит удалить из текста слова, в которых они содержатся? Или и вовсе содержимое ячеек целиком?
- Не проще ли вместо удаления этих символов рассматривать операцию как извлечение из текста определенных символов кроме этих, удаляемых?
Примеры лишних символов
Лишние пробелы
Наиболее часто ненужными считаются повторяющиеся пробелы между словами или пробелы в конце и начале ячейки. Как убрать их, можно узнать тут: Удалить лишние пробелы в Excel
Лишние символы справа / слева
Кто-то видит лишними символы справа или слева от основного текста в ячейке, желая отрезать их от него по позиции или по определенной границе.
Когда же границей является определенный символ и нужно удалить все что перед ним, читаем тут:
Цифры
Бывает, что ненужными символами становятся цифры, которых 10 и хочется более быстрый способ, чем очищать строки от них методом замены на пустоту. Как удалить цифры из текста в ячейках — этот раздел даст ответ на вопрос.
Буквы, латиница, кириллица
Аналогично сложно удалить разом все буквы алфавита, которых 26 или 33 в случае с английскими и русскими символами соответственно. Подробные гайдлайны — Удалить латиницу в Excel.
Удалить все, кроме…
Часты случаи, когда лишними считаются вообще все символы, кроме определенных. Тут речь уже больше об извлечении нужных символов, а не об удалении ненужных.
Удалить все, кроме букв и цифр (удалить пунктуацию)
Удалить все символы, кроме букв и цифр, а иначе говоря, пунктуацию, нетривиальная задача, ведь их могут быть сотни! Но она решается, смотрите статью.
Удалить все, кроме цифр (извлечь цифры)
Номера телефонов, почтовые коды, числовые артикулы, IP адреса… иногда страдают от наличия в ячейках других символов, помимо цифр. Читайте: удалить все, кроме цифр в ячейках Excel
Удалить все, кроме букв (извлечь буквы)
Когда в данных лишними являются любая пунктуация, цифры и прочие символы, кроме букв алфавита. Это могут быть:
- кириллица
- латиница
- любые буквы
Другое
Хотите узнать, как удалять другие символы в Excel? Оставляйте комментарии под этой статьей.
Другие операции с символами в Excel
Не всегда нужны такие кардинальные меры, как удаление символов. Иногда необходимо просто обнаружить их наличие, извлечь или заменить на какие-то другие. В решении подобных задач вам помогут соответствующие разделы сайта:
Также Microsoft Excel способен на полную мощность задействовать возможности регулярных выражений. Буквы, цифры, знаки препинания, специальные символы — им подвластна работа с любыми данными. Подробнее читайте в статье — регулярные выражения в Excel.
Хотите быстро удалять любые лишние символы или пробелы в ваших таблицах?
!SEMTools существенно расширит возможности вашего Excel.
2 комментария:
Добрый вечер! Есть ли возможность удалить все цифры и буквы, написанные в нижнем регистре и оставить только caps look слова?
Дмитрий Тумайкин :
Преднастроенной возможности нет, но можно извлечь слова по регулярному выражению (ознакомьтесь с их синтаксисом, открывает огромные возможности!). Выражение будет ^[A-ZА-ЯЁ]+$
У нас ввели учет клиентов в Excel когда-то. Единого стандарта не было, как и контроля за введенными данными. Сотрудники в столбце номера телефонов ввели все что угодно, от любых знаков, цифр и текста.
Получалось так:
244-33-33 многоканальный секретарь Катя, 256-65-56
(8555) 566-06-06, - указанные телефоны это тел ТЭЦ 2
Сейчас эти файлы нужно загрузить в CRM систему Битрикс. Однако когда загружаешь, то в системе все отображается с текстом. Контактов более 1000, так что в ручную очень очень долго.
Пробовал в excel:
Формат ячеек-все форматы, формулы подставлял - итог никаких изменений
Подскажите пожалуйста как можно "превратить" в Excel значения в столбце с
"244-33-33 многоканальный секретарь Катя, 256-65-56"
на
244-33-33
256-65-56
"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".
Вариантов решения два:
Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:
Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
?
1
2
3
4
5
6
7
8
9
Sub Apostrophe_Remove()
For Each cell In Selection
If Not cell.HasFormula Then
v = cell.Value
cell.Clear
cell.Formula = v
End If
Next
End Sub
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Rus = "асекорхуАСЕНКМОРТХ"
Eng = "acekopxyACEHKMOPTX"
For Each cell In Selection
For i = 1 To Len(cell)
c1 = Mid(cell, i, 1)
If c1 Like "[" & Eng & "]" Then
c2 = Mid(Rus, InStr(1, Eng, c1), 1)
cell.Value = Replace(cell, c1, c2)
End If
Next i
Next cell
End Sub
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Очистить и переформатировать телефонные номера в Excel
Очистите и переформатируйте телефонные номера в Excel с помощью функции ЗАМЕНА
Чтобы очистить все форматирование телефонных номеров, функция ЗАМЕНА может помочь вам заменить ненужные символы (дефисы, точки и круглые скобки) пустой строкой. Общий синтаксис:
- text : Текстовая строка, содержащая символ или текст, который вы хотите заменить;
- old_text : Текст, который вы хотите заменить;
- new_text : Текст, которым нужно заменить;
- instance_num : Необязательный. Указывает, какое вхождение old_text вы хотите заменить. Если не указано, заменяются все экземпляры.
1. Примените следующую формулу в пустую ячейку, в которой вы хотите получить результат:
2. Затем перетащите дескриптор заполнения вниз в ячейки, к которым следует применить эту формулу, и все форматирование телефонных номеров будет немедленно очищено, см. Снимок экрана:
3. Затем скопируйте ячейки формулы и вставьте их в другое место как значения.
5, Затем нажмите OK Кнопка, и теперь к номерам применен новый формат номера телефона, см. снимок экрана:
Пояснение к формуле:
В приведенной выше формуле есть комбинация пяти функций ЗАМЕНА, дополнительный «+0» в конце формулы используется для преобразования телефонных номеров в числовой формат. Вы можете написать формулу, как показано на скриншоте ниже, с этим форматом вы можете легко добавить другие или удалить замены.
Очистите и переформатируйте телефонные номера в Excel с помощью удобной функции
Если вы не знакомы с функцией ЗАМЕНА, чтобы удалить все ненужные символы из телефонных номеров, вы можете применить Kutools for Excel's Удалить символы Функция, с помощью этой функции вы можете удалять числовые, буквенные, непечатаемые или буквенно-цифровые символы из текстовых строк всего за несколько щелчков мышью. Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!
Используемая относительная функция:
- ЗАМЕНА:
- Функция SUBSTITUTE заменяет текст или символы в текстовой строке другим текстом или символами.
Другие статьи:
- Преобразование чисел в текст в Excel
- Возможно, существует несколько способов преобразования чисел в текстовый формат на листе Excel, но в этой статье я расскажу о некоторых функциях для решения этой задачи.
- Преобразование текста в число в Excel
- Числа, хранящиеся в текстовом формате, не могут быть вычислены в Excel обычным образом, в этом случае вам следует преобразовать текст в общее число. В этой статье я расскажу о некоторых функциях для работы с этой работой в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Предположим, что в ячейках вашего рабочего листа есть список почтовых индексов или длинных номеров ISBN, а длинные номера содержат тире, для какой-то цели вам нужно избавиться от тире между числами (как показано на следующих снимках экрана). Есть ли какие-нибудь быстрые способы убрать эти дефисы между числами в excel?
Удалить тире из ячеек с формулой
С этой ЗАМЕНА функции, числа будут преобразованы в текстовые значения, поэтому, когда вы удалите дефис, ни начальный 0 не будет удален, ни числа не станут экспонентами. Пожалуйста, сделайте следующее:
1. В соседней пустой ячейке, например C1, введите следующую формулу: = ПОДСТАВИТЬ (A1; "-"; "") , см. снимок экрана:
2, нажмите Enter и выберите ячейку C1, затем перетащите маркер заполнения в диапазон, который вы хотите содержать эту формулу. И дефисы между цифрами убраны. Смотрите скриншот:
Легко удаляйте все числовые, нечисловые или указанные символы, такие как "тире", из ячеек в Excel:
Kutools for Excel's Удалить символы Утилита поможет вам легко удалить все числовые, нечисловые или указанные символы из выбранных ячеек в Excel.
Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
Удалите тире из ячеек с кодом VBA
С помощью следующего кода VBA вы также можете удалить дефисы между числами из диапазона ячеек.
1. Выделите диапазон, в котором вы хотите убрать тире.
2. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
3. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
VBA: убрать тире между числами
4, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, в котором вам будет предложено выбрать диапазон, который вы хотите использовать. Смотрите скриншот:
5, Затем нажмите OK, тире между числами убраны с ячеек. И числовой формат преобразуется в текстовый формат. Смотрите скриншоты:
Удалите тире из ячеек с помощью Kutools for Excel
Если вы новичок в Excel, и формула, и код для вас трудны, не волнуйтесь, Kutools for ExcelАвтора Удалить символы Функция может помочь вам быстро удалить тире из ячеек.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите диапазон ячеек, в которых необходимо удалить тире, и нажмите Кутулс > Текст > Удалить символы, см. снимок экрана:
2. В Удалить символы диалоговое окно, отметьте На заказ вариант и введите "-»В текстовое поле, затем вы сможете просмотреть результаты предварительный просмотр Панель. Затем нажмите кнопку OK кнопку, чтобы удалить все дефисы в выбранных ячейках. Смотрите скриншот:
Внимание. Если выбранные ячейки содержат начальные нули или много цифр, чтобы сохранить начальные нули и избежать превращения числа в научную нотацию после удаления дефисов, вам необходимо отформатировать ячейки как текст, как показано на скриншоте ниже, прежде чем применять Удалить символы утилита.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Если вы недавно начали использовать сенсорные устройства, рекомендуем изучить несколько жестов, с помощью которых можно максимально эффективно использовать возможности этих устройств.
Перемещение по файлу
Коснитесь экрана и проведите пальцем вверх и вниз, влево и вправо.
Раздвиньте кончики двух пальцев.
Сожмите кончики двух пальцев.
Выделение
Выделение нескольких ячеек
Коснитесь ячейки, а затем перетащите маркер выделения.
Быстрое выбор всех ячеек с содержимым
Коснитесь ячейки, а затем проведите по кнопке выделения в нужном направлении.
Коснитесь строки формул.
Добавление или редактирование содержимого ячейки
Дважды коснитесь ячейки или коснитесь строки формул.
Скрытие экранной клавиатуры
Открытие контекстного меню для ячейки
Дважды коснитесь ячейки или нажмите и удерживайте ее.
Работа со столбцами или строками
Выбор или изменение столбца или строки
Коснитесь заголовка столбца или строки.
Перемещение одной или нескольких ячеек либо столбца или строки
Коснитесь и удерживайте выбранные столбец или строку и перетащите выделенные данные в нужное место.
Изменение размера столбца или строки
Коснитесь заголовка столбца или строки и перетащите его от индикатора двойной линии на краю.
Читайте также: