Search code examples
regexexcelvbaregexp-replace

Regex pattern to replace characters

The program contains text of this type:

A B Ccccc
A Ccccc
ACcccc
ABCcccc

I need only such text to remain:

Ccccc

I wrote a replacement function, but I just can’t pick up a pattern How to make such a pattern?

Solution

  • No need for regex, nor VBA. It seems you simply are looking for the position of the last upper-case letter and then to extract from there:

    enter image description here

    Formula in B1 (with Excel O365):

    =MID(A1,MAX(SEQUENCE(LEN(A1))*(EXACT(UPPER(MID(A1,SEQUENCE(LEN(A1)),1)),MID(A1,SEQUENCE(LEN(A1)),1)))),LEN(A1))

    If you don't have Excel O365:

    =MID(A1,MAX(ROW(A1:INDEX(A:A,LEN(A1)))*(EXACT(UPPER(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)),MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)))),LEN(A1))

    You probably need to enter as array through: CtrlShiftEnter

    If you must go through VBA and regex then a pattern like:

    [A-Z][^A-Z]*$