How to use subfield function on this field?

[jim_chan]

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

[avinashelite]

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)

[Gysbert_Wassenaar · Accepted solution]

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

[jim_chan]

sorry. my apology on the example given.

the logic here should be always AFTER 1st 3 characters

[Anonymous]

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.

[Anonymous]

I would use

=right('ABCDJIM', len('ABCDJIM') -4)

=right(customercode, len(customercode) -4)

If there is constantly 3 characters in front

[jim_chan]

in my case. i have used MID() . thank you.