Получить символы справа от числового символа в Excel

Question

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

PW22CL -> CL
PW22CLB -> CLB
PW22EBS ->EBS
PW22EK -> EK
PW22FL ->FL
PW22HF -> HF
W21M -> M
W21OB ->OB

Можно ли как-нибудь добавить регулярные выражения в обычные функции LEFT, RIGHT, MID, FIND в Excel?

Answer 1

Если вы не хотите использовать VBA:

Метод 1: Используйте эту формулу массива:

=RIGHT(A1,LEN(A1)-MAX(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),ROW(INDIRECT("1:" & LEN(A1))))))

Не забудьте нажать CTRL+SHIFT+ENTER, чтобы оценить его.

---

Метод 2: Или используйте эту формулу массива:

=RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))

Не забудьте нажать CTRL+SHIFT+ENTER, чтобы оценить его.

---

Метод 3: Или эта обычная формула, если вы используете Excel 2010 или выше (так как он использует функцию AGGREGATE):

=RIGHT(A1,LEN(A1)-AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),1))

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

Answer 2

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

=MID(A2,LOOKUP(2,1/ISNUMBER(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2))))+1,99)For a non-volatile form, useful if you have a large worksheet, 

Или, чтобы избежать изменчивых функций, вы можете определить имя seq где:

seq Refers To:  =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

Затем используйте эту формулу:

=MID(A1,LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)+1,99)

объяснение

• seq или Row(Indirect... возвращает массив целых чисел 1..n
• Второй MID(... затем возвращает массив каждого отдельного символа в строке
• 1/isnumber(-MID(... затем возвращает массив ошибок или 1
• В этом случае LOOKUP вернет позицию последнего числового значения в строке. Это соответствует последнему значению в массиве, которое меньше, чем lookup_value. Это дает нам начальный номер терминальных буквенных символов.

Answer 3

Как насчет этого маленького UDF():

Public Function JunkOnTheRight(sIN As String) As String
    Dim i As Long

    For i = Len(sIN) To 1 Step -1
        If IsNumeric(Mid(sIN, i, 1)) Then
            JunkOnTheRight = Mid(sIN, i + 1)
        Exit Function
        End If
    Next i
End Function