Hello all,

I have a problem with the RIGHT function.
It all seems very simple to me, but I can't get it to work the way I want.

I have a set of fields:
RETURNED BACS - ET1685311TA
they all start with "Returned Bacs - " bit.

I thought it would be easy using the RIGHT function to just pick the bit right of the last space.

I have written this (with help from an example):
Right(Trim([text]),Len(Trim([text]))-InStr(1,[text]," ")-1)

but it is always bringing back "ACS - ET1685311TA

I know I am doing something wrong, I have played around with the formula, and as I change parameters it keeps bringing back different results, but never just the stuff to the right of the final space, which is what I need.

Any help would be much appreciated...

InStr() is searching for a space and the first one it finds is the one before BACS since it searches from left to right.

If they all start with RETURNED BACS - then you don't need to calculate the start position, it's always going to be 17. So this resolves to
Right(Trim([text]),Len(Trim([text]))-17)

That's great thanks.

Just out of interest. how can you make InStr search for the 2nd or 3rd etc space (if you can)?

I don't think you can. You'd have to build a function in VBA.

as the instr function contains a start postition parameter you can embed this function in a loop. so the start position is the previous position +1

You can also use InstrRev() to get the first instring value but starting from the right and moving back to the left.

You can also use InstrRev() to get the first instring value but starting from the right and moving back to the left.

Bob, you have just reduced my ignorance by a tiny fraction!

Happy to do so, Neil! :)

While you are looking at the space, you are ignoring another, even better marker. Look for the first dash in INSTR - then use RIGHT to get the remainder - then TRIM the result.