Problem with Right function

[knightwest]

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

 

[neileg]

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)

 

[knightwest]

That's great thanks.

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

 

[neileg]

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

 

[Dennisk]

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

 

[boblarson]

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

 

[neileg]

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!

 

[boblarson]

Happy to do so, Neil!

 

[The_Doc_Man]

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.