Формула для устранения всех символов, кроме буквенных

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

ПРИМЕР: изменить O'Malley-Smith, Tom, Jr. на OMALLEYSMITHTOMJR

Клиент требует, чтобы это была функция Excel, в противном случае я бы упростил ее с помощью быстрой программы Java, похожей на replaceAll("[^a-zA-Z]", "").toUpperCase(). Кажется, я не могу найти ничего похожего на готовую функцию, чтобы сделать это за пределами целого беспорядка SUBSTITUTE функций, которые, кажется, доступны только по одной на ячейку.

Я не очень хорошо разбираюсь в разработке пользовательских макросов, если это то, что мне нужно.


excel excel-formula vba substitution excel-udf
person dwwilson66    schedule 19.03.2015    source источник

Ответы (6)


arrow_upward
8
arrow_downward

У меня была аналогичная потребность некоторое время назад, и я нашел что-то, что отлично сработало.

Нажмите Alt+F11, чтобы открыть редактор Visual Basic. Вставьте новый модуль и вставьте следующий код.

Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(UCase(Rng), n, 1))
            Case 48 To 57, 65 To 90
                strTemp = strTemp & Mid(UCase(Rng), n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

CleanCode теперь является новой функцией, и вы можете использовать ее как формулу.

Поэтому рядом с ячейкой со строкой, которой вы хотите манипулировать, просто скопируйте =CleanCode(yourcell)

person aurezio    schedule 19.03.2015

arrow_upward
9
arrow_downward

Действительно возможен беспорядок SUBSTITUTE, но в пределах одной ячейки, например:

=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"'",""),".",""),"-",""))

Конечно, может потребоваться «расширение», чтобы охватить другие неальфа-символы.

person pnuts    schedule 19.03.2015
comment
Умный шаблонный подход. - person brettdj; 20.03.2015
comment
мило с твоей стороны, но формулы на самом деле не моя тема. В то время как я не думаю, что у Барри есть ровесники, он просто лучший игрок. - person brettdj; 21.03.2015
comment
Отличное решение для удаления ограниченного количества уникальных нечисловых строк. Сработало для меня, когда мне пришлось преобразовать такие ключи, как 14D, 8M, 9hr и 23min, в числовые значения, просто используя D, M, hr, min в качестве субтитров. - person e_i_pi; 20.04.2020
comment
Отличное решение для устранения нескольких персонажей. Спасибо - person YosiN; 31.07.2021

arrow_upward
8
arrow_downward

Если вы хотите пойти по маршруту VBA — вы не можете использовать определяемую пользователем функцию (UDF) для изменения значения ячейки, в которую вы вводите функцию, — но вы можете использовать простой макрос и воспользоваться преимуществами Microsoft. Механизм регулярных выражений VBScript:

Sub SO()

Dim searchRange     As Excel.Range
Dim cell            As Variant
Dim RegEx           As Object

Set RegEx = CreateObject("VBScript.RegExp")

With RegEx
    .Pattern = "[^a-zA-Z]"
    .Global = True
    .MultiLine = True
End With

Set searchRange = ActiveSheet.Range("A1:D5") '// Change as required

    For Each cell In searchRange.Cells
        If RegEx.test(cell) Then cell.Value = RegEx.Replace(cell.Value, vbNullString)
        cell.Value = UCase(cell.Value)
    Next cell

Set searchRange = Nothing
Set RegEx = Nothing

End Sub
person SierraOscar    schedule 19.03.2015
comment
Regexp — наиболее эффективный метод VBA. Хотя использовал бы это в массиве вариантов, а не в диапазоне - person brettdj; 20.03.2015

arrow_upward
3
arrow_downward

Это можно сделать с помощью одной формулы в Excel 2016 и более поздних версий.

В то время как решение pnuts перечисляет явные символы для удаления, это решение перечисляет явно допустимые символы.

Предположим, что ваши грязные данные находятся в столбце A. Предположим, вы хотите, чтобы ваши чистые данные находились в столбце B.

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

  • Нажмите на ячейку B1
  • Нажмите на строку формул
  • Вставьте формулу
  • Нажмите CTRL+Shift+Enter ‹-- важный шаг

Скопируйте ячейку B1 и вставьте ее в столбец B настолько, насколько вам нужно.

Во-первых, вот краткий пример, объясняющий, что происходит:

=TEXTJOIN("",TRUE,

IFs(
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "t", "t",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "e", "e",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "s", "s",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "T", "T",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "E", "E",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "S", "S",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = "2", "2",
    MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1) = " ", " ",
    true, ""
  )

)

В этом случае я указал следующие символы как допустимые: t, e, s, T, E, S, 2 и символ пробела.

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

Как это работает:

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

Функция 'IFS' обрабатывает свои аргументы попарно за раз. Если первый аргумент оценивается как истина, то возвращается второй аргумент. Если нет, то он переходит к следующей паре аргументов — вот почему вы видите буквы, перечисленные дважды в каждой строке. Последняя пара значений в функции IFS — это набор «истина» и пустая строка. Это говорит о том, что если мы дойдем до конца набора допустимых значений (т.е. и не совпадем с допустимым значением), то вернем пустую строку.

Подробнее о том, почему это работает:

