Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
i have a field customercode.
for example i have ABCDJIM. and now i want to use subfield to get 'JIM' only.
how should i write using subfield?
SubField( customercode, 3,1) as Name_only,
Rgds
Jim
What's the logic to get the name?
Is it always the last three characters: Right(customercode, 3) as Name_only
Or does the name always start on position 5 and the first 4 characters need to be discarded? Mid(customercode,5) as Name_only
To use Subfield() function you need a specific separator ..In your case I don't see any valid separator .
May for your case(if the you know the place value of the text to fetch) you can use the Right function
Right(customercode,3)
What's the logic to get the name?
Is it always the last three characters: Right(customercode, 3) as Name_only
Or does the name always start on position 5 and the first 4 characters need to be discarded? Mid(customercode,5) as Name_only
sorry. my apology on the example given.
the logic here should be always AFTER 1st 3 characters
If this format is consistantly ABCD infront of the name,
To use substring to Return JIM:
Below we use D as the delimiter and returns the 2nd occurance.
subfield('ABCDJIM', 'D' ,2)
in your case try:
subfield(customercode, 'D' ,2)
I dont feel this is the best way but the above should answer your question.
Please provide some sample data if the values do not have ABCD consistantly appended to the front of the name.
I would use
=right('ABCDJIM', len('ABCDJIM') -4)
=right(customercode, len(customercode) -4)
If there is constantly 3 characters in front
in my case. i have used MID() . thank you.