excel - Trying to keep last 9 of various strings - Stack Overflow

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))))

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))))

• excel
• excel-formula

Share Improve this question Follow edited Jan 7 at 13:28 Iron Man asked Jan 6 at 22:20 Iron ManIron Man 84911 gold badge1313 silver badges2828 bronze badges 23

• 1 "Some cells in the column contain" -- contain what? – Barmar Commented Jan 6 at 22:23
• 1 It's not clear what distinguishes the ones that should be ignored and the ones that should be copied. – Barmar Commented Jan 6 at 22:26
• 1 But what's different in XXX XXXXXXXXXXX? Why shouldn't the last 9 characters of that be copied? – Barmar Commented Jan 6 at 22:28
• 2 =IF(G3="","",IF(MID(G3,LEN(G3)-9,1)=" ",RIGHT(G3,9),""))? – Scott Craner Commented Jan 6 at 22:29
• 1 Back to my original question: What is the criteria for "text I need"? – Barmar Commented Jan 6 at 22:31

 |  Show 18 more comments

1 Answer 1

Sorted by: Reset to default 2

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

---

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

Or,

• If MS365 then,

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

---