Это вариант решения, представленного в ExcelJet. В этом решении используется функция TEXTJOIN (для объединения значений массива) с функцией INDIRECT (которая разбивает строку на массив) вместе с математическим оператором (символ плюс) для принудительной оценки вычисления между каждым символом в строке с числовым значением. Числовые символы в строке вернут числовые значения, а другие символы вернут ошибку. Это решение использует функцию ISERR для проверки наличия ошибки, чтобы решить, следует ли включать данный символ в окончательный вывод. Там есть похожая статья, чтобы работать наоборот - исключить цифры и сохранить буквы.

Проблема, которую я хотел решить, заключается в том, чтобы кодировщик мог решить, какие значения допустимы, а какие нет. Я попытался объединить функции ВПР и ИНДЕКС с функцией ДВССЫЛ, но они будут работать только с первым символом в строке. Хитрость заключается в том, что не все функции будут воздействовать на на вывод INDIRECT таким образом, чтобы оценить каждый элемент в массиве (т. е. каждый символ в строке). Секрет был в том, что ExcelJet использовал математический оператор. Если вы проверите полную функцию Microsoft , IFS классифицируется как "логическая" функция. Я подозреваю, что логические функции можно использовать с INDIRECT таким образом.

(Примечание: я также пытался использовать И и ИЛИ в различных комбинациях. Однако ДВССЫЛ оценивает все символы в строке. Так, например, используя функцию КОД для получения значения ASCII каждого символа и утверждения все символы должны иметь значения от 65 до 90 (верхний регистр) или 97 - 122 (строчные буквы) будут работать только в том случае, если все символы в строке в верхнем или нижнем регистре, но не в том случае, если есть смесь.)

Я не знаю, как производительность этого решения сравнивается с более ранним предложением с использованием SUBSTITUTE. Если вы хотите удалить только несколько символов, я рекомендую решение SUBSTITUTE. Если вы хотите явно указать допустимые символы для сохранения (это был исходный вопрос), используйте этот.

Наконец, вот точный ответ, который вам нужен, включая преобразование в верхний регистр, которое вы не отметили в вопросе, но отобразили в своем примере. (Для тех, кто не хочет преобразования в верхний регистр, удалите экземпляры «ПРОПИСНЫЕ» из этого примера, затем снова добавьте алфавит в список в нижнем регистре и обязательно оставьте пару «истина»/пустая строка в качестве последней записи. в списке.)

=TEXTJOIN("",TRUE,
IFs(
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "A", "A",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "B", "B",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "C", "C",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "D", "D",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "E", "E",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "F", "F",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "G", "G",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "H", "H",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "I", "I",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "J", "J",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "K", "K",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "L", "L",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "M", "M",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "N", "N",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "O", "O",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "P", "P",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Q", "Q",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "R", "R",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "S", "S",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "T", "T",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "U", "U",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "V", "V",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "W", "W",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "X", "X",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Y", "Y",
    upper(MID(a1,ROW(INDIRECT(CONCATENATE("1:",LEN(a1)))),1)) = "Z", "Z",
    true, ""
)
)

С исходным вопросом «устранить все, кроме альфа-символов» — этот ответ делает трюк в формуле без необходимости в VBA.

person youcantryreachingme    schedule 15.05.2018

arrow_upward
2
arrow_downward

Еще одно решение VBA

Sub RemoveCrap()
    Dim varRange As Range
    Dim varWorkRange As Range

    Set varWorkRange = Range("A1:A10")

    For Each varRange In varWorkRange
        varVal = ""
        For i = 1 To Len(varRange.Value)
            varTemp = Mid(varRange.Value, i, 1)
            If Not (varTemp Like "[a-z]" Or varTemp Like "[A-Z]") Then
                varStr = ""
            Else
                varStr = UCase(varTemp)
            End If
            varVal = varVal & varStr
        Next i
        varRange.Value = varVal
    Next
End Sub
person Pankaj Jaju    schedule 19.03.2015
comment
@user3415869 user3415869 — Концепция будет работать, но не код, потому что Google Sheets использует скрипт Google Apps, тогда как MS Excel использует VBA. - person Pankaj Jaju; 22.11.2019

arrow_upward
2
arrow_downward

Не могу напрямую ответить aurezio, так как только что присоединился и не имеет достаточной репутации. Мне очень нравится это решение, и я нашел его очень эффективным и коротким/простым для моих требований, которые заключаются в очистке текстовых полей для вывода отфильтрованного набора символов в столбцы для имени файла и потребностей вывода веб-кодирования.

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

Ряд 7 относится к номеру символа или числовой серии для разрешения. Используйте функцию Excel CODE() для определения кода символа и CHAR() для размещения кода символа.

Мои критерии (как показано ниже) заключались в том, чтобы отфильтровать все, кроме 45 (тире), от 48 до 57 (числовой ряд), от 65 до 90 (буквенно-цифровой ряд в верхнем регистре), от 97 до 122 (буквенно-цифровой ряд в нижнем регистре). Кроме того, отсутствие необходимости в верхнем регистре означает, что UCase() можно удалить.

'based off aurezio's solution
Function CleanCode(Rng As Range)
    Dim strTemp As String
    Dim n As Long

    For n = 1 To Len(Rng)
        Select Case Asc(Mid(Rng, n, 1))
            Case 45, 48 To 57, 65 To 90, 97 To 122
                strTemp = strTemp & Mid(Rng, n, 1)
        End Select
    Next
    CleanCode = strTemp
End Function

В конце концов, я хотел бы улучшить его, чтобы в конечном итоге сделать его динамичным и разрешить входные аргументы необходимых фильтров. например ClearCode(Диапазон,"45", "48-57", "65-90", "97-122")

person dsdrk    schedule 20.08.2018