InStr Query

dastr

Registered User.
Local time
Today, 15:19
Joined
Apr 1, 2012
Messages
43
Dear all,

I have the below query. In a column called Sizes S – I have the below sizes. I would like to take the last one A58, but when I apply the below Right/InStr - I get not only the the comma deleted but also the first capital A. Can anybody help or provide another query that does the same?

54,46,50,58,A46,A48,A50,A52,A54,A56,A58

Last Size: (Right([Sizes S],InStr(1,[Sizes S],",")-1))

Thanks in advance
 
dastr, why do you have the -1? Remove that and it should give you A58.
 
Hi,
I have tried this, but it still deletes the A in front. Are tehre any other fucntions that can give me the last size?

many thanks,
 
Really? I just tried this in the immediate window to test this, it gave me A58..
Code:
? Right("54,46,50,58,A46,A48,A50,A52,A54,A56,A58",InStr(1,"54,46,50,58,A46,A48,A50,A52,A54,A56,A58",","))
A58
See if the data is properly received in the [Sizes S] field.
 
If all you need is the last three characters of the string why not use:
Code:
Right([FONT=Calibri]([Sizes S],3)[/FONT]
 
I presume that you are not using MrB 's suggestion as the number of characters may not be 3, however you are not doing this the correct way as you are looking for the position of the first , use instrrev to find the position of the last , and subtract this from the Len of the string to find the length for use in the Right function.

Right(stringname,len(stringname)- instrrev(Stringname,","))

Brian
 
I presume that you are not using MrB 's suggestion as the number of characters may not be 3, however you are not doing this the correct way as you are looking for the position of the first , use instrrev to find the position of the last , and subtract this from the Len of the string to find the length for use in the Right function.

Right(stringname,len(stringname)- instrrev(Stringname,","))

Brian

You are right. Dastr gets only 2 characters out because the Instr returns the position of the first comma, ie. 3 and he subtracts 1. pr2-eugin's suggestion does not work and he gets 3 characters only because of a coincidence. If the last code was 2 or 4 characters long his method would fail.

InstrRev is the function that will isolate the last size code. Another way to get it would be :

Mid(stringname, InStrRev(stringname, ",") + 1)

Best,
Jiri
 
LOL
Jiri that is the solution I would have given if asked how to get the characters after the last "," it's amazing how one gets tunnel vision and corrects an error rather than giving best answer.

Brian
 

Users who are viewing this thread

Back
Top Bottom