Как удалить неанглийские символы в столбце Excel?
Управление данными в Excel часто требует очистки текста, особенно при работе со списками, которые могут содержать нежелательные или несогласованные символы. Например, у вас может быть столбец с именами или записями, где некоторые ячейки содержат неанглийские символы — такие как буквы с акцентами, специальные символы или иностранные письменности — которые вам нужно удалить для обеспечения согласованности данных или для дальнейшей обработки.
Удаление таких символов вручную может быть очень трудоемким и подверженным ошибкам, особенно при работе с большими наборами данных. Для эффективного решения этой задачи Excel предоставляет несколько подходов: вы можете использовать скрипты VBA для автоматического удаления строк, содержащих любые неанглийские символы, или вы можете очистить данные на месте, удалив неанглийские символы из текста, сохраняя при этом структуру данных нетронутой. Здесь мы представляем два практических решения, чтобы помочь вам справиться с этими сценариями.
Удаление строк, содержащих неанглийские символы, с помощью VBA
Удаление неанглийских символов из строк с использованием Kutools для Excel
Удаление неанглийских символов с использованием формулы Excel
Удаление неанглийских символов из текста ячеек с помощью VBA
Удаление строк, содержащих неанглийские символы, с помощью VBA
Если вам нужно полностью удалить все строки, содержащие любые неанглийские символы, из определенного столбца в Excel, использование скрипта VBA может автоматизировать этот процесс. Этот подход особенно подходит, когда вы хотите убедиться, что остаются только ячейки со стандартными английскими алфавитными символами, и любая ячейка с иностранными символами или специальными знаками приведет к удалению всей строки. Этот метод особенно полезен для подготовки чистых наборов данных для импорта в системы, которые принимают только стандартные английские символы.
Однако следует отметить ограничение: этот метод нацелен на один столбец за раз; вам нужно запустить скрипт отдельно для каждого столбца, если вы хотите применить его в другом месте. Кроме того, после выполнения удаленные строки нельзя восстановить, если вы не создали резервную копию или не можете использовать функцию Отменить.
Вот пошаговое руководство:
1. Откройте свою книгу Excel, активируйте рабочий лист, содержащий столбец, который вы хотите обработать, и нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic for Applications. Это позволит вам получить доступ к встроенной в Excel среде программирования VBA.
2. В редакторе VBA нажмите Вставить > Модуль, чтобы создать новый модуль, затем скопируйте и вставьте следующий код VBA в появившееся окно кода:
VBA: Удаление неанглийских символов
Sub RemoveNonEnglish()
'UpdatebyExtendoffice20171204
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim xRows As Long
Dim xAsc As Long
On Error Resume Next
Set xRg = Application.InputBox("Select single column:", "KuTools For Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
xRows = xRg.Rows.Count
Set xRg = xRg(1)
For I = 1 To xRows
Set xCell = xRg.Offset(I - 1)
If xCell.Value <> "" Then
For J = 1 To Len(xCell.Value)
xAsc = Asc(UCase(Mid(xCell.Value, J, 1)))
If xAsc < 65 Or xAsc > 90 Then
xCell.EntireRow.Delete
I = I - 1
Exit For
End If
Next
End If
Next
Application.ScreenUpdating = True
MsgBox "Completed...", vbInformation
End Sub
3. Нажмите F5 или щелкните Выполнить кнопку для выполнения кода. Появится диалоговое окно, спрашивающее вас "Выберите один столбец". Используйте мышь, чтобы выбрать столбец, который вы хотите обработать на вашем листе, затем нажмите OK.
4. Подтвердите свой выбор, нажав OK в последующих диалоговых окнах. Скрипт затем проверит каждую ячейку в выбранном столбце. Если он обнаружит любой символ, который не является прописной или строчной английской буквой (A-Z), он удалит всю строку, содержащую эту ячейку. После завершения вы увидите сообщение о том, что процесс завершен.
 |  |  |
Примечание: Этот скрипт VBA работает только для однократного выбора одного столбца. Рекомендуется сохранить вашу книгу перед запуском кода, так как удаленные строки нельзя восстановить после операции. Если ваши данные большие или содержат специальное форматирование, настоятельно рекомендуется создать резервную копию заранее.
Советы по устранению неполадок: Если вы видите сообщение об ошибке или ничего не происходит, убедитесь, что вы выбрали действительный столбец и только один столбец (не диапазон, охватывающий несколько столбцов). Для работы этого скрипта необходимо включить VBA. Если редактор VBA заблокирован или ограничен в вашей среде, рассмотрите возможность предоставления прав или использования альтернативного метода.
Применимость: Этот подход лучше всего подходит, если вы хотите убедиться, что ваши данные включают только записи с чисто английскими буквами, и вам не нужно сохранять строки, содержащие какие-либо специальные или иностранные символы. Однако, если ваша цель состоит в том, чтобы сохранить данные, просто удалив неанглийские символы, но не удаляя целые строки, рассмотрите использование решения Kutools или других методов ниже.
Удаление неанглийских символов из строк с использованием Kutools для Excel
Если вы хотите очистить текст, удалив неанглийские символы внутри ячеек — без удаления каких-либо строк — функция Удалить символы в Kutools для Excel предлагает быстрое и практичное решение. Это подходит для ситуаций, где вы хотите сохранить общую структуру данных, но удалить любые специальные, акцентированные или иностранные символы внутри строк, оставляя видимыми только обычные английские буквы.
Например, этот подход идеально подходит для стандартизации имен, кодов продуктов или любых буквенно-цифровых записей, которые могли быть введены неправильно или скопированы из внешних источников, в результате чего появились различные символы или неанглийские символы. По сравнению с ручной очисткой, это снижает риск пропуска нежелательных символов и ускоряет процесс.
 | |  |
1. Выберите диапазон, содержащий текстовые строки, которые вы хотите очистить, затем перейдите к Kutools вкладке на ленте Excel, нажмите Текст, и выберите Удалить символы из выпадающего меню. См. скриншот ниже для справки:
2. В Удалить символы диалоговом окне, которое появляется, отметьте опцию для Небуквенные (что нацелено на неалфавитные символы, включая цифры, пунктуацию и специальные или неанглийские символы). Предварительный просмотр обновляется автоматически, чтобы показать вам, как будет выглядеть текст после удаления неанглийских и специальных символов.
3. После подтверждения, что предварительный просмотр соответствует вашим ожиданиям, нажмите OK. Все неанглийские символы (т.е., всё, что не является стандартной английской буквой) мгновенно удаляются из выбранного диапазона, в то время как остальные ваши данные остаются нетронутыми.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Если вам часто нужно очищать текст в Excel, подход Kutools предоставляет удобный пользовательский интерфейс и эффективно обрабатывает большие диапазоны данных всего за несколько кликов. Он также предлагает поддержку отмены, поэтому вы можете быстро отменить изменения, если результаты не соответствуют вашим потребностям.
Советы и предостережения: Опция 'Небуквенные' также удалит любые числа или специальные знаки препинания в тексте. Если вам нужно сохранить числа или другие определенные символы, экспериментируйте с различными флажками в диалоговом окне, пока не добьетесь желаемого результата. Всегда проверяйте панель предварительного просмотра, чтобы подтвердить эффект перед нажатием OK.
Сравнение: Этот метод более гибкий, чем удаление строк через VBA, поскольку он удаляет только нежелательные символы, а не всю строку, что менее разрушительно и безопаснее, если есть риск потери важной информации, связанной с каждой строкой.
Удаление неанглийских символов с использованием формулы Excel
Если у вас нет доступа к Kutools или вы предпочитаете не использовать VBA, вы также можете использовать формулу Excel для удаления неанглийских символов (т.е., всего, что не является буквой английского алфавита) из текста в ячейке. Этот подход подходит для ситуаций, где вы хотите недеструктивное, основанное на формуле решение, которое можно применить к каждой строке и легко скопировать в другие ячейки.
1. Введите следующую формулу массива в первую ячейку вашего выходного столбца (например, B1, если ваши исходные данные находятся в столбце A):
=TEXTJOIN("",TRUE,IF((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=65)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=90)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<=122),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))Эта формула проверяет каждый символ в ячейке A1, сохраняет его только если это прописная (A-Z) или строчная (a-z) английская буква, и объединяет результат в очищенную строку.
2. После ввода этой формулы нажмите Ctrl+Shift+Enter (для версий Excel раньше Office 365; в Office 365 и позднее вы можете просто нажать Enter). Скопируйте формулу для всех соответствующих строк, перетаскивая маркер заполнения или дважды щелкнув его в правом нижнем углу ячейки.
Объяснение параметров: Эта формула использует функции MID, CODE (которая возвращает ASCII код), INDIRECT и ROW для обработки каждого символа в целевой ячейке. Сохраняются только буквы a-z или A-Z.
Предостережения: Этот подход не изменяет исходные данные; выходные данные являются результатами формул в целевом столбце. Если вам нужны фиксированные, статические значения, скопируйте результаты и используйте Вставить значения, чтобы перезаписать формулы.
Ограничения: Длина текста влияет на производительность формулы. Этот метод может не правильно обрабатывать многозначные Unicode графемы, поэтому некоторые визуально неанглийские символы могут остаться, если они представлены составными Unicode последовательностями.
Удаление неанглийских символов из текста ячеек с помощью VBA
Если вы хотите удалить неанглийские символы внутри текста ячеек (без удаления целых строк), вы также можете использовать VBA для более настраиваемой автоматизации. Это практично для задач очистки данных, которые должны выполняться часто, и где использование дополнений не является вариантом.
1. Откройте Excel, затем нажмите Alt + F11, чтобы запустить редактор Microsoft Visual Basic for Applications. Перейдите к Вставить > Модуль и вставьте следующий код в окно модуля:
Sub RemoveNonEnglishCharactersFromCells()
' Remove non-English characters, keep only a-z, A-Z
Dim rng As Range
Dim cell As Range
Dim i As Long
Dim ch As String
Dim output As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range to clean (cells will be altered):", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each cell In rng
output = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If (Asc(ch) >= 65 And Asc(ch) <= 90) Or (Asc(ch) >= 97 And Asc(ch) <= 122) Then
output = output & ch
End If
Next i
cell.Value = output
Next cell
Application.ScreenUpdating = True
MsgBox "Cleanup complete.", vbInformation
End Sub2. Вернитесь в Excel, выберите диапазон, который вы хотите очистить, вернитесь в редактор VBA и запустите код, нажав F5 или щелкнув кнопку Выполнить. При появлении запроса выберите диапазон для обработки и подтвердите.
Результаты: Этот код обрабатывает каждую выбранную ячейку, удаляет любой символ, который не является стандартной английской буквой, и обновляет ячейку очищенным результатом. Исходные данные будут перезаписаны, поэтому разумно сделать резервную копию вашего листа или работать с копией.
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Демонстрация: Удаление неанглийских символов из строк с использованием Kutools для Excel
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек