excelexcel-formula

Trying to keep last 9 of various strings

I am trying to extract the a set of 9 characters that follow the text "VRID". There are a few variations on the text I am trying to extract.

Here are 2 examples:

VRID 116BMGJHD
ISA 84858027977   VRID 116BMGJHD

In these examples I only need to keep 116BMGJHD.

Some of the cells are either empty or contain just ISA 84858027977. These cells need to be ignored (i.e. not return anything to the target cell in the adjacent column.

Any assistance is appreciated.

Formulas I have tried:

=IF(G3="","",TRIM(RIGHT(SUBSTITUTE(G3, J3, REPT(" ", LEN(G3))), LEN(G3))))

=IF(G19="","",RIGHT(G19,LEN(G19)-FIND(" ",G19)))

=IF(G77="","",RIGHT($G77,(LEN($G77)-(LEN($G77)-9))))
Solution

  • As mentioned in commnets above, you could try using the following formula if not using MS365 as already was suggested by Scott Craner Sir:

    enter image description here

    =IFERROR(REPLACE(A1,1,FIND("VRID ",A1)+4,),"")

    Or,

    • If MS365 then,

    =IFNA(TEXTAFTER(A1:A3,"VRID "),"")